Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

PL/SQL

599010Sep 20 2007 — edited Jun 20 2008
Hi,
I have a problem in finding duplicates in a table which has three fields : EVENTID, START_DATE, END_DATE. I want to find out another record which entirely or partially covers the period shown in START_DATE and END_DATE fields.

Comments

438877

Like this ?

SQL> with t as (
  2  select 1 id, trunc(sysdate)-3 start_date, trunc(sysdate-1) end_date from dual
  3  union all
  4  select 2, trunc(sysdate)-4, trunc(sysdate) from dual
  5  union all
  6  select 3, trunc(sysdate)-4, trunc(sysdate)-3 from dual
  7  union all
  8  select 4, trunc(sysdate)-2, trunc(sysdate)-1 from dual
  9  union all
 10  select 5, trunc(sysdate)-2, trunc(sysdate) from dual
 11  union all
 12  select 6, trunc(sysdate), trunc(sysdate)+1 from dual
 13  )
 14  select * from t
 15  /
 
        ID START_DAT END_DATE
---------- --------- ---------
         1 17-SEP-07 19-SEP-07
         2 16-SEP-07 20-SEP-07
         3 16-SEP-07 17-SEP-07
         4 18-SEP-07 19-SEP-07
         5 18-SEP-07 20-SEP-07
         6 20-SEP-07 21-SEP-07
 
6 rows selected.
 
SQL> with t as (
  2  select 1 id, trunc(sysdate)-3 start_date, trunc(sysdate-1) end_date from dual
  3  union all
  4  select 2, trunc(sysdate)-4, trunc(sysdate) from dual
  5  union all
  6  select 3, trunc(sysdate)-4, trunc(sysdate)-3 from dual
  7  union all
  8  select 4, trunc(sysdate)-2, trunc(sysdate)-1 from dual
  9  union all
 10  select 5, trunc(sysdate)-2, trunc(sysdate) from dual
 11  union all
 12  select 6, trunc(sysdate), trunc(sysdate)+1 from dual
 13  )
 14  select t1.* from t, t t1
 15  where t.id = 1 and (t.start_date, t.end_date) overlaps (t1.start_date, t1.end_date)
 16  and t1.id <> t.id
 17  /
 
        ID START_DAT END_DATE
---------- --------- ---------
         2 16-SEP-07 20-SEP-07
         4 18-SEP-07 19-SEP-07
         5 18-SEP-07 20-SEP-07

Rgds.

Rob van Wijk

And if you don't want to use the undocumented OVERLAPS function, you can do it like this:

SQL> create table mytable
  2  as
  3  select 1 eventid, date '2007-01-01' start_date, date '2007-12-31' end_date from dual union all
  4  select 2, date '2007-07-01', date '2008-07-01' from dual union all
  5  select 3, date '2006-07-01', date '2007-06-01' from dual union all
  6  select 4, date '2006-07-01', date '2008-07-01' from dual union all
  7  select 5, date '2006-01-01', date '2006-12-31' from dual union all
  8  select 6, date '2008-01-01', date '2008-12-31' from dual union all
  9  select 7, date '2007-04-01', date '2007-10-01' from dual
 10  /

Tabel is aangemaakt.

SQL> var P_EVENTID number
SQL> exec :P_EVENTID := 1

PL/SQL-procedure is geslaagd.

SQL> with input as
  2  ( select eventid
  3         , start_date
  4         , end_date
  5      from mytable
  6     where eventid = :P_EVENTID
  7  )
  8  select t.eventid
  9       , t.start_date
 10       , t.end_date
 11    from mytable t
 12       , input
 13   where t.eventid != input.eventid
 14     and input.start_date < t.end_date
 15     and input.end_date > t.start_date
 16  /

   EVENTID START_DATE          END_DATE
---------- ------------------- -------------------
         2 01-07-2007 00:00:00 01-07-2008 00:00:00
         3 01-07-2006 00:00:00 01-06-2007 00:00:00
         4 01-07-2006 00:00:00 01-07-2008 00:00:00
         7 01-04-2007 00:00:00 01-10-2007 00:00:00

4 rijen zijn geselecteerd.

Regards,
Rob.

Aketi Jyuuzou

Sometimes,
Result of OVERLAPS and result of simultaneous inequality are not equal.

Although OVERLAPS of Oracle is unofficial.
I hope that I know high specification of OVERLAPS of Oracle.

for instance on 10.2.0.1.0

SQL> select d1,d2,d3,d4,"OVERLAPS1","OVERLAPS2"
  2  from (select d1,d2,d3,d4,
  3        case when (d1,d2) OVERLAPS (d3,d4) then 1 else 0 end as "OVERLAPS1",
  4        case when d3 < d2 and d1 < d4 then 1 else 0 end as "OVERLAPS2"
  5        from (select Least(d1,d2) as d1,
  6              greatest(d1,d2) as d2,
  7              Least(d3,d4) as d3,
  8              greatest(d3,d4) as d4
  9              from(select
 10                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D1,
 11                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D2,
 12                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D3,
 13                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D4
 14                   from all_catalog,all_catalog where RowNum <=90000)))
 15  where "OVERLAPS1" != "OVERLAPS2"
 16    and RowNum <=5;

D1        D2        D3        D4        OVERLAPS1  OVERLAPS2
--------  --------  --------  --------  ---------  ---------
07-06-16  07-07-05  07-06-16  07-06-16          1          0
07-06-28  07-12-02  07-06-28  07-06-28          1          0

SQL> /

D1        D2        D3        D4        OVERLAPS1  OVERLAPS2
--------  --------  --------  --------  ---------  ---------
07-10-11  07-11-19  07-10-11  07-10-11          1          0
07-08-15  07-08-25  07-08-15  07-08-15          1          0
07-06-13  07-10-25  07-06-13  07-06-13          1          0
07-08-09  07-12-03  07-08-09  07-08-09          1          0
07-07-10  07-11-26  07-07-10  07-07-10          1          0
Aketi Jyuuzou

umm

SQL> select
  2  count(nullif("OVERLAPS1","OVERLAPS2")) as OL2,
  3  count(nullif("OVERLAPS1","OVERLAPS3")) as OL3,
  4  count(nullif("OVERLAPS1","OVERLAPS4")) as OL4,
  5  count(nullif("OVERLAPS1","OVERLAPS5")) as OL5
  6  from (select d1,d2,d3,d4,
  7        case when (d1,d2) OVERLAPS (d3,d4) then 1 else 0 end as "OVERLAPS1",
  8        case when d3 < d2 and d1 < d4 then 1 else 0 end as "OVERLAPS2",
  9        case when d3 < d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS3",
 10        case when d3 <= d2 and d1 < d4 then 1 else 0 end as "OVERLAPS4",
 11        case when d3 <= d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS5"
 12        from (select Least(d1,d2) as d1,
 13              greatest(d1,d2) as d2,
 14              Least(d3,d4) as d3,
 15              greatest(d3,d4) as d4
 16              from(select
 17                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D1,
 18                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D2,
 19                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D3,
 20                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D4
 21                   from all_catalog,all_catalog where RowNum <=90000)));

 OL2   OL3   OL4   OL5
----  ----  ----  ----
   1   293   307   599

SQL> /

 OL2   OL3   OL4   OL5
----  ----  ----  ----
   2   282   303   583

SQL> /

 OL2   OL3   OL4   OL5
----  ----  ----  ----
   2   305   291   594
Aketi Jyuuzou

I researched.

SQL> select count(*),
  2  count(nullif("OVERLAPS1","OVERLAPS2")) as OL2,
  3  count(nullif("OVERLAPS1","OVERLAPS3")) as OL3,
  4  count(nullif("OVERLAPS1","OVERLAPS4")) as OL4,
  5  count(nullif("OVERLAPS1","OVERLAPS5")) as OL5,
  6  count(nullif("OVERLAPS1","OVERLAPS6")) as OL6
  7  from (select d1,d2,d3,d4,
  8        case when (d1,d2) OVERLAPS (d3,d4) then 1 else 0 end as "OVERLAPS1",
  9        case when d3 < d2 and d1 < d4 then 1 else 0 end as "OVERLAPS2",
 10        case when d3 < d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS3",
 11        case when d3 <= d2 and d1 < d4 then 1 else 0 end as "OVERLAPS4",
 12        case when d3 <= d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS5",
 13        case when d3 < d2 and d1 < d4
 14               or d1=all(d3,d4) then 1 else 0 end as "OVERLAPS6"
 15        from (select Least(d1,d2) as d1,
 16              greatest(d1,d2) as d2,
 17              Least(d3,d4) as d3,
 18              greatest(d3,d4) as d4
 19              from(select
 20                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D1,
 21                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D2,
 22                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D3,
 23                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D4
 24                   from all_catalog,all_catalog where RowNum <=90000)));

 COUNT(*)        OL2        OL3        OL4        OL5        OL6
---------  ---------  ---------  ---------  ---------  ---------
    90000          2        291        317        606          0

SQL> /

 COUNT(*)        OL2        OL3        OL4        OL5        OL6
---------  ---------  ---------  ---------  ---------  ---------
    90000          3        320        299        616          0

SQL> /

 COUNT(*)        OL2        OL3        OL4        OL5        OL6
---------  ---------  ---------  ---------  ---------  ---------
    90000          4        302        300        598          0

SQL> /

 COUNT(*)        OL2        OL3        OL4        OL5        OL6
---------  ---------  ---------  ---------  ---------  ---------
    90000          2        308        317        623          0

Therefore I conclude that "(d1,d2) OVERLAPS (d3,d4)" means "d3 < d2 and d1 < d4 or d1=all(d3,d4)"

Aketi Jyuuzou
653549

OVERLAPS is not documented.
However on oracle11g,
WM_OVERLAPS is documented.
Mohan K Narayanan
Hi friends,
I am using a database ORACLE 10G. I wrote a query using with function but it can't ran and it shows error message is

SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed


Query is

1 with t as
2 (select 1 id, trunc(sysdate)-3 start_date, trunc(sysdate-1) end_date from dual
3 union all
4 select 2 ,trunc(sysdate)-4 ,trunc(sysdate) from dual
5 union all
6 select 3 , trunc(sysdate)-4,trunc(sysdate)-3 from dual
7 union all
8 select 4 ,trunc(sysdate)-2,trunc(sysdate)-1 from dual
9 )
10* select * from t
11 /
jgarry
Worked for me, 10.2.0.4:
  1  with t as
  2  (select 1 id, trunc(sysdate)-3 start_date, trunc(sysdate-1) end_date from dual
  3  union all
  4  select 2 ,trunc(sysdate)-4 ,trunc(sysdate) from dual
  5  union all
  6  select 3 , trunc(sysdate)-4,trunc(sysdate)-3 from dual
  7  union all
  8  select 4 ,trunc(sysdate)-2,trunc(sysdate)-1 from dual
  9  )
 10* select * from t
SQL> /

        ID START_DAT END_DATE
---------- --------- ---------
         1 17-JUN-08 19-JUN-08
         2 16-JUN-08 20-JUN-08
         3 16-JUN-08 17-JUN-08
         4 18-JUN-08 19-JUN-08
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 18 2008
Added on Sep 20 2007
8 comments
6,705 views