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