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
I searched and searched, and this is the clearest explanation I could find for calculating the difference between row values. Thank you!!!
Comment by colleen — October 23, 2007 @ 2:38 PM |
Thanks Colleen for appreciating the effort.
Comment by namwar — October 23, 2007 @ 3:20 PM |
brilliant! I agree with colleen that this is the best example I’ve seen for finding the difference.
Comment by cdf — January 3, 2008 @ 9:20 PM |
looking for a simple and fast way to analyze the disk growth data I have been tracking. This is just what I was looking for.
Comment by Dustin W. Jones — January 25, 2008 @ 9:13 PM |
Very helpful. Before this technique, I had 2 sets of selects/group bys that I joined with a union, and then a 3rd select would aggregate on the output of the first two. This method is so much easier and works with more than 2 time periods. Thank you.
Comment by mb — February 15, 2008 @ 6:49 PM |
I have a problem in sql server 2005. Please any one could suggest it.
Table Name:
There are no identity columns, primary key columns on this table.
DayId DayName Temperature
——– ———— ——————-
SN Sunday 32
MN Monday 39
TU Tuesday 36
WD Wednesday 45
TH Thursday 41
FR Friday 47
SA Satruday 40
Query: Calulate the temperature difference on Temperature column
Output should be:
DayId DayName Temperature DifferenceTemperature
——– ———— ——————- ——————
SN Sunday 32 6
MN Monday 39 -3
TU Tuesday 36 9
WD Wednesday 45 -4
TH Thursday 41 6
FR Friday 47 -7
SA Satruday 40 null
Please explain the query
Comment by Ramesh — May 23, 2008 @ 11:12 AM |
Hi , Ramesh
dou remember this query? have you resolved the problem?
Query: Calulate the temperature difference on Temperature column
Output should be:
DayId DayName Temperature DifferenceTemperature
——– ———— ——————- ——————
SN Sunday 32 6
MN Monday 39 -3
TU Tuesday 36 9
WD Wednesday 45 -4
TH Thursday 41 6
FR Friday 47 -7
SA Satruday 40 null
Please explain the query
Comment by Ramesh — May 23, 2008 @ 11:12 am | Reply
Comment by fabio — August 27, 2009 @ 2:00 PM |
If nothing else works try:
SELECT (SELECT TOP 1 t2.DateTimeValue FROM tableName t2 WHERE t2.DateTimeValue > t1.DateTimeValue)-t1.DateTimeValue AS DateTimeDifference
FROM tableName t1
This takes a while but gives you what you need. By the end of the day you might consider adding a column to you table representing the duration.
Comment by Pete — January 28, 2010 @ 4:49 PM |
There are some articles which says row_number() is slow when number of records > 1 million or some complex query is there? Can you please tell me about its performance in those cases
Comment by Guru — September 20, 2010 @ 7:13 PM |
Thank you !!!
Comment by john — March 28, 2011 @ 6:24 PM |
What about comparing non-numerical values? As opposed to trending, this would be more like change control tracking. I want to find the action taken by a user to an asset based on an asset ID. For instance, a product called Foo has a color description added to it, when one didn’t exist before. ProductID, ProductName, ProductColor, ModifiedByUserId, ModifiedDate. I want to see that the difference between rows is that one has a color, and one doesn’t, and the date that the color was added.
Comment by Jon Mitten — July 22, 2011 @ 10:07 PM |
nice article. clear understandig.
Comment by jagz w — October 12, 2011 @ 5:53 AM |
Nice Example…Thanks..!!!!
Comment by Akshay — February 15, 2012 @ 9:19 AM |
Thanks you very much. This is perfect example !!
Comment by Hiten — March 27, 2012 @ 7:06 PM |
Than you , very nice example…
Comment by sandesh — June 18, 2012 @ 11:54 AM |
Hmm is anyone else encountering problems with the pictures on this blog loading?
I’m trying to figure out if its a problem on my end or if it’s the blog.
Any feed-back would be greatly appreciated.
Comment by Filemaker GO — April 4, 2013 @ 6:21 AM |
I want to count number of rows in he table, need to skip counting if consecutive row has same value. Ezample
A1 1st Jan 1000 1200
A1 2nd Jan 1010 1200
A1 3rd Jan 1010 1200
A1 4th Jan 1010 1220
in this case count should be 3, after checking 3rd and 4th column.
How to achieve this?
Comment by azmina — July 21, 2014 @ 8:08 PM |
thanks a lot perfect solution
Comment by d — November 30, 2015 @ 10:10 AM |
Perfect!!! Thank you
Comment by vibha — May 5, 2016 @ 12:03 AM |
There is a better solution to that in the meantime 🙂
SELECT convert(varchar, DateRecorded,103) as CurrentDay
,LAG(CONVERT(varchar,DateRecorded,103), 1, NULL) OVER (ORDER BY DateRecorded ASC ) As PreviousDay
,PointValue as CurrentValue
,LAG(PointValue, 1, NULL) OVER (ORDER BY DateRecorded ASC ) As PreviousValue
,PointValue-LAG(PointValue, 1, NULL) OVER (ORDER BY DateRecorded ASC ) as Difference
FROM @m_TestTable;
Comment by Martin Schlender — November 18, 2016 @ 1:54 PM |