Skip to Main Content

Query - Merging intervals

arizioOct 5 2009 — edited Oct 5 2009
Hi all,
I need an help for merging multiple intervals in one grouping by a column value.

Here there is an examle
DROP TABLE rj_mytest PURGE;

CREATE TABLE rj_mytest (
 start_date DATE,
 end_date DATE,
 p1 NUMBER,
 p2 NUMBER
);

INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091010000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091011000000', 'YYYYMMDDHH24MISS'), 1, 10);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091011000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091012000000', 'YYYYMMDDHH24MISS'), 1, 11);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091012000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091013000000', 'YYYYMMDDHH24MISS'), 1, 12);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091013000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091014000000', 'YYYYMMDDHH24MISS'), 2, 13);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091014000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091015000000', 'YYYYMMDDHH24MISS'), 2, 10);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091015000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091016000000', 'YYYYMMDDHH24MISS'), 2, 15);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091016000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091017000000', 'YYYYMMDDHH24MISS'), 1, 10);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091017000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091018000000', 'YYYYMMDDHH24MISS'), 1, 12);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091018000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091019000000', 'YYYYMMDDHH24MISS'), 3, 121);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091019000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091020000000', 'YYYYMMDDHH24MISS'), 3, 112);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091120000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091121000000', 'YYYYMMDDHH24MISS'), 1, 210);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091121000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091122000000', 'YYYYMMDDHH24MISS'), 1, 410);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091122000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091123000000', 'YYYYMMDDHH24MISS'), 5, 310);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091123000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091124000000', 'YYYYMMDDHH24MISS'), 5, 210);
INSERT INTO rj_mytest (start_date, end_date, p1, p2)
     VALUES (TO_DATE ('20091124000000', 'YYYYMMDDHH24MISS'), TO_DATE ('20091125000000', 'YYYYMMDDHH24MISS'), 5, 110);


SELECT   a.*
FROM rj_mytest a
ORDER BY a.start_date, a.end_date;

START_DAT END_DATE          P1         P2
--------- --------- ---------- ----------
10-OTT-09 11-OTT-09          1         10
11-OTT-09 12-OTT-09          1         11
12-OTT-09 13-OTT-09          1         12
13-OTT-09 14-OTT-09          2         13
14-OTT-09 15-OTT-09          2         10
15-OTT-09 16-OTT-09          2         15
16-OTT-09 17-OTT-09          1         10
17-OTT-09 18-OTT-09          1         12
18-OTT-09 19-OTT-09          3        121
19-OTT-09 20-OTT-09          3        112
20-NOV-09 21-NOV-09          1        210
21-NOV-09 22-NOV-09          1        410
22-NOV-09 23-NOV-09          5        310
23-NOV-09 24-NOV-09          5        210
24-NOV-09 25-NOV-09          5        110
The result should be:
START_INTERVAL     END_INTERVAL       P1         
---------          ---------          ---------- 
10-OTT-09          13-OTT-09          1        
13-OTT-09          16-OTT-09          2        
16-OTT-09          18-OTT-09          1        
18-OTT-09          20-OTT-09          3        
20-NOV-09          22-NOV-09          1        
22-NOV-09          25-NOV-09          5        
Be careful: the adjacency condition is: END_DATE (row j) = START_DATE (row <> j). There are no overlapping intervals and there are also "holes" between intervals

Thanks a lot,
Riccardo
This post has been answered by Aketi Jyuuzou on Oct 5 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Nov 2 2009
Added on Oct 5 2009
9 comments
7,047 views