This forum is currently locked. You can't register or post questions at this time. (read more)

Question about detecting overlapping date values in a table

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

User avatar
dariyoosh
Member
Posts: 26
Joined: Tue Aug 11, 2009 11:18 am

Question about detecting overlapping date values in a table

Postby dariyoosh » Thu May 29, 2014 1:26 pm

Dear Tim,

I would like to ask a question about finding overlapping date values in a table. Actually I've written a query but I'm not sure that the predicate covers all possibilities in terms of overlaps.

Environment:

Code: Select all

            OS:  LinuxFedora Corre 17 X86_64
Oracle version:  12.1.0.1.0 Enterprise Edition - 64bit


Test case:

Code: Select all

alter session set nls_date_format = 'yyyy-mm-dd';
--
drop table tmptab;
create table tmptab
(
    idval       number  ,
    startDate   date    ,
    endDate     date
);
alter table tmptab add constraint tmptab_pk primary key(idval);
--
--
insert all
    into tmptab(idval, startDate, endDate)
        values (1, date '2014-01-01', date '2014-01-06')
    into tmptab(idval, startDate, endDate)
        values (2, date '2014-01-03', date '2014-01-05')
    into tmptab(idval, startDate, endDate)
        values (3, date '2014-02-01', date '2014-02-10')
    into tmptab(idval, startDate, endDate)
        values (4, date '2014-02-14', date '2014-02-17')
    into tmptab(idval, startDate, endDate)
        values (5, date '2014-05-01', date '2014-07-04')
    into tmptab(idval, startDate, endDate)
        values (6, date '2014-01-02', date '2014-01-20')
    into tmptab(idval, startDate, endDate)
        values (7, date '2013-12-28', date '2014-01-03')
    into tmptab(idval, startDate, endDate)
        values (8, date '2014-10-01', date '2014-10-02')
    into tmptab(idval, startDate, endDate)
        values (9, date '2014-11-01', date '2014-11-04')
    into tmptab(idval, startDate, endDate)
        values (10, date '2005-08-01', date '2006-01-07')
select
    *
from
    "PUBLIC".dual;

Code: Select all

select
    t1.*
from
    tmptab t1;


    IDVAL       STARTDATE      ENDDATE
------------- -------------- --------------
         1     2014-01-01     2014-01-06
         2     2014-01-03     2014-01-05
         3     2014-02-01     2014-02-10
         4     2014-02-14     2014-02-17
         5     2014-05-01     2014-07-04
         6     2014-01-02     2014-01-20
         7     2013-12-28     2014-01-03
         8     2014-10-01     2014-10-02
         9     2014-11-01     2014-11-04
        10     2005-08-01     2006-01-07

10 rows selected.



Now as you can see above, there are several date values that overlap. Suppose that we want to write a query that shows the idval of the tasks whose date ranges overlap. Here is what I have done:

Code: Select all

select
    t1.idval,
    t1.startDate,
    t1.endDate,
    t2.idval,
    t2.startDate,
    t2.endDate
from
    tmptab t1
cross join
    tmptab t2
where
        (t1.idval < t2.idval) 
    and
        (
                (t2.startDate between t1.startDate AND t1.endDate)
            or
                (t2.endDate between t1.startDate AND t1.endDate)
        )
order by
    t1.idval asc;



      IDVAL STARTDATE  ENDDATE         IDVAL STARTDATE  ENDDATE
---------- ---------- ---------- ---------- ---------- ----------
         1 2014-01-01 2014-01-06          2 2014-01-03 2014-01-05
         1 2014-01-01 2014-01-06          7 2013-12-28 2014-01-03
         1 2014-01-01 2014-01-06          6 2014-01-02 2014-01-20
         2 2014-01-03 2014-01-05          7 2013-12-28 2014-01-03
         6 2014-01-02 2014-01-20          7 2013-12-28 2014-01-03


Table dropped.

SQL>


Is my logic and the predicate in my query correct?

I consider that there could be only 2 possible cases where two tasks can overlap

1st case:

Code: Select all

Task1 (startDate, endDate) = (S1, E1)
Task2 (startDate, endDate) = (S2, E2)

Task2 is included in the whole period of execution of task1, therefore
starts after Task1 but ends before Task1

------------------------
|        Task1         |
|                      |
|     ------------     |
|     |  Task2   |     |
v     v          v     v
------------------------
S1    S2         E2   E1


2nd case:

Code: Select all

Task1 (startDate, endDate) = (S1, E1)
Task2 (startDate, endDate) = (S2, E2)

Task2 starts after Task1 but ends later than Task1

------------------------
|        Task1         |
|                      |
|     -----------------|----------
|     |    Task2       |         |
v     v                v         v
----------------------------------
S1    S2              E1         E2


Is my logic/query correct? is it a better way to write it?

Thanks in advance,
Dariyoosh

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Question about detecting overlapping date values in a ta

Postby Tim... » Thu May 29, 2014 2:30 pm

Hi.

There is actually a very simple way to do this.

http://www.oracle-base.com/articles/mis ... ranges.php

When you look at it, it looks like it is wrong, but the relationship between the start and end of the range means you automatically cover some of the possibilities. I came across this solution a few years ago and was convinced it wouldn't work, but it does... :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

User avatar
dariyoosh
Member
Posts: 26
Joined: Tue Aug 11, 2009 11:18 am

Re: Question about detecting overlapping date values in a ta

Postby dariyoosh » Thu May 29, 2014 6:30 pm

Thanks for your help.

I read your article and I did several tests with your predicate that is,

Code: Select all

WHERE  EXISTS (SELECT 1
               FROM   dates_test b
               WHERE  b.start_date <= a.end_date
               AND    b.end_date   >= a.start_date
               AND    b.id         <  a.id);

As you said, at the first look it seems to be wrong but I run the query with three possible cases
- Task2 is inside Task1
- Task2 starts after Task1 but ends later
- Tasks do not overlap
And I saw that indeed for each case your predicate provides the correct answer. Strange ! I admit that it works but I'm not capable of understanding why it works ! :D

Tim wrote:but the relationship between the start and end of the range means you automatically cover some of the possibilities.

Why do you say some of possibilities? It seems to me that your solution covers all the three possible scenarios, unless I misunderstood what you wrote.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Question about detecting overlapping date values in a ta

Postby Tim... » Fri May 30, 2014 7:54 am

Hi.

Should have said, "some of the possibilities that don't look like they are covered"... :)

Since we know that start time must be before end time and end time must be after start time, there is an implication of certain overlaps that the code doesn't explicitly check for. The person who came up with this solution had a mathematical explanation that I don't understand. All I know is it works. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests