Calculate Moving Average Over Last 12 Months

  • 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

  • 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

  • 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

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

  • 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

  • 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?

  • 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

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

  • Here is an interesting approach for moving average

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911

    In that topic, set-based solution beat all other tehniques for moving average.


    N 56°04'39.16"
    E 12°55'05.25"

  • Peter - as always you bust out the most efficient solution with a minimum of fuss. Nice work...

    Regards,

    Jacob

    :w00t:

  • Thank you.


    N 56°04'39.16"
    E 12°55'05.25"

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

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