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.