Home » SQL & PL/SQL » SQL & PL/SQL » Comparing Overlapping Dates (Oracle 10g)
Comparing Overlapping Dates [message #428337] Wed, 28 October 2009 00:19 Go to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
Hi ,
Below is my table data having 4 columns ruleid,ruleno, startdate,enddate

data is :-

RuleId RuleNo StartDate EndDate
1 1 10-Jan-09 15-Jan-09
2 1 16-Jan-09 19-Jan-09
3 1 20-Jan-09 23-Jan-09
4 1 24-Jan-09 26-Jan-09
5 1 30-Jan-09 10-Feb-09
6 1 11-Feb-09 16-Feb-09
7 2 10-Oct-09 15-Oct-09
8 2 16-Oct-09 18-Oct-09
9 2 19-Oct-09 10-Nov-09
10 2 11-Nov-09 10-Dec-09


I want to compare the overlapping dates for ex.
RuleNo :-- 1
StartDate :-- 09-Jan-09
EndDate :-- 16-Jan-09

i want the check whether the new value are overlapping with the existing value or not. If the value overlap with any of the value for the respective RuleNo. Then it should give me those ruleid.
I tried using Overlaps function but it did'nt work for multiple values.

Please help me out for the same.

Thanks.
San

[Updated on: Wed, 28 October 2009 00:26]

Report message to a moderator

Re: Comparing Overlapping Dates [message #428346 is a reply to message #428337] Wed, 28 October 2009 00:46 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Please give us more detail.

Give us an example of expect results for some given dataset.

Kevin
Re: Comparing Overlapping Dates [message #428348 is a reply to message #428346] Wed, 28 October 2009 00:52 Go to previous messageGo to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
CREATE TABLE DATECHECK
(RULEID NUMBER,RULENO NUMBER,STARTDATE DATE,ENDDATE DATE);

INSERT INTO DATECHECK VALUES ( 1, 1, TO_DATE('01/10/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('01/15/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM') )
/
INSERT INTO DATECHECK VALUES ( 2, 1, TO_DATE('01/16/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('01/19/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM') )
/
INSERT INTO DATECHECK VALUES ( 3, 1, TO_DATE('01/20/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('01/23/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM') )
/
INSERT INTO DATECHECK VALUES ( 4, 1, TO_DATE('01/24/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('01/26/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM') )
/
INSERT INTO DATECHECK VALUES ( 5, 1, TO_DATE('01/30/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/10/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM') )
/
INSERT INTO DATECHECK VALUES ( 6, 1, TO_DATE('02/11/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('02/16/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM') )
/
INSERT INTO DATECHECK VALUES ( 7, 2, TO_DATE('10/10/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('10/15/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM') )
/
INSERT INTO DATECHECK VALUES ( 8, 2, TO_DATE('10/16/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('10/18/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM') )
/
INSERT INTO DATECHECK VALUES ( 9, 2, TO_DATE('10/19/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('11/10/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM') )
/
INSERT INTO DATECHECK VALUES ( 10, 2, TO_DATE('11/11/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), TO_DATE('12/10/2009 12:00:00 AM', 'MM/DD/YYYY HH12:MI:SS AM') )
/

Input will be
RuleNo :-- 1
StartDate :-- 09-Jan-09
EndDate :-- 16-Jan-09

Expected Resulr
RuleId
1
2

Do let me know if you need more details on this.
Thanks
San.
Re: Comparing Overlapping Dates [message #428350 is a reply to message #428337] Wed, 28 October 2009 00:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Seems to me a simple question of > or < etc.

Try this:

Undocumented OVERLAPS Function, Don't Use it Yet

Good luck, Kevin
Re: Comparing Overlapping Dates [message #428352 is a reply to message #428350] Wed, 28 October 2009 01:32 Go to previous messageGo to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
Not working,

It will look out for a specific range.

Thanks
San.
Re: Comparing Overlapping Dates [message #428355 is a reply to message #428352] Wed, 28 October 2009 01:50 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SELECT ruleid
  FROM datecheck
 WHERE startdate BETWEEN TO_DATE ('09-Jan-09', 'dd-mon-yy')
                     AND TO_DATE ('16-Jan-09', 'dd-mon-yy')
Re: Comparing Overlapping Dates [message #428360 is a reply to message #428355] Wed, 28 October 2009 01:55 Go to previous messageGo to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
Thanks Ayush,
It works.
Re: Comparing Overlapping Dates [message #428361 is a reply to message #428348] Wed, 28 October 2009 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like (just a quick idea):
SQL> select * from  DATECHECK order by 1;
    RULEID     RULENO STARTDATE   ENDDATE
---------- ---------- ----------- -----------
         1          1 10-JAN-2009 15-JAN-2009
         2          1 16-JAN-2009 19-JAN-2009
         3          1 20-JAN-2009 23-JAN-2009
         4          1 24-JAN-2009 26-JAN-2009
         5          1 30-JAN-2009 10-FEB-2009
         6          1 11-FEB-2009 16-FEB-2009
         7          2 10-OCT-2009 15-OCT-2009
         8          2 16-OCT-2009 18-OCT-2009
         9          2 19-OCT-2009 10-NOV-2009
        10          2 11-NOV-2009 10-DEC-2009

10 rows selected.

SQL> def ruleno=1
SQL> def startdate='09-Jan-2009'
SQL> def enddate='16-Jan-2009'

SQL> select ruleid from DATECHECK
  2  where ruleno = &ruleno
  3    and ( to_date('&startdate','DD-Mon-YYYY') between startdate and enddate
  4        or to_date('&enddate','DD-Mon-YYYY') between startdate and enddate 
  5        or ( to_date('&startdate','DD-Mon-YYYY') < startdate 
  6           and to_date('&enddate','DD-Mon-YYYY') > enddate
  7           )
  8        )
  9  order by 1
 10  /
    RULEID
----------
         1
         2

2 rows selected.

Regards
Michel
Re: Comparing Overlapping Dates [message #428363 is a reply to message #428361] Wed, 28 October 2009 02:07 Go to previous messageGo to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
Thanks Michel:-

Your query have solved my purpose.

Just a simple query is there any built in function in oracle 10g which do this ?

Thanks
San.
Re: Comparing Overlapping Dates [message #428371 is a reply to message #428363] Wed, 28 October 2009 02:46 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Noone I am aware of.

Regards
Michel
Previous Topic: Read write access
Next Topic: Error after running control file in toad
Goto Forum:
  


Current Time: Sun Dec 04 02:55:29 CST 2016

Total time taken to generate the page: 0.13517 seconds