Skip to content
Advertisement

Making groups of dates in SQL Server

I have a table contains ids and dates, I want to groups of dates for each id

 id      date 
 ------------------
  1      2019-01-01
  2      2019-01-01
  1      2019-01-02
  2      2019-01-02
  2      2019-01-03
  1      2019-01-04
  1      2019-01-05
  2      2019-01-05
  2      2019-01-06

I want to check where are gaps in date for each id to get output like

id      from             to
------------------------------------
 1      2019-01-01       2019-01-02
 1      2019-01-04       2019-01-05
 2      2019-01-01       2019-01-03
 2      2019-01-05       2019-01-06

Advertisement

Answer

This is a form of gaps-and-islands problem. The simplest solution is to generate a sequential number for each id and subtract that from the date. This is constant for dates that are sequential.

So:

select id, min(date), max(date)
from (select t.*, row_number() over (partition by id order by date) as seqnum
      from t
     ) t
group by id, dateadd(day, -seqnum, date)
order by id, min(date);

Here is a db<>fiddle.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement