Original table is as below, the table has been ordered by GroupId and Date in acceding. I'd like to calculate the day difference between current and next row for the same GroupId
| GroupId | Date |
|---|---|
| 1 | 2022-02-20 00:00:00.000 |
| 1 | 2022-02-27 00:00:00.000 |
| 1 | 2022-03-02 00:00:00.000 |
| 2 | 2022-02-03 00:00:00.000 |
| 2 | 2022-02-17 00:00:00.000 |
The target output should be like this:
| GroupId | Date | Previous_Date | Day_Difference |
|---|---|---|---|
| 1 | 2022-02-20 00:00:00.000 | null | null |
| 1 | 2022-02-27 00:00:00.000 | 2022-02-20 00:00:00.000 | 7 |
| 1 | 2022-03-02 00:00:00.000 | 2022-02-27 00:00:00.000 | 3 |
| 2 | 2022-02-03 00:00:00.000 | null | null |
| 2 | 2022-02-17 00:00:00.000 | 2022-02-03 00:00:00.000 | 14 |
I got the script as below, but it's getting the Previous_Date from the last row and do the calculation, but I would like to keep the Previous_Date and Day_Difference as Null for the first row as the target table above. Can someone please help?
My script is:
SELECT
GroupId,
[Date],
lag([Date]) OVER (ORDER BY [Date]) as Previous_Date,
DATEDIFF(day, lag([Date]) OVER (ORDER BY [Date]), [Date]) AS Day_Difference
FROM TestTable
order by GroupId
CodePudding user response：
You can try to use PARTITION BY GroupId in OVER clause. PARTITION BY that divides the query result set into partitions.
SELECT
GroupId,
[Date],
lag([Date]) OVER (PARTITION BY GroupId ORDER BY [Date]) as Previous_Date,
DATEDIFF(day, lag([Date]) OVER (PARTITION BY GroupId ORDER BY [Date]), [Date]) AS Day_Difference
FROM TestTable
order by GroupId