Grouping SQL results by continuous time intervals (oracle sql)

Question

Hi I have following data in the table:

ID-----startDate----endDate
5549 2008-05-01 4712-12-31
5567 2008-04-17 2008-04-30 1
5567 2008-05-01 2008-07-31 1
5567 2008-09-01 4712-12-31 2
5569 2008-05-01 2008-08-31
5569 2008-09-01 4712-12-31
5589 2008-04-18 2008-04-30
5589 2008-05-01 4712-12-31
5667 2008-05-01 4712-12-31
5828 2008-06-03 4712-12-31
5867 2008-06-03 4712-12-31
6167 2008-11-01 4712-12-31
6207 2008-07-01 4712-12-31
6228 2008-07-01 4712-12-31
6267 2008-07-14 4712-12-31

I am looking for I way to group the continuous time intervals for each id to return:

ID, min(startDate), max(endDate),

to have something like this in result for the bolded ID 5567

5567 2008-04-17 2008-07-31
5567 2008-09-01 4712-12-31

PL/SQL is also an option here :)

Thanks,

Answer 1

I think this will do what you need: (note that it will probably get confused by overlapping ranges; don't know if they're possible in your data set)

select id, min(start_date) period_start, max(end_date) period_end
from
(
select 
    id, start_date, end_date,
    max(contig) over (partition by id order by end_date) contiguous_group
from
(
select 
    id, start_date, end_date,
    case 
        when lag(end_date) over (partition by id order by end_date) != start_date-1 or row_number() over (partition by id order by end_date)=1 
            then row_number() over (partition by id order by end_date) else null end contig
from t2
)
)
group by id, contiguous_group
order by id, period_start
/

Here's the test data that I used - based on yours with a couple extra entries:

create table t2 (id number, start_date date, end_date date);

insert into t2(id, start_date, end_date)values(5549, to_date('2008-05-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5567, to_date('2008-04-17', 'yyyy-mm-dd'), to_date('2008-04-30', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5567, to_date('2008-05-01', 'yyyy-mm-dd'), to_date('2008-07-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5567, to_date('2008-08-01', 'yyyy-mm-dd'), to_date('2008-08-14', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5567, to_date('2009-09-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5567, to_date('2008-11-17', 'yyyy-mm-dd'), to_date('2008-12-13', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5567, to_date('2008-12-14', 'yyyy-mm-dd'), to_date('2008-12-24', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5569, to_date('2008-05-01', 'yyyy-mm-dd'), to_date('2008-08-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5569, to_date('2008-09-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5589, to_date('2008-04-18', 'yyyy-mm-dd'), to_date('2008-04-30', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5589, to_date('2008-05-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5667, to_date('2008-05-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5828, to_date('2008-06-03', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(5867, to_date('2008-06-03', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(6167, to_date('2008-11-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(6207, to_date('2008-07-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(6228, to_date('2008-07-01', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd'));
insert into t2(id, start_date, end_date)values(6267, to_date('2008-07-14', 'yyyy-mm-dd'), to_date('4712-12-31', 'yyyy-mm-dd'));

commit;