Overlapping Records

Question

I have a table called PRODUCTS and each PRODUCT_NO_REGISTRATION_NO is allowed to be used only once that is product start date and return date should not clashing.

We return the products and return date is entered in ACTUAL_RETURN_DATE and if ACTUAL_RETURN_DATE is null we take END_DATE as return date.

You could see the records here

For example PRODUCT_NO_REGISTRATION_NO is clashing, as HP_2014 is returned on 18-Jun-2001, however HP_2012 same PRODUCT_NO_REGISTRATION_NO is allotted on 18-Jun-2001.

How can I find out using sql whether records are overlapping?

Update 1

There was a modification in table PRODUCTS to include PRODUCT_EXTENSION_NO. Combination of PRODUCT_NO,PRODUCT_NO_REGISTRATION_NO and PRODUCT_EXTENSION_NO becomes a unique row (composite primary key).

Rules are as below Each PRODUCT_NO_REGISTRATION_NO is allowed to be used only once that is product start date and return date should not clashing.

We return the products and return date is entered in ACTUAL_RETURN_DATE and if ACTUAL_RETURN_DATE is null we take END_DATE as return date.

PRODUCT_NO has extensions, so the END_DATE is extended.

Sql Fiddle

E.g. if you see the records PRODUCT_NO - ORP76 with PRODUCT_EXTENSION_NO - 1 is clashing with PRODUCT_NO- ORP100 with PRODUCT_EXTENSION_NO - 0.

How can I find out using sql whether records are overlapping, extension of PRODUCT_NO is allowed though. i.e. PRODUCT_NO ORP76 with extension 0 and extension 1 are basically extended.

Answer 1

Assuming that product_no is unique, you might self-join products table and check for overlapping dates:

select *
  from PRODUCTS
 inner join products products_test
    on products.PRODUCT_NO_REGISTRATION_NO 
     = products_test.PRODUCT_NO_REGISTRATION_NO
   and products.start_date 
    <= nvl (products_test.ACTUAL_RETURN_DATE, products_test.end_date)
   and nvl (products.ACTUAL_RETURN_DATE, products.end_date) 
    >= products_test.start_date
   and products.product_no 
    <> products_test.product_no

And here is Sql Fiddle.

EDIT: a version using rowid:

select *
  from PRODUCTS
 inner join products products_test
    on products.PRODUCT_NO_REGISTRATION_NO 
     = products_test.PRODUCT_NO_REGISTRATION_NO
   and products.start_date 
    <= nvl (products_test.ACTUAL_RETURN_DATE, products_test.end_date)
   and nvl (products.ACTUAL_RETURN_DATE, products.end_date) 
    >= products_test.start_date
   and products.rowid
    <> products_test.rowid

Second Sql Fiddle

UPDATE after clarification: the idea is to get minimum and maximum range of product start and end date counting in extended records, and then compare two streams for range overlap removing self-references by testing product_no and product_no_registration_no.

with extended as
(
  select PRODUCT_NO, 
         PRODUCT_NO_REGISTRATION_NO, 
         min (START_DATE) as start_date,
         max (nvl (ACTUAL_RETURN_DATE, END_DATE)) as end_date
    from products
   group by PRODUCT_NO, PRODUCT_NO_REGISTRATION_NO
)
select e1.PRODUCT_NO_REGISTRATION_NO,
       e1.PRODUCT_NO,
       e1.start_date,
       e1.end_date,
       e2.PRODUCT_NO "PRODUCT_NO - CLASH",
       e2.start_date "START_DATE - CLASH",
       e2.end_date "END_DATE - CLASH"
  from extended e1
 inner join extended e2
    on e1.PRODUCT_NO_REGISTRATION_NO
     = e2.PRODUCT_NO_REGISTRATION_NO
   and e1.start_date <= e2.end_date
   and e1.end_date >= e2.start_date
-- Remove self-references
   and not
   (
           e1.PRODUCT_NO = e2.PRODUCT_NO
       and e1.PRODUCT_NO_REGISTRATION_NO 
         = e2.PRODUCT_NO_REGISTRATION_NO
   )

Third Sql Fiddle.