Grouping SQL results by continous 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

you will have to write a PL/SQL block sample logic as below;

Create or Replace someproc
Declare
    Cursore someCur AS
    Select * from someTable
    Order by ID,StartDate

    IDVar as Varchar(10)
    MinDate as DATE
    MaxDate as DATE

Begin
    Open someCur
    Fetch ID,StartDate,EndDate into IDVar,MinDate,MaxDate
    While SomeCur%NOTFOUND
    LOOP
        Fetch ID,StartDate,EndDate into TempID,TempStartDate,TempEndDate
        if IDVar <> TempID then
            -- output into your required structure values: IDVar,MinDate,MaxDate
            IDVar = TempID
            MinDate = TempStartDate
            MaxDate = TempEndDate
            Exit Loop
        ELSE IF
            MaxDate+1 >= TempStartDate THEN
            MaxDate = TempEndDate
        END IF
    End LOOP

Answer 2

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;

Answer 3

You could do this with analytic functions like this:

with d as
( select id, start_date, end_date
  ,      case when start_date = prev_end+1 
              then 'cont' else 'new' end start_status
  ,      case when end_date = next_start-1
              then 'cont' else 'new' end end_stat
  from
  (
  select id, start_date, end_date
  ,      lag(end_date) over (partition by id order by start_date) prev_end
  ,      lead(start_date) over (partition by id order by start_date) next_start
  from t1
  order by id, start_date
  )
)
select starts.id, starts.start_date, ends.end_date
from
( select id, start_date, row_number() over (order by id, start_date) rn
  from   d
  where  start_status='new'
) starts,
( select id, end_date, row_number() over (order by id, start_date) rn
  from   d
  where  end_status='new'
) ends
where starts.rn = ends.rn

I get this result with your data:

        ID START_DATE END_DATE
---------- ---------- ----------
      5549 2008-05-01 4712-12-31
      5567 2008-04-17 2008-07-31
      5567 2008-09-01 4712-12-31
      5569 2008-05-01 4712-12-31
      5589 2008-04-18 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

12 rows selected.

How it works:

1. The WITH clause generates a view D of the data where each row is assigned a "start status" and an "end status", each of which is 'new' or 'cont' to indicate whether it is continuous with the previous/next row or not.
2. In-line views "starts" and "ends" pull out only the rows that have a "new" start status / end status respectively, with a row number to marry them up.
3. The "main query" then selects from these 2 views and joins on the row number column.

Answer 4

I'm not near an instance to test, but have you tried;

SELECT
 ID, 
 startDate,
 endDate
FROM
 myTable
WHERE
 (ID, startDate) in 
 (SELECT
   ID, 
   min(startDate) 
  FROM
   myTable
  GROUP BY
   ID
  )

  or 

 (ID, endDate) in 
 (SELECT
   ID, 
   max(endDate) 
  FROM
   myTable
  GROUP BY
   ID
  )

This should give you all the earliest startDates, and the latest endDates for each ID. Continuous or not.

Answer 5

Hi,

nice solution!

Just wundering, how you could merge these time stamps: (periods can be included in the previous on...)

Start End

16.03.2010 07:00 16.03.2010 09:00

16.03.2010 08:00 16.03.2010 16:00

16.03.2010 10:00 16.03.2010 12:00

16.03.2010 12:00 16.03.2010 18:00

Would be great to get an answer here - using Oracle 9 (no connect_by_root available)

Thx Dirk

Answer 6

If there is a time period where you have to check continuity then ??? solution plz

Answer 7

I have been trying to do for overlapping ranges but getting some difficuly with these data : INSERT INTO zzz_scrap_dates (id,effdate,termdate)

SELECT id,effdate,termdate from ( SELECT '1'id ,To_Date('2000-01-01','YYYY-MM-DD')effdate,To_Date('2020-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '1'id ,To_Date('2000-01-01','YYYY-MM-DD')effdate,To_Date('2010-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '1'id ,To_Date('2005-01-01','YYYY-MM-DD')effdate,To_Date('2020-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '1'id ,To_Date('2000-01-01','YYYY-MM-DD')effdate,To_Date('2020-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '1'id ,To_Date('1999-01-01','YYYY-MM-DD')effdate,To_Date('2020-01-31','YYYY-MM-DD')termdate FROM dual union SELECT '2'id ,To_Date('2000-01-01','YYYY-MM-DD')effdate,To_Date('2020-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '2'id ,To_Date('2000-01-01','YYYY-MM-DD')effdate,To_Date('2010-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '2'id ,To_Date('2005-01-01','YYYY-MM-DD')effdate,To_Date('2020-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '2'id ,To_Date('2000-01-01','YYYY-MM-DD')effdate,To_Date('2020-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '2'id ,To_Date('1999-01-01','YYYY-MM-DD')effdate,To_Date('2020-01-31','YYYY-MM-DD')termdate FROM dual union SELECT '3'id ,To_Date('2000-01-01','YYYY-MM-DD')effdate,To_Date('2020-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '3'id ,To_Date('1998-01-01','YYYY-MM-DD')effdate,To_Date('1999-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '3'id ,To_Date('1005-01-01','YYYY-MM-DD')effdate,To_Date('1197-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '3'id ,To_Date('2000-01-01','YYYY-MM-DD')effdate,To_Date('2020-01-31','YYYY-MM-DD')termdate FROM dual UNION SELECT '3'id ,To_Date('1197-01-01','YYYY-MM-DD')effdate,To_Date('2020-01-31','YYYY-MM-DD')termdate FROM dual