I need to write a query that displays time intervals that go on without interruption. Example:
Input:
create table calc(Id int, StartDate DATE, EndDate DATE); insert into calc values(1, '2019-01-01', '2019-01-02'); insert into calc values(2, '2019-01-02', '2019-01-03'); insert into calc values(3, '2019-01-03', '2019-01-04'); insert into calc values(4, '2019-01-14', '2019-01-15'); insert into calc values(5, '2019-01-16', '2019-01-17'); insert into calc values(6, '2019-01-17', '2019-01-18'); insert into calc values(7, '2019-01-25', '2019-01-26'); insert into calc values(8, '2019-02-03', '2019-02-04'); insert into calc values(9, '2019-02-04', '2019-02-05'); insert into calc values(10, '2019-03-01', '2019-03-02');
Output:
StartDate , EndDate '2019-01-01', '2019-01-04' '2019-01-14', '2019-01-15' '2019-01-16', '2019-01-18' '2019-01-25', '2019-01-26' '2019-02-03', '2019-02-05' '2019-03-01', '2019-03-02'
I think we need to use the function DATEDIFF
line by line.
The problem is that I don’t know how to access indexes. Or can this problem be solved much easier?
Advertisement
Answer
This is a type of gaps-and-islands problem. Use a lag()
to see if adjacent rows overlap. Do a cumulative sum of gaps, and then aggregate:
select min(startdate), max(enddate) from (select c.*, sum(case when prev_ed = startdate then 0 else 1 end) over (order by startdate) as grp from (select c.*, lag(enddate) over (order by startdate) as prev_ed from calc c ) c ) c group by grp order by min(startdate);
Here is a db<>fiddle.