SQL Tips by Namwar Rizvi

May 21, 2007

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

About these ads

16 Comments »

  1. 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 | Reply

  2. Thanks Colleen for appreciating the effort.

    Comment by namwar — October 23, 2007 @ 3:20 PM | Reply

  3. 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 | Reply

  4. 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 | Reply

  5. 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 | Reply

  6. 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 | Reply

    • 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 | Reply

  7. 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 | Reply

  8. 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 | Reply

  9. Thank you !!!

    Comment by john — March 28, 2011 @ 6:24 PM | Reply

  10. 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 | Reply

  11. nice article. clear understandig.

    Comment by jagz w — October 12, 2011 @ 5:53 AM | Reply

  12. Nice Example…Thanks..!!!!

    Comment by Akshay — February 15, 2012 @ 9:19 AM | Reply

  13. Thanks you very much. This is perfect example !!

    Comment by Hiten — March 27, 2012 @ 7:06 PM | Reply

  14. Than you , very nice example…

    Comment by sandesh — June 18, 2012 @ 11:54 AM | Reply

  15. 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: