April 10, 2015 at 5:37 am
Hello
I have a question regarding Running Totals (SUM) in a query.
My DB has X entries regarding SALES from certain WEEKS between 2012 and 2015 divided into MARKETS and ITEMS.
I want each row to show both the week sale and the sum of sales the preceeding 52 weeks (a rolling year total).
The example I provide below works as intended but is very slow when im working with my original dataset which is 800 000+ rows. I suspect that this is because it runs a new subquery with each row.
My company is using Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) and i CANNOT update this. Hence i cannot use 2012 functions and commands such as windows etc.
I have tried to find a better solution but I cannot get it to work with other methods.
Can someone please point me in the right direction or preferrably give me some form of example or solution that does not produce a 5 minute query.
Ideally if there is some form of method like the SUM-Partition. ie:
SUM(SALES) OVER (PARTITION BY MARKET, ITEM
WHERE DATE BETWEEN DATEADD(wk, -52, a.DATE) AND DATE) AS "ROLL12-SALES"
Simply put I need a query that does exactly the same thing as the one below but in a much faster way!
(i.e. it cannot run a new query with every row to find the sum of 52 preceeding weeks' sales)
Here is an example of the code im using (which only shows a small selection of weeks between 2013 and 2015):
DROP TABLE #TEST
CREATE TABLE #TEST (DATE date,MARKET varchar(10), ITEM varchar(10), SALES int)
go
-- MARKET = NORTH AND ITEM = A
INSERT #TEST VALUES ('2013-12-15','NORTH','A',67)
INSERT #TEST VALUES ('2013-12-22','NORTH','A',53)
INSERT #TEST VALUES ('2013-12-29','NORTH','A',0)
INSERT #TEST VALUES ('2014-01-05','NORTH','A',11)
INSERT #TEST VALUES ('2014-01-12','NORTH','A',23)
INSERT #TEST VALUES ('2014-01-19','NORTH','A',10)
INSERT #TEST VALUES ('2014-12-14','NORTH','A',18)
INSERT #TEST VALUES ('2014-12-21','NORTH','A',54)
INSERT #TEST VALUES ('2014-12-28','NORTH','A',51)
INSERT #TEST VALUES ('2015-01-04','NORTH','A',0)
INSERT #TEST VALUES ('2015-01-11','NORTH','A',0)
INSERT #TEST VALUES ('2015-01-18','NORTH','A',28)
-- MARKET = NORTH AND ITEM = B
INSERT #TEST VALUES ('2013-12-15','NORTH','B',120)
INSERT #TEST VALUES ('2013-12-22','NORTH','B',55)
INSERT #TEST VALUES ('2013-12-29','NORTH','B',12)
INSERT #TEST VALUES ('2014-01-05','NORTH','B',24)
INSERT #TEST VALUES ('2014-01-12','NORTH','B',58)
INSERT #TEST VALUES ('2014-01-19','NORTH','B',63)
INSERT #TEST VALUES ('2014-12-14','NORTH','B',75)
INSERT #TEST VALUES ('2014-12-21','NORTH','B',5)
INSERT #TEST VALUES ('2014-12-28','NORTH','B',85)
INSERT #TEST VALUES ('2015-01-04','NORTH','B',14)
INSERT #TEST VALUES ('2015-01-11','NORTH','B',99)
INSERT #TEST VALUES ('2015-01-18','NORTH','B',2)
-- MARKET = SOUTH AND ITEM = A
INSERT #TEST VALUES ('2013-12-15','SOUTH','A',77)
INSERT #TEST VALUES ('2013-12-22','SOUTH','A',55)
INSERT #TEST VALUES ('2013-12-29','SOUTH','A',45)
INSERT #TEST VALUES ('2014-01-05','SOUTH','A',3)
INSERT #TEST VALUES ('2014-01-12','SOUTH','A',0)
INSERT #TEST VALUES ('2014-01-19','SOUTH','A',7)
INSERT #TEST VALUES ('2014-12-14','SOUTH','A',88)
INSERT #TEST VALUES ('2014-12-21','SOUTH','A',65)
INSERT #TEST VALUES ('2014-12-28','SOUTH','A',34)
INSERT #TEST VALUES ('2015-01-04','SOUTH','A',28)
INSERT #TEST VALUES ('2015-01-11','SOUTH','A',7)
INSERT #TEST VALUES ('2015-01-18','SOUTH','A',66)
-- MARKET = SOUTH AND ITEM = B
INSERT #TEST VALUES ('2013-12-15','SOUTH','B',5)
INSERT #TEST VALUES ('2013-12-22','SOUTH','B',88)
INSERT #TEST VALUES ('2013-12-29','SOUTH','B',54)
INSERT #TEST VALUES ('2014-01-05','SOUTH','B',0)
INSERT #TEST VALUES ('2014-01-12','SOUTH','B',12)
INSERT #TEST VALUES ('2014-01-19','SOUTH','B',18)
INSERT #TEST VALUES ('2014-12-14','SOUTH','B',18)
INSERT #TEST VALUES ('2014-12-21','SOUTH','B',77)
INSERT #TEST VALUES ('2014-12-28','SOUTH','B',44)
INSERT #TEST VALUES ('2015-01-04','SOUTH','B',19)
INSERT #TEST VALUES ('2015-01-11','SOUTH','B',6)
INSERT #TEST VALUES ('2015-01-18','SOUTH','B',7)
SELECT
DATEPART(ISO_WEEK,DATE) AS WEEK
,DATE
,MARKET
,ITEM
,SALES
,CASE WHEN DATE > '2014-1-14' THEN
(
SELECT SUM(SALES) AS R12
FROM #TEST AS r1
WHERE r1.DATE <= a.DATE AND r1.DATE > DATEADD(wk, -52, a.DATE)
AND r1.ITEM = a.ITEM AND r1.MARKET = a.MARKET
)
ELSE NULL END AS "ROLL12-SALES"
FROM #TEST AS a
WHERE MARKET = 'NORTH' AND ITEM = 'A' -- LIMITATION OF RESULTS
ORDER BY DATE
Which results in:
WEEKDATE MARKETITEMSALESROLL12-SALES
502013-12-15NORTHA67NULL
512013-12-22NORTHA53NULL
522013-12-29NORTHA0NULL
12014-01-05NORTHA11NULL
22014-01-12NORTHA23NULL
32014-01-19NORTHA10164
502014-12-14NORTHA18115
512014-12-21NORTHA54116
522014-12-28NORTHA51167 (calculation is:)
12015-01-04NORTHA0156 (0+51+54+18+10+23 = 156)
22015-01-11NORTHA0133 (0+0+51+54+18+10 = 133)
32015-01-18NORTHA28151 (28+0+0+51+54+18 = 151)
Best Regards
April 10, 2015 at 7:33 am
Are you looking for something like this?
WITH Last52 (Market, Item, Sales52)
AS
(
SELECT
Market,
Item,
SUM(sales) AS Sales52
FROM
#Test
WHERE
[Date] BETWEEN DATEADD (wk, -52, Date)AND GETDATE()
GROUP BY
Market,
Item
)
SELECT
DATEPART(wk, t.date) AS [Week],
t.*,
l.Sales52
FROM #Test t
JOIN Last52 l ON l.Market = t.Market AND l.Item = T.Item
This is giving you the sales on the last 52 weeks as of the date the query is executed. Are you looking for the previous 52 weeks of that particular date?
In that case this might do.
SELECT
DATEPART(wk, t.date) AS [Week],
t.*,
x.Sales52
FROM #Test t
OUTER APPLY
(
SELECT SUM(sales) AS Sales52
FROM #Test
WHERE
([Date] BETWEEN DATEADD (wk, -52, Date)AND t.date)
AND
(market = t.market AND item = t.item)
) x
EDIT: Added Week# to output
April 10, 2015 at 9:27 am
Thank you for the response
but no.
Im looking for a new query that produces exactly the same results as the example I give.
The only problem with my code, that is:
(SELECT SUM(SALES) AS R12 FROM #TEST AS r1
WHERE r1.DATE <= a.DATE AND r1.DATE > DATEADD(wk, -52, a.DATE)
AND r1.ITEM = a.ITEM AND r1.MARKET = a.MARKET) AS "ROLL12-SALES"
Is that it it runs a new query with every row which takes FAR too long with my original query that is 800 000+ rows.
I've edited the original post to clarify.
But simply I need a solution that produces identical results to my example but in another, faster way.
That is, SALES for the WEEK, PARTITIONED by ITEM and MARKET and also the SUM of SALES the 52 WEEKS preceeding that week.
For example: 2015-01-18 - WEEK 3 - MARKET NORTH - ITEM A SALES are 28 and the sum of the 52 preceeding weeks is 151 (28+0+0+51+54+18).
your example (#2) gives:
WeekDATE MARKETITEMSALESSales52
512013-12-15NORTHA6767
522013-12-22NORTHA53120
532013-12-29NORTHA0120
22014-01-05NORTHA11131
32014-01-12NORTHA23154
42014-01-19NORTHA10164
512014-12-14NORTHA18182
522014-12-21NORTHA54236
532014-12-28NORTHA51287
22015-01-04NORTHA0287
32015-01-11NORTHA0287
42015-01-18NORTHA28315
Which is not what im looking for.
April 10, 2015 at 10:06 am
Here is another way to write our query but it results is basically the same execution plan. Depending on how much data you have you may want to consider a clustered index on the table using DATE, MARKET, ITEM as the clustering key. And yes, I would probably have DATE as the leading column. You'd need to test this to be sure.
SELECT
DATEPART(ISO_WEEK,a.DATE) AS WEEK
,a.DATE
,a.MARKET
,a.ITEM
,a.SALES
,CASE WHEN a.DATE > '2014-1-14' THEN
oa.Rolling12Sales
ELSE NULL END AS "ROLL12-SALES"
FROM
#TEST AS a
outer apply (select sum(r.SALES) Rolling12Sales from #TEST r where r.ITEM = a.ITEM and r.MARKET = a.MARKET and r.DATE > DATEADD(wk, -52, a.DATE) and r.DATE <= a.DATE) oa
WHERE a.MARKET = 'NORTH' AND a.ITEM = 'A' -- LIMITATION OF RESULTS
ORDER BY a.DATE;
April 10, 2015 at 11:36 am
Sorry I used the wrong date in the APPLY. It should have been this:
SELECT
DATEPART(wk, t.date) AS [Week],
t.*,
x.Sales52
FROM #Test t
OUTER APPLY
(
SELECT SUM(sales) AS Sales52
FROM #Test
WHERE
([Date] BETWEEN DATEADD (wk, -52, t.date)AND t.date)
AND
(market = t.market AND item = t.item)
) x
Those numbers look good. It will only give you the total of the last 52 weeks. Notice how From 2014-12-21 to 2014-12-28 your total when down from 182 to 169 because the Sales from 2013-12-15 dropped. Of course you have to take into account the new current sales of 54 which was 13 less than 67.
EDIT: Mind you there is room for tweaking the date range. I noticed for example that week 4 (2015-01-18) rolling total is including the week 4 sales from the previous year (2014-01-19). Which technically is accurate since that date is within the last 52 weeks. However it can easily be changed, at least the method is sound. Also this way you don't get NULLs when there isn't a full 52 weeks of data unless of course that is also intentional.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply