ROWS PRECEDING and FOLLOWING in TSQL 2012

First for this example, and a few to follow we need to create the database that we are going to play around in.

USE [Master];
CREATE DATABASE [tsql2012];
GO

USE [tsql2012];


-- Table to be used by Over Clause Rows/Range
CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
 (1,20000,1999),(2,60000,1999),(3,50000,1999),
 (1,40000,2000),(2,40000,2000),(3,60000,2000),
 (1,30000,2001),(2,30000,2001),(3,70000,2001),
 (1,90000,2002),(2,20000,2002),(3,80000,2002),
 (1,10300,2003),(2,1000,2003), (3,90000,2003),
 (1,10000,2004),(2,10000,2004),(3,10000,2004),
 (1,20000,2005),(2,20000,2005),(3,20000,2005),
 (1,40000,2006),(2,30000,2006),(3,30000,2006),
 (1,70000,2007),(2,40000,2007),(3,40000,2007),
 (1,50000,2008),(2,50000,2008),(3,50000,2008),
 (1,20000,2009),(2,60000,2009),(3,60000,2009),
 (1,30000,2010),(2,70000,2010),(3,70000,2010),
 (1,80000,2011),(2,80000,2011),(3,80000,2011),
 (1,10000,2012),(2,90000,2012),(3,90000,2012);

Next a review of the OVER clause as it is support prior to SQL Server 2012.

--First OVER Clause pre SQL 2012
-- http://stevestedman.com/?p=1454
select *,
 avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue,
 sum(Revenue) OVER (PARTITION by DepartmentID) as TotalRevenue
from REVENUE
order by departmentID, year;

Which produces the following output. Which gives you the average revenue and sum or the revenue for each department.  In the output you will see that the AverageRevenue and TotalRevenue are the same for the entire range of the departmentID.

Sliding Aggregation

Introducing ROWS in the OVER clause which has been called Windowing Functions, or running or sliding aggregates.

With the addition of ROWS in the over clause, you can either specify to look at ROWS before (PRECEDING) or ROWS after (FOLLOWING), and you can specify the number of ROWS PRECEDING or ROWS FOLLOWING as shown in the following TSQL.

--ROWS PRECEDING
select Year, DepartmentID, Revenue,
sum(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as Prev3
from REVENUE
order by departmentID, year;

In this example as shown in the output below, you can see the in row 1, the prev3 column shows 10030, or the just the same thing as the current row. In the second row it shows the sum of Revenue from row 1 and row 2. For row 3 Prev3 shows the total of 70030 which is the sum of revenue from rows 1 to 3. Finally Row4 Prev3 shows the current row (4) plus the sum of the previous 3, or a total of 100030. Now on to row five. Row 5, prev3 shows 180000 which is the revenue from row 5, row 4, row 3, row2, but not row1.

Now for following, very similar to preceding, but in the other direction.

</pre>
-- ROWS FOLLOWING

select Year, DepartmentID, Revenue,
 sum(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) as Next3
from REVENUE
order by departmentID, year;
<pre>

The ROWS PRECEDING and ROWS FOLLOWING options in the OVER clause in TSQL 2012 give an easy way to calculate sliding window aggregates in your TSQL code.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!