Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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 on Nov 2 2009
Added on Oct 5 2009
9 comments
7,315 views