# Window functions
# Centered Moving Average
Calculate a 6-month (126-business-day) centered moving average of a price:
SELECT TradeDate, AVG(Px) OVER (ORDER BY TradeDate ROWS BETWEEN 63 PRECEDING AND 63 FOLLOWING) AS PxMovingAverage FROM HistoricalPrices
Note that, because it will take up to 63 rows before and after each returned row, at the beginning and end of the TradeDate range it will not be centered: When it reaches the largest TradeDate it will only be able to find 63 preceding values to include in the average.
# Find the single most recent item in a list of timestamped events
In tables recording events there is often a datetime field recording the time an event happened. Finding the single most recent event can be difficult because it's always possible that two events were recorded with exactly identical timestamps. You can use row_number() over (order by ...) to make sure all records are uniquely ranked, and select the top one (where my_ranking=1)
select * from ( select *, row_number() over (order by crdate desc) as my_ranking from sys.sysobjects ) g where my_ranking=1
This same technique can be used to return a single row from any dataset with potentially duplicate values.
# Moving Average of last 30 Items
Moving Average of last 30 Items sold
SELECT value_column1, ( SELECT AVG(value_column1) AS moving_average FROM Table1 T2 WHERE ( SELECT COUNT(*) FROM Table1 T3 WHERE date_column1 BETWEEN T2.date_column1 AND T1.date_column1 ) BETWEEN 1 AND 30 ) as MovingAvg FROM Table1 T1