Y.O.Y Sales Reporting by Originating Order Branch with Monthly Sale Goals
Y.O.Y Sales Reporting by Originating Order Branch with Monthly Sale Goals
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 report that a typical business person can read and utilize
– pulling it into Excel allows you to quickly add the functionality Excel is designed for (conditional formatting to show drop in sales, etc ..)
– for this query a case statement is used to assign sales to the branch from a user defined field in SOP10106 where the sale originated from and not where the sale ships from. this is done to capture the true sales a branch is developing.
– the portion of code to calculate returns is commented out in the query but they can be easily included in the numbers by removing the ‘–‘
Variables in the query:
– XXX, replace the XXX with the period (Month number, i.e. “5” for May) you are reporting on
– YYY, replace the YYY with number budgeted for sales for that period
– ZZZ, replace the ZZZ with the multiplier that will set the sales growth goal #1
– QQQ, replace the QQQ with the multiplier that will set the sales growth goal #2
– AAA & BBB, replace the AAA with a starting date range (current year) to pull sales and the BBB with the ending date range (current year) to pull sales
– CCC & DDD, replace the CCC with a starting date range (previous year) to pull sales and the DDD with the ending date range (previous year) to pull sales
– ???, replace the ??? with the assigned sales person from the particular branch (optional, you can comment out this code)
– EEE & FFF, replace the EEE with the beginning date of the previous year and the FFF with the ending date of the current year you wish to run. (this captures all transactions to make sure the math is right)
select
summary.Month,
summary.Day,
ISNUll(SUM(CASE WHEN summary.Month = XXX THEN summary.[Current Year SALES] END),0) [Current Year Sales],
ISNUll(SUM(CASE WHEN summary.Month = XXX and summary.[Current Year SALES] > ‘0’ THEN (cast(YYY as int)) END),0) [Budget],
(ISNUll(SUM(CASE WHEN summary.Month = XXX THEN summary.[Current Year SALES] END),0) – ISNUll(SUM(CASE WHEN summary.Month = XXX and summary.[Current Year SALES] > ‘0’ THEN (cast(YYY as int)) END),0) ) [Delta to Budget],
(SUM(CASE WHEN summary.Month = XXX and summary.[Current Year SALES] > ‘0’ then (cast(YYY as int)) else ‘0’ END) * (ZZZ)) + (ISNUll(SUM(CASE WHEN summary.Month = YYY and summary.[Current Year SALES] > ‘0’ then (cast(YYY as int)) END),0)) [Goal 1],
(SUM(CASE WHEN summary.Month = XXX and summary.[Current Year SALES] > ‘0’ then (cast(YYY as int)) else ‘0’ END) * (QQQ)) + (ISNUll(SUM(CASE WHEN summary.Month = XXX and summary.[Current Year SALES] > ‘0’ then (cast(YYY as int)) END),0)) [Goal 2],
ISNUll(SUM(CASE WHEN summary.Month = XXX THEN summary.[Previous Year SALES] END),0) [Previous Year Sales]
from
(select
CASE
WHEN month(detail.glpostdt) = ‘1’ THEN ‘January’
WHEN month(detail.glpostdt) = ‘2’ THEN ‘February’
WHEN month(detail.glpostdt) = ‘3’ THEN ‘March’
WHEN month(detail.glpostdt) = ‘4’ THEN ‘April’
WHEN month(detail.glpostdt) = ‘5’ THEN ‘May’
WHEN month(detail.glpostdt) = ‘6’ THEN ‘June’
WHEN month(detail.glpostdt) = ‘7’ THEN ‘July’
WHEN month(detail.glpostdt) = ‘8’ THEN ‘August’
WHEN month(detail.glpostdt) = ‘9’ THEN ‘September’
WHEN month(detail.glpostdt) = ’10’ THEN ‘October’
WHEN month(detail.glpostdt) = ’11’ THEN ‘November’
WHEN month(detail.glpostdt) = ’12’ THEN ‘December’
end [Month],
day(detail.GLPOSTDT) [Day],
ISNUll(SUM(CASE WHEN detail.glpostdt between AAA and BBB and datename(dw,detail.GLPOSTDT) not in (‘Saturday’, ‘Sunday’) THEN detail.sales END),0) [Current Year SALES],
ISNUll(SUM(CASE WHEN detail.glpostdt between CCC and DDD and datename(dw,detail.GLPOSTDT) not in (‘Saturday’, ‘Sunday’) THEN detail.sales END),0) [Previous Year SALES]
from
(select a.sopnumbe, a.CUSTNMBR, a.CUSTNAME, a.locncode, a.state, b.ITEMNMBR, b.ITEMDESC, a.ZIPCODE, a.glpostdt,
case
when c.USRTAB01 = ‘???’ then ‘Branch1’
when c.usrtab01 = ‘???’ then ‘Branch2’
else a.locncode
end [OriginSale],
(b.UNITcost * (case
when a.soptype = ‘3’ then ((b.qtyfulfi) * 1)
–when a.soptype = ‘4’ then ((b.QUANTITY) * -1)
— pulled out returns
end)) [COGS],
(b.UNITPRCE * (case
when a.soptype = ‘3’ then ((b.QTYFULFI) * 1)
–when a.soptype = ‘4’ then ((b.quantity) * -1)
— pulled out returns
end)) [SALES]
from sop30200 a
inner join sop30300 b
on a.sopnumbe = b.sopnumbe and a.soptype in (‘3’) and a.voidstts = ‘0’
–on a.sopnumbe = b.sopnumbe and a.soptype in (‘3′,’4’) and a.voidstts = ‘0’
— pulled out returns
left outer join sop10106 c
on c.sopnumbe = a.sopnumbe
where a.GLPOSTDT between EEE and FFF
and month(a.GLPOSTDT) = XXX
) detail
where detail.OriginSale in (‘Branch1′,’Branch2’)
group by detail.GLPOSTDT
) summary
group by summary.month, summary.day