0

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.

jay-B
  • 11
  • 1
  • As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Apr 07 '22 at 09:30
  • I will do as you have mentioned and try my best to correct the initial post – jay-B Apr 07 '22 at 09:34
  • is pair (id, ValuationDate) unique? – mikkapy Apr 07 '22 at 09:41
  • Exactly what do You mean by "taking business days in to account"? – MatBailie Apr 07 '22 at 09:54
  • @smvenk yes, the pair is unique – jay-B Apr 07 '22 at 10:22
  • 1
    Relevant: [Is there a way to access the "previous row" value in a SELECT statement?](https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) Sounds like you just need `LAG(Value) OVER (PARTITION BY ID ORDER BY ValuationDate)` – Charlieface Apr 07 '22 at 11:28

1 Answers1

1
-- 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
mikkapy
  • 254
  • 2
  • 4
  • Oh wow, this exactly what I was looking for. Thank you so much for your help. I am so appreciative. I tried to upvote your answer, but am unable to. – jay-B Apr 07 '22 at 10:24