Making groups of dates in SQL Server

Question

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

Gordon Linoff · Accepted Answer · 2020-04-19 12:42:35Z

3

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.

answered Apr 19, 2020 at 12:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2020-04-19 13:02:28Z

0

A typical approach to this gaps-and-islands problem is build the groups by comparing the date of the current record to the "previous" date of the same id. When dates are not consecutive, a new group starts:

select id, min(date) from_date, max(date) to_date
from (
    select 
        t.*, 
        sum(case when date = dateadd(day, 1, lag_date) then 0 else 1 end) 
            over(partition by id order by date) grp
    from (
        select 
            t.*, 
            lag(date) over(partition by id order by date) lag_date
        from mytable t
    ) t
) t
group by id, grp
order by id, from_date

edited Apr 19, 2020 at 13:02

answered Apr 19, 2020 at 12:53

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

Making groups of dates in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related