CODEWITHSUNDEEP
sqlsql-server
tubby
tubby

Reputation: 2144

Getting difference of value between two adjacent records in SQL Server

(Editing my post with data and expected result as per replies.)

I have a table which looks as follows. I would like to compute the difference in Score between two adjacent records.

ID          TimeStamp               Score
1002010     9/26/2015 11:24:08 PM   32
1002010     9/28/2015 10:12:57 PM   38

This is what I have tried.

SELECT
   [current].ID,
   [current].Score,
   ISNULL(convert(int,[next].Score), 0) - convert(int,[current].Score)
FROM
   RiskPredLog AS [current]
LEFT JOIN
   RiskPredLog AS [next] ON [next].ID = (SELECT MIN(ID) 
                                         FROM TableName 
                                         WHERE ID > [current].ID)
WHERE
   [current].ID = '1002010'

But I always get the difference to be -1.

Expected result

 ID         TimeStamp               Score
 -----------------------------------------------
 1002010    9/26/2015 11:24:08 PM   NULL
 1002010    9/28/2015 10:12:57 PM   6

Upvotes: 0

Views: 145

Answers (3)

Rafał Wojtaszek
Rafał Wojtaszek

Reputation: 668

You can try this:

SELECT  r.ID,
        r.Score,
        r.Score - ISNULL(LEAD(r.Score) OVER (ORDER BY r.TimeStamp DESC),0) AS ComputedScore
FROM    RiskPredLog r

Upvotes: 0

Sam CD
Sam CD

Reputation: 2097

You can implement ROW_NUMBER() in place of lead/lag if you are using pre-2012 SQL Server:

SELECT
   [current].ID,
   [current].Score,
   ISNULL(convert(int,[next].Score), 0) - convert(int,[current].Score)
FROM
   (Select *,ROW_NUMBER() OVER (ORDER BY ID,TimeStamp) as rn from RiskPredLog) AS [current]
LEFT JOIN
   (Select *,ROW_NUMBER() OVER (ORDER BY ID,TimeStamp) as rn from RiskPredLog) AS [next]
      ON [next].rn = [current].rn + 1

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use lead to get the value from the next row and use it for subtraction. Note that this function is available in sql server 2012 and later versions.

If you need null to be the result when there is no leading row, remove the isnull condition.

SELECT
ID,
Score,
ISNULL(convert(int, lead(Score) over(partition by id order by timestamp)), 0) 
- convert(int, Score)
FROM RiskPredLog 
-- where ID = '1002010'

Upvotes: 1

Related Questions