Solved

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

Posted on 2008-09-29
8
641 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:sdinesh21
  • 3
  • 2
8 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
will there be at most 2 date ranges overlapping?
0
 

Author Comment

by:sdinesh21
Comment Utility
Thanks for your reply.

For your question......Nope. I just cut short while posting. . It may go on and on... more than 2...
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 15

Accepted Solution

by:
Shaju Kumbalath earned 20 total points
Comment Utility
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
 

Author Comment

by:sdinesh21
Comment Utility
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
Comment Utility
" 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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MySQL Error 3 34
null value 15 58
Selecting specific rows 3 34
Best RAID for a BDD Oracle 4 15
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now