Search code examples
sqlsql-servernorthwind

SQL write query that show the skipping between dates

I try to write a query that will find hops by showing the date before the start of the jump and the first date after the skip.

I tried:

SELECT OrderDate FROM Orders

and I got:

enter image description here

but I don't succeed to split them to gaps like this:

enter image description here

How can I do that?

Solution

  • Use can use dense_rank() to generate a sequence. When you subtract this from the date, you will identify adjacent sequential dates.

    So:

    select min(orderdate), max(orderdate)
from (select o.*, dense_rank() over (order by orderdate) as seqnum
      from orders o
     ) o
group by dateadd(day, -seqnum, orderdate)
order by min(orderdate);

    You need dense_rank() because your data has duplicates.

    EDIT:

    To find jumps, use LEAD():

    select dateadd(day, 1, orderdate),
       dateadd(day, -1, next_orderdate)
from (select orderdate, lead(orderdate) over (order by orderdate) as next_orderdate
      from (select distinct orderdate orders o) o
     ) o
where next_orderdate > dateadd(day, 1, orderdate);