T-SQL ROWS BETWEEN 10 AND CURRENT ROW - SHOULD RETURN NULL?

Question

Forgive me for not creating test data but

AVG (fmp_close) OVER(PARTITION BY SeriesID ORDER BY fmp_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS '10DMA'

The first row in the date series does NOT have 9 rows preceding, but I'm getting a average for the 9 available rows, of which there are none, so I get fmp_close. I prefer a NULL. Any ideas how to return NULL when the preceding rows don't exist?

Answer 1

You seem to want:

CASE WHEN ROW_NUMBER() OVER(PARTITION BY SeriesID ORDER BY fmp_date) > 1
    THEN AVG(fmp_close) OVER(PARTITION BY SeriesID ORDER BY fmp_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) 
END AS [10DMA]

This forces a null value for the "first" row in each partition (assuming that there are no duplicate fmp_date for a given SeriesID).

It might be a little more efficient to use a window min():

CASE WHEN fmp_date > MIN(fmp_date) OVER(PARTITION BY SeriesID)
    THEN AVG(fmp_close) OVER(PARTITION BY SeriesID ORDER BY fmp_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) 
END AS [10DMA]

Side note: do not use single quotes for identifiers - they should be reserved to literal strings only, as specified in standard SQL. Use the relevant identifier quoting character for your database (in TSQL, that's square brackets).