SQL with Manoj

First of all I would like to wish my readers a very Happy and Prosperous New Year – 2013!!!

This is my 1st post in this new year and I will try to come up with more informative and interesting topics related to SQL Server.

Coming back to the topic, sometime back in Part-1 [link] I discussed about how to use both PARTITION BY & ORDER BY clause within OVER() clause when using it with AGGREGATE functions, like SUM().

Here in this post we will see other features that are added to the OVER() clause.

ROWS/RANGE are new the 2 new clauses that you can use inside the OVER() clause to limit the records within the Partition by specifying start and end within the partition. They use a new set of keywords to define the boundaries within the partition, i.e. PREECEDING, FOLLOWING mixed with UNBOUNDED or CURRENT, or number of rows. The PRECEDING and FOLLOWING rows are defined based on the ordering in the ORDER BY clause of the query.

#1. Using ROWS/RANGE UNBOUNDED PRECEDING with OVER() clause:
Let’s check with 1st example where I want to calculate Cumulative Totals or Running Totals at each row. This total is calculated by the SUM of current and all previous rows.
We will use UNBOUND PRECEDING option here, which means that the window starts from the 1st row till the current row.

USE [AdventureWorks2012]
GO

-- To Calculate Cumulative SUM or Running Totals:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS UNBOUNDED PRECEDING) AS CumulativeSumByRows
   ,SUM(Salary) OVER (ORDER BY SalesPersonID RANGE UNBOUNDED PRECEDING) AS CumulativeSumByRange
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:

In the Output above you can see the was the SUM is calculated, as we go down all previous and current rows are getting summed at all levels.
Here you can also see a difference between the way ROW & RANGE are calculated, for SalesPersonID=4:
– ROWS shows different and continuous calculation at different levels.
– But RANGE shows same calculation of all 3 records at different levels.

#2. Using ROWS/RANGE CURRENT ROW & UNBOUNDED FOLLOWING with OVER() clause:
Now, let’s say you’ve to do the same calculation, but in reverse order. So, here we will use BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING options to define our Window. This means the window starts from current row and ends at the last row.

-- To Calculate Cumulative SUM or Running Totals, but in REVERSE order:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ReverseCumulativeSumByRows
   ,SUM(Salary) OVER (ORDER BY SalesPersonID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ReverseCumulativeSumByRange
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:

In the Output above we can see that the Running totals are now in reverse order, compared to the 1st example.
Also, the ROWS & RANGE difference can be seen for the SalesPersonID=4 similar to the 1st example.

#3. Using ROWS/RANGE PRECEDING & FOLLOWING with OVER() clause, without UNBOUNDED:
Now, if you want to calculate the SUM only of those values that are just adjacent to a current row. Here we will use combination of row numbers with CURRENT/PRECEDING & FOLLOWING keywords.
Let’s us check this by following example:

-- To Calculate Moving SUM, by taking 3 moving rows:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MovingSumByRowsPrevCurrentNext
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS MovingSumByRowsCurrentNext2
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:

In the output above for:
– Column MovingSumByRowsPrevCurrentNext: it calculates the SUM of just one Previous, Current & one Next row.
– Column MovingSumByRowsCurrentNext2: it calculates the SUM of Current and next two rows.

So, by above 3 examples we saw how to use new ROWS & RANGE clauses with a mix of UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW, etc. Similarly there could be many different cases and scenarios where we can use there clauses and options.

I will try to post more on these features as I see anything new and exciting around this.

For more info on OVER() Clause check MS BOL: http://msdn.microsoft.com/en-us/library/ms189461.aspx