Sales Book of Business Y.O.Y Report Showing Amount of Sales and Transactions
Sales Book of Business Y.O.Y Report Showing Amount of Sales and Transactions
A few things to note about this query:
– I typically pull this query and a majority of the others into Excel via a stored procedure to produce a year over year (Y.O.Y) analysis of the performance of the sales person
– pulling it into Excel allows you to quickly add the functionally Excel is designed for (conditional formatting to show drop in margin, drop in sales, etc …)
– sales person is being pulled from the RM00101 table using the SLPRSNID
– the sales data is being pulled from the SOP30200 table (historical sales header) since i am only looking for sales totals
– this query is only looking at invoices (SOPTYPE = ‘3’) and removing all sales voids (VOIDSTTS = ‘0’)
– the date range is being determined by the posted date (GLPOSTDT) of the invoice although you could use the document date (DOCDATE) if you chose to do so
Variables in the query:
– XXX, replace the XXX in the query with the current year date range you would like to pull numbers for
– ZZZ, replace the ZZZ in the query with the previous year date range you would like to pull numbers for
select a.slprsnid [BDM], sum(b.SUBTOTAL) [Current Sales], sum(b.EXTDCOST) [Current COGS], count(b.sopnumbe) [Current TRX],
(select sum(x.subtotal) from rm00101 z left outer join sop30200 x on x.CUSTNMBR = z.CUSTNMBR
and z.SLPRSNID = a.SLPRSNID and x.SOPTYPE = ‘3’ and x.VOIDSTTS = ‘0’ and x.GLPOSTDT between ZZZ
and ZZZ) [Previous Sales],
(select sum(x.EXTDCOST) from rm00101 z left outer join sop30200 x on x.CUSTNMBR = z.CUSTNMBR
and z.SLPRSNID = a.SLPRSNID and x.SOPTYPE = ‘3’ and x.VOIDSTTS = ‘0’ and x.GLPOSTDT between ZZZ
and ZZZ) [Previous COGS],
(select count(x.sopnumbe) from rm00101 z left outer join sop30200 x on x.CUSTNMBR = z.CUSTNMBR
and z.SLPRSNID = a.SLPRSNID and x.SOPTYPE = ‘3’ and x.VOIDSTTS = ‘0’ and x.GLPOSTDT between ZZZ
and ZZZ) [Previous Trx]
from rm00101 a
left outer join sop30200 b
on b.custnmbr = a.CUSTNMBR
where b.GLPOSTDT between XXX and XXX
and b.SOPTYPE = ‘3’ and b.VOIDSTTS = ‘0’
group by a.SLPRSNID