In SQL Server 2012, one of the addition to OVER clause is, ROWS/RANGE Clause. In general, when we apply aggregate functions in over clause, it will apply to all rows in that group/partition. Suppose, if we wants to restrict the number of rows, it involves some complexity. Assume, to calculate Next 5yrs total revenue for a company, the number of rows needs to be considered are always that row and next 4 rows.

Now in SQL Server 2012, Over Clause is enhanced with ROWS/RANGE Clause, which allows to specify number of rows to consider while applying aggregates.

Suppose, to calculate sum of last 5 yrs revenue for every row,

ROWS BETWEEN 4 PRECEDING AND CURRENT ROW

Suppose to calculate sum of all preceding years(Cumulative sum),

ROWS  UNBOUNDED PRECEDING

Suppose to calculate sum of next 5yrs revenue for every row

ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING

DECLARE @Companies TABLE
(   
    CompanyId int,
    [Year] int,
    Amount int
)
INSERT INTO @Companies (CompanyId,[Year],[Amount])
VALUES (1,2000,100000),(1,2001,200000),(1,2002,35000),(2,2000,50000),(2,2001,75000),(2,2002,35000)

SELECT  CompanyId,[Year],Amount,
    	SUM(Amount) OVER (Partition by CompanyID 
    						ORDER BY [Year]
    						ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) AS Next4YrsIncludingCurrentYrRevenue	,
    	SUM(Amount) OVER (Partition by CompanyID 
    						ORDER BY [Year]
    						ROWS  UNBOUNDED PRECEDING
    						) AS CumulativeRevenue,
    	SUM(Amount) OVER (Partition by CompanyID 
    						ORDER BY [Year]
    						ROWS BETWEEN 4 PRECEDING AND CURRENT ROW 
    						) AS Last5YrsRevenue

FROM @Companies



/*
CompanyId   Year        Amount      Next4YrsRevenue CumulativeRevenue Last5YrsRevenue
----------- ----------- ----------- --------------- ----------------- ---------------
1           2000        100000      335000          100000            100000
1           2001        200000      235000          300000            300000
1           2002        35000       35000           335000            335000
2           2000        50000       160000          50000             50000
2           2001        75000       110000          125000            125000
2           2002        35000       35000           160000            160000
*/

• Reference 1  [90 clicks]
• Reference 2  [32134 clicks]
• Reference 3  [32134 clicks]

Category :	TSQL Enhancements
Release :	All
Tags :	SQL Server 2012,Denali,TSQL,OVER Clause