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