Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

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.
0
sdinesh21
Asked:
sdinesh21
  • 3
  • 2
1 Solution
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Shaju KumbalathDeputy 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
 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now