Cumulative Report
-
Hello,
I am looking to create a cumulative report that forecasts potential future sales based on current customer enrollment and order frequency. Below is an example of my customer data and frequency of order. How would I be able to make future projections so that I could show sales in a monthly grid? Will I need a Dates table to be able to do something like this?
Sample code:
-- DROP TABLE #c
CREATE TABLE #c (CustNo int, EnrollDt datetime, CustPrice money, Freq varchar(10))
INSERT INTO #c (CustNo, EnrollDt, CustPrice, Freq) VALUES (4444, '09/15/2021', 12.00, 'Monthly')
INSERT INTO #c (CustNo, EnrollDt, CustPrice, Freq) VALUES (9999, '12/15/2021', 22.00, 'Monthly')
INSERT INTO #c (CustNo, EnrollDt, CustPrice, Freq) VALUES (7777, '01/15/2022', 11.00, 'Quarterly')
INSERT INTO #c (CustNo, EnrollDt, CustPrice, Freq) VALUES (6666, '05/15/2021', 10.00, 'Quarterly')
INSERT INTO #c (CustNo, EnrollDt, CustPrice, Freq) VALUES (6666, '06/15/2021', 20.00, 'Monthly')
SELECT * FROM #cDesired Results:
Many thanks for any help in advance!
-
See the article at the similarly named link in my signature line below for a copy of the dbo.fnTally() function I use in the following. There are 3 main processes used here... Relational Multiplication and pre-aggregation for performance is used in the CTE and a CROSS TAB is used in the outer SELECT to do the "Pivot" and so much more.
I also added an extra column and an extra row because you just gotta know they're going to ask for them.
Also, I didn't use any of your dates. They didn't need to come into play for what you requested.
WITH ctePreAgg AS
(
SELECT c.CustNo,MonNum = t.N,CustPrice = SUM(CustPrice)
FROM #c c
CROSS APPLY dbo.fnTally(1,12) t
WHERE Freq = 'Monthly'
OR (Freq = 'Quarterly' AND (t.N-1)%3 = 0)
GROUP BY c.CustNo,t.N
)
SELECT Customer = IIF(GROUPING(CustNo) = 1, 'Total',CONVERT(VARCHAR(10),CustNo))
,[2022] = ''
,[ 1] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 1,CustPrice,0)),0),1),'')
,[ 2] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 2,CustPrice,0)),0),1),'')
,[ 3] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 3,CustPrice,0)),0),1),'')
,[ 4] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 4,CustPrice,0)),0),1),'')
,[ 5] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 5,CustPrice,0)),0),1),'')
,[ 6] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 6,CustPrice,0)),0),1),'')
,[ 7] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 7,CustPrice,0)),0),1),'')
,[ 8] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 8,CustPrice,0)),0),1),'')
,[ 9] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 9,CustPrice,0)),0),1),'')
,[ 10] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 10,CustPrice,0)),0),1),'')
,[ 11] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 11,CustPrice,0)),0),1),'')
,[ 12] = ISNULL('$'+CONVERT(CHAR(8),NULLIF(SUM(IIF(MonNum = 12,CustPrice,0)),0),1),'')
,[ Total] = '$'+CONVERT(CHAR(8),SUM(CustPrice),1)
FROM ctePreAgg
GROUP BY CustNo WITH ROLLUP
ORDER BY GROUPING(CustNo),CustNo
;Here are the results using your good test data (and thank you for helping me help you). Click on the graphic to see a larger version.
See the following link for more info on how the ancient "Black Art" known as CROSS TABs works.
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's a great answer from Jeff which exactly matches the output. My approach also uses Jeff's "number generator" function dbo.fnTally. While Jeff "didn't use any of your dates" (because it's not strictly necessary) here's an "enrollment flow" approach (admittedly, with quite a bit more code). Also, this code doesn't format the sums with a $ since the query results seem pre-destined to end up in a spreadsheet (where someone might semi-manually remove them). The query makes forward projections off the end of the EnrollDt for each (CustNo, Freq) pair so the @fcst_yr could be set to 2021 and it calculates the partial year etc
declare @fcst_yr int=2021;
select iif(grouping(c.CustNo) = 1, 'Total', cast(c.CustNo as varchar(30))) CustNo,
sum(case when month(v.calc_dt)=1 then c.CustPrice else 0 end) as [1],
sum(case when month(v.calc_dt)=2 then c.CustPrice else 0 end) as [2],
sum(case when month(v.calc_dt)=3 then c.CustPrice else 0 end) as [3],
sum(case when month(v.calc_dt)=4 then c.CustPrice else 0 end) as [4],
sum(case when month(v.calc_dt)=5 then c.CustPrice else 0 end) as [5],
sum(case when month(v.calc_dt)=6 then c.CustPrice else 0 end) as [6],
sum(case when month(v.calc_dt)=7 then c.CustPrice else 0 end) as [7],
sum(case when month(v.calc_dt)=8 then c.CustPrice else 0 end) as [8],
sum(case when month(v.calc_dt)=9 then c.CustPrice else 0 end) as [9],
sum(case when month(v.calc_dt)=10 then c.CustPrice else 0 end) as [10],
sum(case when month(v.calc_dt)=11 then c.CustPrice else 0 end) as [11],
sum(case when month(v.calc_dt)=12 then c.CustPrice else 0 end) as [12]
from #c c
cross apply (values (case when Freq='Monthly' then datediff(month, c.EnrollDt, datefromparts(@fcst_yr, 12, 31))
when Freq='Quarterly' then datediff(quarter, c.EnrollDt, datefromparts(@fcst_yr, 12, 31))
else null end)) fcst_periods(mo_or_qtr)
cross apply dbo.fnTally(0, iif(fcst_periods.mo_or_qtr>=0, fcst_periods.mo_or_qtr, 1)) fn
cross apply (values (case when Freq='Monthly' then dateadd(month, fn.n, c.EnrollDt)
when Freq='Quarterly' then dateadd(quarter, fn.n, dateadd(qq, datediff(qq, 0, c.EnrollDt), 0))
else null end)) v(calc_dt)
where year(v.calc_dt)=@fcst_yr
group by CustNo with rollup
order by grouping(c.CustNo), CustNo;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
There's also something crazy going on with this forum. The timestamp on the original post is for today at 8:15 PM, which hasn't happened here yet. I thought these things were normalized for whatever your local time zone is. My post is in the same condition and Steve Collin's post, which is the latest post, has a timestamp before all the other posts.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply