CODEWITHSUNDEEP
sqloracle-database
Lukasz
Lukasz

Reputation: 127

Find records with overlapping date range in SQL

I have following table and data:

CREATE TABLE customer_wer(
  id_customer NUMBER,
  name VARCHAR2(10),
  surname VARCHAR2(20),
  date_from DATE,
  date_to DATE NOT NULL,
  CONSTRAINT customer_wer_pk PRIMARY KEY (id_customer, data_from));

INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-JAN-00', '31-MAR-00');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-APR-00', '30-JUN-00');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '15-JUN-00', '30-SEP-00');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-OCT-00', '31-DEC-00');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-JAN-01', '31-MAR-01');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-APR-01', '30-JUN-01');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-JUL-01', '5-OCT-01');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-OCT-01', '31-DEC-01');

I need a SELECT query to find the records with overlapping dates. It means that in the example above, I should have four records in result

number 
2
3
7
8

Thank you in advance. I am using Oracle DB.

Upvotes: 3

Views: 19567

Answers (5)

Sergey Afinogenov
Sergey Afinogenov

Reputation: 2212

In my situation with much data in a table better performance was reached in a such way:

select *
from (
select id_customer,
       name,
       surname,
       date_from,
       date_to,
       lead(id_customer) over (partition by id_customer order by date_from) id_customer1,
       lead(name)        over (partition by id_customer order by date_from) name1,
       lead(surname)     over (partition by id_customer order by date_from) surname1,
       lead(date_from)   over (partition by id_customer order by date_from) date_from1,
       lead(date_to)     over (partition by id_customer order by date_from) date_to1
 from customer_wer)
where date_from1 <= date_to

Upvotes: 0

Artistan
Artistan

Reputation: 2037

Similar to the answer by @Ben ... but check for overlapping == days as well with a check on id to ensure it is unique.

select * from seasons t1
join seasons t2 on (t1.season_open  >= t2.season_open  and t1.season_open  <= t2.season_close and t1.id != t2.id)
                or (t1.season_close >= t2.season_open  and t1.season_close <= t2.season_close and t1.id != t2.id)
                or (t1.season_close >= t2.season_close and t1.season_open  <= t2.season_open  and t1.id != t2.id)

Upvotes: 0

Guest
Guest

Reputation: 21

If each beginning date is less than or equal to the other record's ending date, then it will be within the range.

If starting date 1 is less than ending date 2 AND starting date 2 is less than ending date 1.

 SELECT * 
 FROM t t1
    JOIN t t2 ON (t1.datefrom <= t2.dateto)
              AND (t2.datefrom <= t1.dateto)

Upvotes: 2

Ben
Ben

Reputation: 71

the earlier answer does not account for situations where t2 is entirely within t1

select * from t t1
join t t2 on (t1.datefrom > t2.datefrom and t1.datefrom < t2.dateto  )
          or (t1.dateto   > t2.datefrom and t1.dateto   < t2.dateto  )
          or (t1.dateto   > t2.dateto   and t1.datefrom < t2.datefrom)

Upvotes: 7

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Try this:

select * from t t1
join t t2 on (t1.datefrom > t2.datefrom and t1.datefrom < t2.dateto)
          or (t1.dateto > t2.datefrom and t1.dateto < t2.dateto)

Thank You for this example. After modification it is working:

SELECT *
FROM customer_wer k
JOIN customer_wer w
ON k.id_customer = w.id_customer
WHERE (k.date_from > w.date_to AND k.date_from < w.date_to)
OR (k.date_to > w.date_from AND k.date_to < w.date_to);

Upvotes: 0

Related Questions