Search code examples
sqlsql-serverdatedifferencedatediff

How doing a datediff between current and next date?

I want to do a datediff between two columns as explain in the example:

 date 1    date 2
 date 1.1  date 2.1
 date 1.2  date 2.2
 date 1.3  date 2.3
 date 1.4  date 2.4

I want to do datediff (date 2.1,date 1.2), datediff (date 2.2,date 1.3)? datediff (date 2.3,date 1.4) ...

Doing the difference between the current of date 2 et the next of date 1.

Any help please?

Solution

  • You can use lag() or lead() . . . it is hard to tell which direction. But something like this:

    select t.*,
       datediff(day,
                lag(end_date) over (order by start_date),
                start_date
               ) as dates_between
from t;