Calculate Difference between current and previous rows…..CTE and Row_Number() rocks!

Ever wanted to calculate difference between current row’s column to previous row’s column? It has several usages like calculating trends, monitoring changes etc. Several TSQL solutions are available for this problem but Common Table Expressions and Row_Number function in SQL Server 2005 provides an excellent way to solve this. By using CTE to calculate the difference between current and last row gives you excellent performance and can be used for huge tables. Following is the TSQL code to calculate the difference between CurrentPointValue to PreviousPointValue.

—Declare a test Table variable for our sample
Declare @m_TestTable table
(
DateRecorded datetime,
PointValue int
)

—Insert sample data
Insert into @m_TestTable Values (dateadd(day,1,GetDate()),150)
Insert into @m_TestTable Values (dateadd(day,2,GetDate()),350)
Insert into @m_TestTable Values (dateadd(day,3,GetDate()),500)
Insert into @m_TestTable Values (dateadd(day,4,GetDate()),100)
Insert into @m_TestTable Values (dateadd(day,5,GetDate()),150);

—Create CTE
With tblDifference as
(
Select Row_Number() OVER (Order by DateRecorded) as RowNumber,DateRecorded,PointValue from @m_TestTable
)

—Actual Query
Select convert(varchar, Cur.DateRecorded,103) as CurrentDay, convert(varchar, Prv.DateRecorded,103) as PreviousDay,Cur.PointValue as CurrentValue,Prv.PointValue as PreviousValue,Cur.PointValue-Prv.PointValue as Difference from

tblDifference Cur Left Outer Join tblDifference Prv
On Cur.RowNumber=Prv.RowNumber+1
Order by Cur.DateRecorded