Geek asks and answers

Oracle Timing Report with Overlapping Intervals

I am using Oracle 10g and I have a table with overlapping intervals. In a simplified form:

create TABLE INTERVAL_TEST (
    STARTDATE DATE NOT NULL,
    ENDDATE DATE,
    ITEM VARCHAR2(100) NOT NULL
)

insert into INTERVAL_TEST (STARTDATE, ENDDATE, ITEM) values (to_date('2012-01-01', 'YYYY-MM-DD'), null, 'AAA');
insert into INTERVAL_TEST (STARTDATE, ENDDATE, ITEM) values (to_date('2012-02-01', 'YYYY-MM-DD'), to_date('2012-03-01', 'YYYY-MM-DD'), 'BBB');

As you can see, there are overlapping entries. Zero in the first means that it is really "forever." I need to create a report that shows history on a timeline, indicating the number of valid elements for each row. The output should not be overlapping in time, instead, all valid elements should be listed for each interval where there were no changes:

START       END          VALID_ITEMS
----------  ----------   --------------
2012-01-01  2012-02-01   AAA
2012-02-01  2012-03-01   AAA, BBB
2012-03-01  NULL         AAA

Is there an SQL statement that generates such a result?

+5
source share
1 answer

You can do this in one statement:

SQL> WITH timeline AS
  2          (SELECT mydate startdate,
  3                  lead(mydate) OVER (ORDER BY mydate) - 1 enddate
  4             FROM (SELECT startdate mydate FROM interval_test
  5                   UNION
  6                   SELECT enddate FROM interval_test)
  7            WHERE mydate IS NOT NULL)
  8  SELECT startdate,
  9         enddate,
 10         max(substr(sys_connect_by_path(item, ','), 2)) items
 11    FROM (SELECT t.startdate,
 12                 t.enddate,
 13                 item,
 14                 row_number() OVER (PARTITION BY t.startdate, t.enddate
 15                                    ORDER BY i.item) rn
 16            FROM    timeline t
 17                 JOIN
 18                    interval_test i
 19                 ON nvl(i.enddate, DATE '9999-12-31') - 1 >= t.startdate
 20                AND i.startdate <= nvl(t.enddate, DATE '9999-12-31'))
 21  START WITH rn = 1
 22  CONNECT BY rn = PRIOR rn + 1
 23         AND startdate = PRIOR startdate
 24  GROUP BY startdate, enddate
 25  ORDER BY startdate;

STARTDATE  ENDDATE    ITEMS
---------- ---------- --------------------
2012-01-01 2012-01-31 AAA
2012-02-01 2012-02-29 AAA,BBB
2012-03-01            AAA

I used the first subquery to list all the intervals:

SQL> SELECT mydate startdate,
  2                  lead(mydate) OVER (ORDER BY mydate) - 1 enddate
  3             FROM (SELECT startdate mydate FROM interval_test
  4                   UNION
  5                   SELECT enddate FROM interval_test)
  6            WHERE mydate IS NOT NULL;

STARTDATE  ENDDATE
---------- ----------
2012-01-01 2012-01-31
2012-02-01 2012-02-29
2012-03-01

, :

SELECT max(substr(sys_connect_by_path(item, ','), 2)) items
  FROM (SELECT item, row_number() OVER (ORDER BY item) rn
          FROM interval_test
         WHERE nvl(enddate, DATE '9999-12-31') >= :startdate
           AND startdate <= :enddate)
CONNECT BY rn = PRIOR rn + 1
START WITH rn = 1;
+4

More articles:


All Articles