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

I have a table with data as follows:

START_DT     END_DT         PERCENT
6-SEP-08       10-SEP-08        20
8-SEP-08       12-SEP-08        30

I want the result as follows:

START_DT     END_DT          PERCENT
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.
sdinesh21Asked:
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  
0
 
sdstuberCommented:
will there be at most 2 date ranges overlapping?
0
 
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...
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
0
 
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...
0
 
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
http://www.oratechinfo.co.uk/delimited_lists_to_collections.html

 
0
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.