Calculate Moving Average Over Last 12 Months

Question

Post reply

Calculate Moving Average Over Last 12 Months

nick_hodge

Old Hand

Points: 345
More actions
February 19, 2009 at 8:15 am

#72434

Hi
I have data as below (with 30,000 different items) and whilst I do not yet have 12 months of weekly data I want to create a moving average for the last 12 months (DATEADD(yyyy,-1, GETDATE())). This will run every week to capture our average inventory level for the last 12 months against each item.
Additionally, I need to retrospectively run this to update the records I currently have (30 weeks worth for each item currently).
The retrospective one needs to take an average only upto the date the record was captured, so for the first date the item would be the average on one record and for the second date, two records, etc. (I am thinking the current update would need different T-SQL than the future weekly job).
The data looks like this (the column on the right shows how it should look. From Excel 🙂 )
Date ItemNoValue12mAve
01/06/2008 00:000003E285.6465285.6465
08/06/2008 00:000003E377.9624331.8045
15/06/2008 00:000003E377.9624347.1904
22/06/2008 00:000003E377.9624354.8834
29/06/2008 00:000003E283.4718340.6011
06/07/2008 00:000003E283.4718331.0796
13/07/2008 00:000003E283.4718324.2784
20/07/2008 00:000003E188.9812307.3663
27/07/2008 00:000003E188.9812294.2124
03/08/2008 00:000003E188.9812283.6893
10/08/2008 00:000003E567.0228309.4469
17/08/2008 00:000003E567.0228330.9115
24/08/2008 00:000003E567.0228349.0739
31/08/2008 00:000003E567.0228364.6417
07/09/2008 00:000003E567.0228378.1338
14/09/2008 00:000003E567.0228389.9393
21/09/2008 00:000003E472.5190394.7970

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Milla-1016924 SSC Veteran Points: 227 More actions · Answer 1

Please try: http://www.sqlteam.com/article/calculating-running-totals

CREATE TABLE #Test (Date DATETIME, ItemNo VARCHAR(10), Value MONEY)

INSERT INTO #Test

SELECT '06/1/2008','0003E',285.6465 UNION ALL

SELECT '06/08/2008','0003E',377.9624 UNION ALL

SELECT '06/15/2008','0003E',377.9624 UNION ALL

SELECT '06/22/2008','0003E',377.9624 UNION ALL

SELECT '06/29/2008','0003E',283.4718 UNION ALL

SELECT '07/06/2008','0003E',283.4718 UNION ALL

SELECT '07/13/2008','0003E',283.4718 UNION ALL

SELECT '07/20/2008','0003E',188.9812 UNION ALL

SELECT '07/27/2008','0003E',188.9812 UNION ALL

SELECT '08/03/2008','0003E',188.9812 UNION ALL

SELECT '08/10/2008','0003E',567.0228 UNION ALL

SELECT '08/17/2008','0003E',567.0228 UNION ALL

SELECT '08/24/2008','0003E',567.0228 UNION ALL

SELECT '08/31/2008','0003E',567.0228 UNION ALL

SELECT '09/07/2008','0003E',567.0228 UNION ALL

SELECT '09/14/2008','0003E',567.0228 UNION ALL

SELECT '09/21/2008','0003E',472.5190

SELECT *, (SELECT AVG(Value)

FROM #Test b

WHERE a.ItemNo = a.ItemNo

AND b.DATE BETWEEN DATEADD(m, -12,a.Date) AND a.Date) '12mAve'

FROM #Test a

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Such correlated subquery on a decent amount of data is gonna kill even quite powerful server.

This should not be perfect but at least not so painful:

SELECT a.ItemNo, a.Date, AVG(b.Value) [12mAve]

FROM #Test a

LEFT JOIN #Test b ON a.ItemNo = a.ItemNo

AND b.DATE > DATEADD(m, -12,a.Date) AND b.DATE <= a.Date

GROUP BY a.ItemNo, a.Date

But if your table is "insert only" and there are no inserts back in time then I'd suggest to add a column "12 month AVE" and populate it once when a new row is added.

Trigger would be perfect for that.

Also I fixed the error introduced by BETWEEN.

BETWEEN will include current record and the one for a year ago, which (I guess) should not be included.

_____________
Code for TallyGenerator

nick_hodge Old Hand Points: 345 More actions · Answer 3

Thanks both for your help. I have this actual T-SQL but it times out (Timeout expired). (I have to calculate the inventory value but have tried on a test table with that pre-calculated, same issue). The only thing I can see is that it adds OUTER automatically, but that's probably the default?

SELECT a.ItemNo, a.Date, AVG(b.QOH * b.AveCost) AS [12mAve]

FROM dbo.WeeklyLoc1InventoryData AS a LEFT OUTER JOIN

dbo.WeeklyLoc1InventoryData AS b ON a.ItemNo = a.ItemNo AND b.Date > DATEADD(m, - 12, a.Date) AND b.Date <= a.Date

GROUP BY a.ItemNo, a.Date

Any clues? (I just hope I haven't got something wrong in the T-SQL, but don't think I have)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Why would you need average for previous 12 months for EVERY RECORD AT ONCE?

Every time you probably need it only for one particular record.

And do you have proper index on the table?

_____________
Code for TallyGenerator

nick_hodge Old Hand Points: 345 More actions · Answer 5

Sergiy

Thanks for helping. I will update the data in a temp table once a week and then, once I have the data by item number I will update the 30,000 records (approx) for that week. Currently I need to run it for the data already in the table (1.3m records).

I have three indexes on the table (Primary=ItemNo and Date, two other indexes of just date and just item no.). Not sure if this helps a bit?

Sergiy SSC Guru Points: 110208 More actions · Answer 6

With 1.3 m records such query will return result right before Christmas. 🙂

You definitely need a separate column for precalculated results for historical records.

May be it's reasonable to populate it when you "close the month" or whatever else is used as a milestone.

For current period column remains NULL.

Then your query may be UNION of big chunk WHERE AVG_12mth IS NOT NULL and small chunk WHERE AVG_12mth IS NULL and needs to be calculated on fly using one of the queries above.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

nick_hodge (2/20/2009)
Thanks both for your help. I have this actual T-SQL but it times out (Timeout expired). (I have to calculate the inventory value but have tried on a test table with that pre-calculated, same issue). The only thing I can see is that it adds OUTER automatically, but that's probably the default?
SELECT a.ItemNo, a.Date, AVG(b.QOH * b.AveCost) AS [12mAve]
FROM dbo.WeeklyLoc1InventoryData AS a LEFT OUTER JOIN
dbo.WeeklyLoc1InventoryData AS b ON a.ItemNo = a.ItemNo AND b.Date > DATEADD(m, - 12, a.Date) AND b.Date <= a.Date
GROUP BY a.ItemNo, a.Date
Any clues? (I just hope I haven't got something wrong in the T-SQL, but don't think I have)

"Must look eye"...

[font="Arial Black"]ON a.ItemNo = a.ItemNo [/font]

--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)