CODEWITHSUNDEEP
sqlsql-servert-sql
Mark
Mark

Reputation: 97

SQL - Window Function to Increment Group Number

I have a table of medical visit data. I'd like to determine how frequently patients move from one location to another. My source database is using SQL Server 2008, so LAG window functions aren't possible.

I'd like to start by differentiating each Location_Name change for each patient over time. The Desired_Result column below shows the result that I'm hoping for--each time the Location_Name changes for the same Patient_ID, the column increments by 1. Note that the final change for Patient_ID 1 is to move back to a previous location, which I'd like to treat as a change in location.

Patient_ID | Location_Name | Contact_Date | Desired_Result
1          | Site A        | 1/1/2019     | 1
1          | Site A        | 1/2/2019     | 1
1          | Site B        | 1/3/2019     | 2   
1          | Site B        | 1/4/2019     | 2
1          | Site C        | 1/5/2019     | 3
1          | Site C        | 1/6/2019     | 3
1          | Site C        | 1/7/2019     | 3
1          | Site A        | 1/8/2019     | 4
2          | Site B        | 1/1/2019     | 1
2          | Site B        | 1/4/2019     | 1
2          | Site B        | 1/9/2019     | 1

Is this possible in SQL server 2008? Thank you!

Upvotes: 1

Views: 1623

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is a variation of the groups-and-islands problem. You can use the difference of row numbers to describe the group:

select t.*,
       dense_rank() over (partition by patient_id order by first_contact) as location_name
from (select t.*,
             min(contact_date) over (partition by patient_id, location_name, seqnum - seqnum_2) as desired_result
      from (select t.*,
                   row_number() over (partition by patient_id order by contact_date) as seqnum,
                   row_number() over (partition by patient_id, location_name order by contact_date) as seqnum_2
            from t
           ) t
      ) t;

Upvotes: 4

Related Questions