SQL Server : moving average calculation

Question

I have a script that calculates moving avg based on the past 3 months.

Is it possible to modify this moving average to display values from only the 4th month ? Or when I want to calculate based on 4 months then display values from the 5th row and so on...

So the point is to display values only from that month when I have enough data for the calculation.

SELECT 
    [Brand], [month],
    CAST(SUM([Volume]) AS INT) AS [Volume (t)],
    AVG(CAST(SUM([Volume]) AS INT)) OVER (PARTITION BY [Brand]) AS [Average (t)],
    AVG(CAST(SUM([Volume]) AS INT)) OVER (PARTITION BY [Brand] ORDER BY [month] ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS [Moving Average (3)]
FROM 
    dw_lc_full_aggregated_1
WHERE   
    [year] = 2016 AND 
    [month] BETWEEN 1 AND 9 AND
    [Navision Source] = 'HU' AND
    [Brand] = 'xxxxxxx'
GROUP BY 
    [Brand], [month]

Give it a try you basically want the CASE when the COUNT OVER your PARTITION is greater than 3. You might be able to add that directly into your current query, otherwise add a row number using your PARTITION, find it's max and then filter out max row numbers less than 3 — Dan
How do you decide how many months must be included in moving avg field? And how that query looks like? — uzi

VBalazs VBalazs · Accepted Answer · 2018-02-13T11:04:00

Thank you! I solved it as following:

SELECT  [Brand],
        [month],
        cast(sum([Volume]) AS INT) as [Volume (t)],
        AVG(cast(sum([Volume])AS INT)) OVER (PARTITION BY [Brand]) AS [Average (t)],

        CASE WHEN (Row_Number() OVER (ORDER BY [month]))>3
        THEN AVG(cast(sum([Volume])AS INT)) 
                    OVER (PARTITION BY [Brand] ORDER BY [month] ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) 
                    ELSE NULL END AS [Moving Average (3)]
............

SQL Server : moving average calculation

1 Answers