In this section, we explore the SQL Server 2012 features and enhancements to the windowing functions.
In SQL Server 2012, you can now include the
ORDER BY clause in the
OVER clause with standard aggregate functions. This capability allows you to generate moving averages and cumulative totals. The following example uses the
SUM functions with the
OVER clause to provide a moving average and cumulative total of yearly sales for each territory in the
Sales.SalesPerson table. The
PARTITION BY clause defines the partition on
TerritoryID and logically ordered by the year stored in the
ModifiedDate column. This computes the average value of
SalesYTD for each territory ...