Identify date range and merge into max and min dates

Question

I have data ( int, date , date types)

SELECT * FROM 
(
  VALUES 
     (1700171048,'2020-12-21','2021-01-03'), 
     (1700171048,'2021-01-05','2021-01-12'), 
     (1700171048,'2021-01-13','2021-01-17'), 
     (1700171048,'2021-01-18','2021-01-19'),
     (1700171048,'2021-01-22','2021-01-27'),
     (1700171048,'2021-01-28','2021-02-17')
     
     (1700171049,'2020-12-21','2021-01-03'), 
     (1700171049,'2021-01-04','2021-01-05'), 
     (1700171049,'2021-01-06','2021-01-17'),
     (1700171049,'2021-01-18','2021-01-19'),
     (1700171049,'2021-01-20','2021-01-27'),
     (1700171049,'2021-01-28','2021-02-17')

 ) AS c (id1, st, endt )

I need output( i.e. if start and end dates are continuous then make it part of group )

 id1         st             endt
 1700171048 '2020-12-21' , '2021-01-03'
 1700171048 '2021-01-05' , '2021-01-19'
 1700171048 '2021-01-22' , '2021-02-17'

 1700171049 '2020-12-21' to '2021-02-17'

I tried this, won't work.

    select id, case when min(b.st) = max(b.endt) + 1 then min(b.st) end,
            case when min(b.endt) = min(b.st) + 1 then max(b.st) end 
     from c a  join c b
   group by id

Welcome to Stack Overflow. Please indicate what RDBMS you're using (MySQL, MSSQL, Oracle, ...) and version. Especially when it comes to working with dates, there are differences between RDBMS solutions. Also your desired output doesn't really explain (to me) what you're trying accomplish. Please use words to beef up our understanding of your goal. — Dennis
– Dennis, Commented Feb 19, 2021 at 16:02

Gordon Linoff · Accepted Answer · 2021-02-19 15:56:45Z

0

This is a type of gaps-and-islands problem. Use lag() to identify if there is an overlap. Then a cumulative sum of when there is no overlaps and aggregation:

select id1, min(st), max(endt)
from (select t.*,
             sum(case when prev_endt >= st + interval '-1 day' then 0 else 1 end) over (partition by id1 order by st) as grp
      from (select t.*,
                   lag(endt) over (partition by id1 order by st) as prev_endt
            from t
           ) t
     ) t
group by id1, grp;

Here is a db<>fiddle.

answered Feb 19, 2021 at 15:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Identify date range and merge into max and min dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related