CODEWITHSUNDEEP
sqlsql-server
jay-B
jay-B

Reputation: 11

Difference between values with same ID's between selected date and previous days date

I am trying to solve a problem I have whereby I need to display the difference in values from the same column which has the same IDs, between selected date and selected date - 1 taking business days into account.

I have a sample below of what the data would look like in the database

ID   ValuationDate  Value
=========================
317  07/04/2022     3456
246  07/04/2022     851
317  06/04/2022     2987
246  06/04/2022     853

and the desired output would be

ID   Difference
==============
317   469
246   -2

Any help would be much appreciated. I have tried multiple YouTube videos as well as trying to use lag function, unfortunately, my SQL skills are rather raw.

Upvotes: 0

Views: 108

Answers (1)

mikkapy
mikkapy

Reputation: 279

-- Query for UNIQUE(id, ValuationDate)

SELECT id
     , ISNULL(ct1.Value - ct2.Value, 0) Difference -- isnull(...,0) - if no previous value 
  FROM <Current table> ct1
  LEFT
 OUTER
  JOIN <Current table> ct2
    ON ct1.id = ct2.id
    and DATEDIFF(day, ct2.ValuationDate,ct1.ValuationDate) = 1

Upvotes: 1

Related Questions