Solved

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

Posted on 2008-09-29
8
643 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
ID: 22599089
will there be at most 2 date ranges overlapping?
0
 

Author Comment

by:sdinesh21
ID: 22599389
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
ID: 22604765
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 15

Accepted Solution

by:
Shaju Kumbalath earned 20 total points
ID: 22604818
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
ID: 22624871
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
ID: 22631505
" 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle -- identify blocking session 24 41
sort a spool into file output in oracle 1 22
su - oracle could not open session 6 47
Oracle sql query 7 39
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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…

932 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

13 Experts available now in Live!

Get 1:1 Help Now