Search code examples
sqlsql-serverwindow-functions

How to join SQL table to itself, based on the previous record?

I have the following rows in SQL Server 2016:

enter image description here

The ID is for the patient, and the LogID is the unique id of the record, it is an identity column.

How can I get the FirstVisitDate of the patients that there dates was changed?

Based on the data in the image, the query should return the row with logID 3 (since the firstVisitDate of the patient has been changed w.r.t to the previous record), and the row with logID 5.

That is:

3--2
5--3

Which means that the row with logID 3 has been changed from it's previous which is logid=2 and the record with logID 5 has been changed from it's previous record which is logid=3

Solution

  • You can use LAG to get the previous value of the FirstVisitDate for each patient and compare that to the current value to see if it has changed:

    SELECT *
FROM (SELECT *, LAG(FirstVisitDate) OVER (PARTITION BY id ORDER BY LogID) AS prev_FVD
      FROM visits) v
WHERE FirstVisitDate != prev_FVD

    Demo on dbfiddle