Home » SQL & PL/SQL » SQL & PL/SQL » Combining overlapping dates (Oracle 10g)
Combining overlapping dates [message #563606] Wed, 15 August 2012 16:41 Go to next message
anjulis
Messages: 32
Registered: August 2012
Member
Lets say I have a table with 4 records with start_date and end_date fields

start_date end_date
1/1/2003 1/4/2005
1/1/2004 5/3/2006
1/1/2007 1/4/2008
1/1/2008 6/3/2009

as you see records 1,2 and records 3,4 have overlapping dates. I need to combine them to get continuous intervals. The result should be:

start_date end_date
1/1/2003 5/3/2006
1/1/2007 6/3/2009

Is it possible to do by SQL query?
Re: Combining overlapping dates [message #563608 is a reply to message #563606] Wed, 15 August 2012 19:25 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Combining overlapping dates [message #563619 is a reply to message #563606] Thu, 16 August 2012 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Is "1/4/2005" April 1st or January 4th?

Regards
Michel
Re: Combining overlapping dates [message #563622 is a reply to message #563619] Thu, 16 August 2012 01:09 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
One Question, What is overlapping dates ?

It Looks like everything is overlapping, Then there will be just one record: 1/1/2003 6/3/2009

regards,
Veeresh
Re: Combining overlapping dates [message #563624 is a reply to message #563622] Thu, 16 August 2012 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, not everything is overlapping, carefully read the exemple (even if we don't know if it is DD/MM or MM/DD).

Regards
Michel
Re: Combining overlapping dates [message #563650 is a reply to message #563606] Thu, 16 August 2012 06:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
Registered: January 2010
Senior Member
anjulis wrote on Wed, 15 August 2012 17:41
Is it possible to do by SQL query?


WITH t AS (
  SELECT TO_DATE('1/1/2003','mm/dd/yyyy') start_date,TO_DATE('1/4/2005','mm/dd/yyyy') end_date FROM dual 
  UNION ALL           
  SELECT TO_DATE('1/1/2004','mm/dd/yyyy') start_date,TO_DATE('5/3/2006','mm/dd/yyyy') end_date FROM dual 
  UNION ALL
  SELECT TO_DATE('1/1/2007','mm/dd/yyyy') start_date,TO_DATE('1/4/2008','mm/dd/yyyy') end_date FROM dual 
  UNION ALL
  SELECT TO_DATE('1/1/2008','mm/dd/yyyy') start_date,TO_DATE('6/3/2009','mm/dd/yyyy') end_date FROM dual
          )
SELECT  min(start_date) start_date,
        max(end_date)   end_Date
  FROM  (
         SELECT  start_date,
                 end_date,
                 sum(start_of_group) over(order by start_date,end_date) grp
           FROM  (
                  select  start_date,
                          end_date, 
                          case
                            when start_date 
 > 1 + max(end_date) over(order by start_date,end_date rows between unbounded preceding and 1 preceding) then 1
                          end start_of_group
                    FROM  t
                 )
        )              
  GROUP BY grp
  ORDER BY 1
/

START_DAT END_DATE
--------- ---------
01-JAN-03 03-MAY-06
01-JAN-07 03-JUN-09

SQL> 


SY.

[Updated on: Thu, 16 August 2012 06:19] by Moderator

Report message to a moderator

Re: Combining overlapping dates [message #563651 is a reply to message #563606] Thu, 16 August 2012 06:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
Registered: January 2010
Senior Member
And MODEL solution:

WITH t AS (
  SELECT TO_DATE('1/1/2003','mm/dd/yyyy') start_date,TO_DATE('1/4/2005','mm/dd/yyyy') end_date FROM dual UNION ALL
  SELECT TO_DATE('1/1/2004','mm/dd/yyyy') start_date,TO_DATE('5/3/2006','mm/dd/yyyy') end_date FROM dual UNION ALL
  SELECT TO_DATE('1/1/2007','mm/dd/yyyy') start_date,TO_DATE('1/4/2008','mm/dd/yyyy') end_date FROM dual UNION ALL
  SELECT TO_DATE('1/1/2008','mm/dd/yyyy') start_date,TO_DATE('6/3/2009','mm/dd/yyyy') end_date FROM dual
          )
SELECT  start_date,
        end_date
  FROM  t
  MODEL
  RETURN UPDATED ROWS
  DIMENSION BY(
               ROW_NUMBER() OVER(ORDER BY start_date) rn
              )
  MEASURES(
           start_date,
           end_date,
           1 i
          )
  RULES ITERATE(1e9) UNTIL(start_date[iteration_number + 2] IS NULL)
    (
     end_date[i[1]] = CASE
                        WHEN start_date[iteration_number + 2] <= end_date[i[1]] + 1
                          THEN GREATEST(end_date[i[1]],end_date[iteration_number + 2])
                          ELSE end_date[i[1]]
                      END,
     i[1]           = CASE
                        WHEN start_date[iteration_number + 2] > end_date[i[1]] + 1
                          THEN iteration_number + 2
                          ELSE i[1]
                      END
    )
  ORDER BY end_date
/

START_DAT END_DATE
--------- ---------
01-JAN-03 03-MAY-06
01-JAN-07 03-JUN-09

SQL> 


SY.

[Updated on: Thu, 16 August 2012 06:19] by Moderator

Report message to a moderator

Re: Combining overlapping dates [message #563670 is a reply to message #563624] Thu, 16 August 2012 07:57 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
The format is MM/DD.
Re: Combining overlapping dates [message #563672 is a reply to message #563651] Thu, 16 August 2012 08:03 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
Solomon, thanks a lot. Your first solution seems much simpler than the second. Is there any advantage to use the second one? Wouldn't looping through records be slower than what doing what you wrote in the first solution?
Re: Combining overlapping dates [message #563675 is a reply to message #563672] Thu, 16 August 2012 08:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
Registered: January 2010
Senior Member
No, there is no advantage to use the second one. First is faster. It was just "art for art's sake".

SY.
Re: Combining overlapping dates [message #563677 is a reply to message #563672] Thu, 16 August 2012 08:19 Go to previous message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Wouldn't looping through records be slower than what doing what you wrote in the first solution?


Sure.

Regards
Michel
Previous Topic: In oracle Httpuritype - Not getting the output correctly
Next Topic: function for checking if it is a four digit number
Goto Forum:
  


Current Time: Sat Dec 20 01:39:11 CST 2014

Total time taken to generate the page: 0.07819 seconds