I am working on clinical data to find total sessions for patients by finding date difference. I can achieve this using Python but power bi does not support that script. So I needed help to do this in SQL. I am basic level user of SQL so have no idea how to start for this.
Below is raw data
| PatientID | SessionDate |
|---|---|
| 264 | 5/26/2021 |
| 264 | 5/27/2021 |
| 264 | 5/28/2021 |
| 264 | 5/19/2021 |
| 264 | 5/20/2021 |
| 264 | 5/21/2021 |
| 264 | 5/24/2021 |
| 264 | 5/25/2021 |
| 264 | 5/17/2021 |
| 264 | 5/18/2021 |
| 281 | 5/18/2021 |
| 281 | 5/25/2021 |
| 281 | 6/29/2021 |
| 281 | 6/1/2021 |
| 281 | 4/16/2020 |
| 281 | 4/23/2020 |
| 281 | 4/21/2020 |
| 281 | 4/28/2020 |
| 281 | 4/30/2020 |
| 281 | 5/5/2020 |
| 281 | 10/16/2019 |
Expected output is as below
Advertisement
Answer
Based on @Akina hint I was able to get the required output from following query Note:- My sessionDate column was in Date and timestamp format.
select
t.PatientID,
CAST(t.SessionTimestamp as Date) as SessionDate,
LAG(t.SessionTimestamp) over(partition by t.PatientRecordId order by t.PatientID,t.SessionTimestamp) as Next_Date
from mytable t
order by
t.PatientID,
t.SessionTimestamp