Re: Analytics question maybe - combine over lapping date rows
Date: Sat, 28 Jun 2008 02:03:15 +0200
Message-ID: <48657FC3.8050902@gmail.com>
stephen O'D schrieb:
> 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.
Maybe, something like this
SQL> select key, min(start_dtm) start_dtm, max(end_dtm) end_dtm
2 from (select key,
3 start_dtm, 4 end_dtm, 5 sum(interval_change) over(partition by key order by end_dtm, start_dtm) interval_no 6 from (select key, 7 start_dtm, 8 end_dtm, 9 case 10 when (lag(end_dtm) 11 over(partition by key order by end_dtm, 12 start_dtm) - start_dtm) > 0 then 13 0 14 else 15 1 16 end interval_change 17 from t1))
18 group by key, interval_no
19 order by key, interval_no
20 ;
KEY START_DTM END_DTM
---------- -------------------- -------------------- 1234 01-Jan-2001 15-Apr-2001 1234 01-Jul-2001 30-Dec-2001
Best regards
Maxim Received on Fri Jun 27 2008 - 19:03:15 CDT