Analytics question maybe - combine over lapping date rows

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: Fri, 27 Jun 2008 09:01:22 -0700 (PDT)
Message-ID: <2a767f93-c426-4bd4-b708-41a43f589bad@2g2000hsn.googlegroups.com>


On Oracle 10G, lets say I have a table that looks like this:

create table t1 (key varchar2(10), start_dtm date, end_dtm date);

and I have data some of which over laps:

insert into t1 values ('1234', to_date('01/01/2001', 'DD/MM/YYYY'), to_date('30/03/2001', 'DD/MM/YYYY'));
insert into t1 values ('1234', to_date('01/02/2001', 'DD/MM/YYYY'), to_date('30/03/2001', 'DD/MM/YYYY'));
insert into t1 values ('1234', to_date('15/02/2001', 'DD/MM/YYYY'), to_date('15/04/2001', 'DD/MM/YYYY'));
insert into t1 values ('1234', to_date('01/07/2001', 'DD/MM/YYYY'), to_date('30/12/2001', 'DD/MM/YYYY'));

Ie
1st Jan - 30th March

    2nd Feb - 30th March

       15th Feb - 15th April

1st July - 30th Dec

I want to return only two rows from the 4 that look like

1st jan - 15th April
1st July - 30th Dec

ie - if the dates are continuous, squash all the continuous rows into 1 with the min start and max end date
but if there is a gap it has to become a second row. There can be any number of rows and over laps etc.

I reckon this can be done with some analytics trickery, but I just cannot seem to figure out how.

Any ideas?

Thanks,

Stephen. Received on Fri Jun 27 2008 - 11:01:22 CDT

Original text of this message