Re: Analytics question maybe - combine over lapping date rows

From: Maxim Demenko <mdemenko_at_gmail.com>
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

Original text of this message