Reputation: 97
I'm trying to get the difference between two consecutive rows by ID, the two dates are in different columns. I'm thinking it would have to be something with PARTITION BY combined with getting a MAX 2 desc, then grabbing a MAX 1 asc... getting a bit confused by the logic though. Here is a sample.
Before
ID Start_date End_date
1 1/1/2017 2/8/2017
1 2/10/2017 3/8/2017
1 3/21/2017 3/29/2017
1 4/11/2017 5/12/2017
2 4/2/2016 4/3/2016
2 4/6/2016 5/11/2016
3 4/25/2016 4/30/2016
3 5/25/2016 6/29/2016
3 7/13/2016 7/15/2016
3 7/18/2016 8/24/2016
AFTER
ID Start_date End_date new
1 1/1/2017 2/8/2017 NULL
1 2/10/2017 3/8/2017 2
1 3/21/2017 3/29/2017 13
1 4/11/2017 5/12/2017 13
2 4/2/2016 4/3/2016 NULL
2 4/6/2016 5/11/2016 3
3 4/25/2016 4/30/2016 NULL
3 5/25/2016 6/29/2016 25
3 7/13/2016 7/15/2016 14
3 7/18/2016 8/24/2016 3
Upvotes: 1
Views: 328
Reputation: 49270
Use the lag
function to get the previous value and use it in datediff
.
select t.*,datediff(day,lag(end_date) over(partition by id order by start_date),start_date) as diff
from tbl t
LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.
Upvotes: 2