Overlapping Data

Question

I have a sql query to check overlapping of product records in table PRODUCTS. In most cases query works fine except for the following.

select * from products where 
product_reg_no = 'AL-NAPT' 
and (to_date('14-Aug-2001') BETWEEN to_date('27-Aug-2001') AND to_date('30-Aug-2001')
or to_date('31-Aug-2001') BETWEEN to_date('27-Aug-2001') AND to_date('30-Aug-2001'))

How to make this query to catch all records are overlapping either partially or completely?

If required I can provide table structure with sample records.

Thanks

Update 1

I have added table structure and records here or as below:

create table products
(product_reg_no varchar2(32),
 start_date date,
 end_date date);


Insert into products
   (product_reg_no, START_DATE, END_DATE)
 Values
   ('AL-NAPT', TO_DATE('08/14/2012', 'MM/DD/YYYY'), TO_DATE('08/31/2012', 'MM/DD/YYYY'));
Insert into products
   (product_reg_no, START_DATE, END_DATE)
 Values
   ('AL-NAPT', TO_DATE('08/27/2012', 'MM/DD/YYYY'), TO_DATE('08/30/2012', 'MM/DD/YYYY'));
COMMIT;

The first record which is from August, 14 2012 to August, 31 2012 is overlapping with second record which is from August, 27 2012 to August, 30 2012. So how can I modify my query to get the overlapping?

Is one of the date values (or several of them) supposed to come from a column in the database? It might be as well to provide a minimal schema for the products table and a few sample rows from it. I was going to suggest the OVERLAP operator, but Google suggests that it is not documented by Oracle (though it exists), so maybe you can't (shouldn't) use it. You might want to look at [Determine whether two date ranges overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/328558#328558). — Jonathan Leffler, Oct 01 '12 at 05:29
@JonathanLeffler Updated my question with [sqlfiddle](http://sqlfiddle.com/#!4/03d61/1) Thanks — Jacob, Oct 01 '12 at 06:04

hol · Answer 1 · 2012-10-01T06:54:08.910

This is a strange query. You check if 14-Aug-2001 is between 27-Aug-2001 and 30-Aug-2001 which is always false OR 31-Aug-2001 is between 27-Aug-2001 and 30-Aug-2001 which also always is false. So your where clause will always be false.

Edit: Thanks for clarification

SQL Fiddle Demo

select   p1.product_reg_no
       , p1.start_date p1s
       , p1.end_date   p1e
       , p2.start_date p2s
       , p2.end_date   p2e
from products p1, products p2
where p1.product_reg_no = p2.product_reg_no
  and not (    p1.end_date   < p2.start_date
           and p1.start_date > p2.end_date   );

What you want is the following scenarios (1 stands for the first row 2 for the second)

That you could also be turned around and say you do not want this:

I assumed you also do want this

The WHERE clause could also be written differently

not (    p1.end_date   < p2.start_date and p1.start_date > p2.end_date   )

is the same as

        p1.end_date   >= p2.start_date or p1.start_date <= p2.end_date

I think it was called De Morgan's law when I had that in school eons ago.

You must probably think about what would happen if you have more than 2 rows.

score 2 · Accepted Answer · edited May 23 '17 at 12:12

See Determine whether two date ranges overlap.

You need to evaluate the following, or a minor variant on it using <= instead of <, perhaps:

Start1 < End2 AND Start2 < End1

Since you're working with a single table, you need to have a self-join:

SELECT p1.*, p2.*
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no != p2.product_reg_no
   AND p1.start < p2.end
   AND p2.start < p1.end;

The not equal condition ensures that you don't get a record paired with itself (though the < conditions also ensure that, but if you used <=, the not equal condition would be a good idea.

This will generate two rows for each pair of products (one row with ProductA as p1 and ProductB as p2, the other with ProductB as p1 and ProductA as p2). To prevent that happening, change the != into either < or >.

And, looking more closely at the sample data, it might be that you're really interesting in rows where the registration numbers match and the dates overlap. In which case, you can ignore my wittering about != and < or > and replace the condition with = after all.

SELECT p1.*, p2.*
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no = p2.product_reg_no
   AND p1.start < p2.end
   AND p2.start < p1.end;

SQL Fiddle (unsaved) shows that this works:

SELECT p1.product_reg_no p1_reg, p1.start_date p1_start, p1.end_date p1_end,
       p2.product_reg_no p2_reg, p2.start_date p2_start, p2.end_date p2_end
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no = p2.product_reg_no
   AND p1.start_date < p2.end_date
   AND p2.start_date < p1.end_date
 WHERE (p1.start_date != p2.start_date OR p1.end_date != p2.end_date);

The WHERE clause eliminates the rows that are joined to themselves. With the duplicate column names in the SELECT-list eliminated, you get to see all the data. I added a row:

INSERT INTO products (product_reg_no, start_date, end_date)
VALUES ('AL-NAPT', TO_DATE('08/27/2011', 'MM/DD/YYYY'), TO_DATE('08/30/2011', 'MM/DD/YYYY'));

This was not selected — demonstrating that it does reject non-overlapping entries.

If you want to eliminate the double rows, then you have to add another fancy criterion:

SELECT p1.product_reg_no p1_reg, p1.start_date p1_start, p1.end_date p1_end,
       p2.product_reg_no p2_reg, p2.start_date p2_start, p2.end_date p2_end
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no = p2.product_reg_no
   AND p1.start_date < p2.end_date
   AND p2.start_date < p1.end_date
 WHERE (p1.start_date != p2.start_date OR p1.end_date != p2.end_date)
   AND (p1.start_date < p2.start_date OR
       (p1.start_date = p2.start_date AND p1.end_date < p2.end_date));

See my addendum; for your sample data, replace the `!=` with `=`. — Jonathan Leffler, Oct 01 '12 at 06:21
Your query works perfect when I tested with real data. I have a question though, how can I modify this to receive parameters, so that I could use this query in a function to check any overlapping of records when user enters dates in application before storing in table. Thanks and appreciated. — Jacob, Oct 02 '12 at 06:42
There are a variety of options. In some ways, the easiest would be to have a second table (possibly a temporary table), call it 'prospective_registration`, with the same three columns as `products`. Insert the new values into that table. Change the query so that `JOIN products p2` becomes `JOIN prospective_registration p2`, and leave everything else unchanged. Failing that, you'll need to replace each occurrence of `p2.product_reg_no`, `p2.start_date` and `p2.end_date` with the corresponding parameter, and remove the line `JOIN products p2`. There might be some other simplifications available. — Jonathan Leffler, Oct 02 '12 at 07:12
I have adopted your second approach and modified query as `SELECT p1.product_reg_no p1_reg, p1.start_date p1_start, p1.end_date p1_end FROM products p1 where p1.start_date <= :end_date AND :start_date <= p1.end_date and (p1.start_date != :start_date OR p1.end_date != :end_date) AND (p1.start_date <= :start_date OR (p1.start_date = :start_date AND p1.end_date < :end_date))`. I am checking with some real data whether this is fine. Correct me if anything wrong with the modified sql. Thanks a lot. — Jacob, Oct 02 '12 at 07:22
That looks about right — you simplified the select-list. It should be a systematic edit (one advantage of using `alias.column` notation consistently in SQL in the first place). — Jonathan Leffler, Oct 02 '12 at 07:27
Thanks a lot and much appreciated. I will come back to you if I find any issues. — Jacob, Oct 02 '12 at 07:30
I have found one issue with my modified sql as it is not picking the overlapped record. I have put a example here [SQL Fiddle Demo](http://sqlfiddle.com/#!4/87d26/3) — Jacob, Oct 02 '12 at 07:40

Overlapping Data

2 Answers2

Linked