Analytics question maybe - combine over lapping date rows
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