SQL for sales summary report

If you need report for sales in current and prior year by customer and sales rep.



ALTER proc [dbo].[spSalesSummaryReport] as 

--exec spSalesSummaryReport

Declare @currentperiod int
Declare @currentyear int
Set @currentperiod=month(getdate())
Set @currentyear=year(getdate())
Declare @prioryear int
Set @prioryear=@currentyear-1


IF OBJECT_ID('tempdb..#currentmonthsales') IS NOT NULL
    DROP TABLE #currentmonthsales

IF OBJECT_ID('tempdb..#currentmonthYTDsales') IS NOT NULL
    DROP TABLE #currentmonthYTDsales

IF OBJECT_ID('tempdb..#priormonthsales') IS NOT NULL
    DROP TABLE #priormonthsales

IF OBJECT_ID('tempdb..#priormonthYTDsales') IS NOT NULL
    DROP TABLE #priormonthYTDsales

IF OBJECT_ID('tempdb..#customersandreps') IS NOT NULL
    DROP TABLE #customersandreps

IF OBJECT_ID('tempdb..#finalresults') IS NOT NULL
    DROP TABLE #finalresults



select salesrep_id, customer_id, customer_name, salesrepfullname, sum(salesrepscost) as TotalCost,
sum(salesrepssales) as TotalSales, 
sum(salesrepssales)-sum(salesrepscost) as TotalMargin,
case when sum(salesrepssales)<>0 then (sum(salesrepssales)-sum(salesrepscost))/sum(salesrepssales) 
else 0 end as MarginPercentage
into #currentmonthsales
from
(select a.customer_id, b.customer_name, period, year_for_period, c.salesrep_id, 
d.first_name+' '+d.last_name as SalesRepFullname,
c.commission_percentage,
total_amount, shipping_cost,
(total_amount-(isnull(tax_amount,0)))*commission_percentage/100 as SalesRepsSales,
shipping_cost*commission_percentage/100 as SalesRepsCost

from invoice_hdr as a
inner join
customer as b
on a.customer_id=b.customer_id
left join
invoice_hdr_salesrep as c
on a.invoice_no=c.invoice_number
left join
contacts as d
on c.salesrep_id=d.id
where period=@currentperiod and year_for_period=@currentyear
and a.invoice_class<>'FINANCE'  
and invoice_class+invoice_adjustment_type<>'IA'
) as a

group by salesrep_id, customer_id, customer_name, salesrepfullname


--select * from #currentmonthsales


select salesrep_id, customer_id, customer_name, salesrepfullname, sum(salesrepscost) as TotalCost,
sum(salesrepssales) as TotalSales ,
sum(salesrepssales)-sum(salesrepscost) as TotalMargin,
case when sum(salesrepssales)<>0 then (sum(salesrepssales)-sum(salesrepscost))/sum(salesrepssales) 
else 0 end as MarginPercentage


into #currentmonthYTDsales

from

(select a.customer_id, b.customer_name, period, year_for_period, c.salesrep_id, 
d.first_name+' '+d.last_name as SalesRepFullname,
c.commission_percentage,
total_amount, shipping_cost,
(total_amount-(isnull(tax_amount,0)))*commission_percentage/100 as SalesRepsSales,
shipping_cost*commission_percentage/100 as SalesRepsCost

from invoice_hdr as a
inner join
customer as b
on a.customer_id=b.customer_id
left join
invoice_hdr_salesrep as c
on a.invoice_no=c.invoice_number
left join
contacts as d
on c.salesrep_id=d.id
where period<=@currentperiod and year_for_period=@currentyear
and a.invoice_class<>'FINANCE'  
and invoice_class+invoice_adjustment_type<>'IA'
) as a

group by salesrep_id, customer_id, customer_name, salesrepfullname


--select * from #currentmonthYTDsales where customer_id=102064

--select sum(total_amount)  from invoice_hdr where customer_id=102064 and period<=7 and year_for_period=2015




select salesrep_id, customer_id, customer_name, salesrepfullname, sum(salesrepscost) as TotalCost,
sum(salesrepssales) as TotalSales ,
sum(salesrepssales)-sum(salesrepscost) as TotalMargin,
case when sum(salesrepssales)<>0 then (sum(salesrepssales)-sum(salesrepscost))/sum(salesrepssales) 
else 0 end as MarginPercentage


into #priormonthsales

from

(select a.customer_id, b.customer_name, period, year_for_period, c.salesrep_id, 
d.first_name+' '+d.last_name as SalesRepFullname,
c.commission_percentage,
total_amount, shipping_cost,
(total_amount-(isnull(tax_amount,0)))*commission_percentage/100 as SalesRepsSales,
shipping_cost*commission_percentage/100 as SalesRepsCost

from invoice_hdr as a
inner join
customer as b
on a.customer_id=b.customer_id
left join
invoice_hdr_salesrep as c
on a.invoice_no=c.invoice_number
left join
contacts as d
on c.salesrep_id=d.id
where period=@currentperiod and year_for_period=@prioryear
and a.invoice_class<>'FINANCE'  
and invoice_class+invoice_adjustment_type<>'IA'
) as a

group by salesrep_id, customer_id, customer_name, salesrepfullname



select salesrep_id, customer_id, customer_name, salesrepfullname, sum(salesrepscost) as TotalCost,
sum(salesrepssales) as TotalSales ,
sum(salesrepssales)-sum(salesrepscost) as TotalMargin,
case when sum(salesrepssales)<>0 then (sum(salesrepssales)-sum(salesrepscost))/sum(salesrepssales) 
else 0 end as MarginPercentage


into #priorYTDmonthsales

from

(select a.customer_id, b.customer_name, period, year_for_period, c.salesrep_id, 
d.first_name+' '+d.last_name as SalesRepFullname,
c.commission_percentage,
total_amount, shipping_cost,
(total_amount-(isnull(tax_amount,0)))*commission_percentage/100 as SalesRepsSales,
shipping_cost*commission_percentage/100 as SalesRepsCost

from invoice_hdr as a
inner join
customer as b
on a.customer_id=b.customer_id
left join
invoice_hdr_salesrep as c
on a.invoice_no=c.invoice_number
left join
contacts as d
on c.salesrep_id=d.id
where period<=@currentperiod and year_for_period=@prioryear
and a.invoice_class<>'FINANCE'  
and invoice_class+invoice_adjustment_type<>'IA') as a

group by salesrep_id, customer_id, customer_name, salesrepfullname


select a.customer_id, c.customer_name, b.salesrep_id, d.first_name+' '+d.last_name as SalesRep into #customersandreps from 
invoice_hdr as a
inner join
invoice_hdr_salesrep as b
on a.invoice_no=b.invoice_number
inner join customer as c
on a.customer_id=c.customer_id
left join
contacts as d
on b.salesrep_id=d.id
group by a.customer_id, c.customer_name, b.salesrep_id, d.first_name, d.last_name 

--select * from #currentmonthsales where customer_id=102215
--select sum(totalsales)  from #currentmonthYTDsales where salesrep_id=1011
--Select * from #priormonthsales where customer_id=102215
--select * from #priorYTDmonthsales where customer_id=102215
--select sum(currentsalesytd)  from #finalresults where salesrep_id=1011

select a.customer_id,a.customer_name, a.salesrep_id, a.SalesRep,
  b.TotalSales as CurrentMonthSales, b.TotalMargin as CurrentMonthMargin, b.MarginPercentage*100 as CurrentMonthMarginPercentage,
  c.TotalSales as CurrentSalesYTD, c.TotalMargin as CurrentYTDMargin, c.MarginPercentage*100 as CurrentYTDMarginPercentage,
  d.TotalSales as MonthSalesLastYear, d.TotalMargin as PriorMonthMargin, d.MarginPercentage*100 as PriorMonthMarginPercentage,
 e.TotalSales as SalesLYTD, e.TotalMargin as PriorYTDMargin, e.MarginPercentage*100 as PriorYTDMarginPercentage 

into #finalresults

from #customersandreps as a
left join #currentmonthsales as b
on a.salesrep_id=b.salesrep_id
and a.customer_id=b.customer_id
left join #currentmonthYTDsales as c
on 
a.salesrep_id=c.salesrep_id
and a.customer_id=c.customer_id
left join #priormonthsales as d
on a.salesrep_id=d.salesrep_id
and a.customer_id=d.customer_id
left join #priorYTDmonthsales as e
on a.salesrep_id=e.salesrep_id
and a.customer_id=e.customer_id




select @currentperiod, @currentyear, * from #finalresults
where (currentmonthsales is not null or CurrentSalesYTD is not null or MonthSalesLastYear is not null or SalesLYTD is not null)
order by customer_name, salesrep_id

Comments