SQL Server rolling average

Question

select
    Date, [Close],
    avg([close]) over (order by date asc rows 4 preceding) as MA10
from 
    dbo.APPL

This query returns the rolling 4 day average of stock prices. However I want to show the values as NULL if preceding days are fewer than 4.

How do I adjust that in SQL Server?

Can't you just to count([close]) over the same partition and use that to find if there are fewer than 4 rows returned? — JeffUK, Apr 01 '18 at 00:50
I can do that, but how do I select only those rows where count>3 — Tony, Apr 01 '18 at 01:04
I assume you can do that with a having clause on the count. Or by wrapping the field itself in an if / then. — Dylan Brams, Apr 01 '18 at 07:09

score -1 · Accepted Answer · answered Apr 01 '18 at 07:30

I think JeffUK meant doing something like this:

select
    Date, [Close],
    case when days >= 4 then MA10 end as MA10
from
(
    select
        Date, [Close],
        avg([close]) over (order by date asc rows 4 preceding) as MA10,
        count([close]) over (order by date asc rows 4 preceding) as days
    from 
        dbo.APPL

) X

SQL Server rolling average

1 Answers1