How do i compare dates and split date ranges with overlaps accordingly?

I have a table with data as follows:

6-SEP-08       10-SEP-08        20
8-SEP-08       12-SEP-08        30

I want the result as follows:

6-SEP-08       7-SEP-08         20
8-SEP-08      10-SEP-08        20 + 30
11-SEP-08    12-SEP-08        30

I found a longer way to do the above. I split the dates individually.

DATE           PERCENT
6-SEP-08      20
7-SEP-08      20
8-SEP-08     20 + 30
9-SEP-08     20 + 30
10-SEP-08   20 + 30
11-SEP-08   30
12-SEP-08   30

Is there a shorter way of getting the result i expect? Kindly advice.
Who is Participating?
Shaju KumbalathConnect With a Mentor Deputy General Manager - ITCommented:
Sorry there is a currection

SELECT sdate,
LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", '+' || percent)), '/x/text()'),
FROM (select distinct trunc(start_dt)+level-1 sdate,percent from your_table
connect by trunc(start_dt)+level-1<= trunc(end_dt)
GROUP BY sdate  
will there be at most 2 date ranges overlapping?
sdinesh21Author Commented:
Thanks for your reply.

For your question......Nope. I just cut short while posting. . It may go on and on... more than 2...
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Shaju KumbalathDeputy General Manager - ITCommented:
Try this

SELECT sdate,
LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", '+' || perc)), '/x/text()'),
FROM (select distinct trunc(start_date)+level sdate,perc from your_table
connect by trunc(start_date)+level< trunc(end_date)
GROUP BY sdate
sdinesh21Author Commented:
shajukg, Thanks for the solution suggested.. But when i tried executing it, it took a lot of time.. Costlier query! Any other ideas you could suggest? A Proc or a query will do... Please reply...
Shaju KumbalathDeputy General Manager - ITCommented:
" connect by trunc(start_dt)+level-1<= trunc(end_dt)  "
if  the time is null then u can remove trunc and try
connect by start_dt+level-1<= end_dt  
there are several methods for doing the same, u could use Stragg function or sys connect by path method
refer the below mentioned link for details

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.