CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlwindow-functions
gwhb
gwhb

Reputation: 87

How can I create a moving average in SQL Server that resets for each unique group?

I have a list of contracts and I have a short query that will calculate a moving average over the last 30 entries but I would like it to reset for each contract. Here is what I have so far.

SELECT
    contract, 
    tradedate,
    settle,
    AVG(settle) OVER (ORDER BY contract, tradedate ROWS between 29 PRECEDING and CURRENT ROW) AS MA30
FROM 
    Pricing.dbo.MasterReport$

The output looks like this:

contract  tradedate     settle      MA30
----------------------------------------------
1RF18     2018-02-02    0.90277     0.95134
1RF19     2017-10-24    0.74563     0.943993214285714

I need the MA30 to reset for 1RF19 and start a new moving average. How can I do this?

Upvotes: 0

Views: 93

Answers (1)

GMB
GMB

Reputation: 222432

contract should be part of the PARTITION clause of the window function rather than to the ORDER BY clause:

SELECT
    contract, 
    tradedate,
    settle,
    AVG(settle) OVER (
        PARTITION BY contract 
        ORDER BY tradedate 
        ROWS BETWEEN 29 PRECEDING and CURRENT ROW
    ) AS MA30
FROM Pricing.dbo.MasterReport$

Upvotes: 4

Related Questions