CODEWITHSUNDEEP
sql
Robert Munteanu
Robert Munteanu

Reputation: 68268

Finding overlapping intervals using SQL

I want to extract rows from a table whose interval intersects with an interval specified in the query. Assuming that I have a simple ID, DATE_START, DATE_END table and two query parameters P_DATE_START and P_DATE_END, what is the simplest way of expressing the query so that I find all rows for which [DATE_START, DATE_END] has at least one common element with [P_DATE_START, P_DATE_END]?

Update:

To make the desired outcome clearer, please find a list of input values and expected outcomes below. Colums are DATE_START, DATE_END, P_DATE_START, P_DATE_END, MATCH.

16, 17, 15, 18, YES
15, 18, 16, 17, YES
15, 17, 16, 18, YES
16, 18, 15, 17, YES
16, 17, 18, 19, NO
18, 19, 16, 17, NO

Upvotes: 4

Views: 7204

Answers (3)

KobbyPemson
KobbyPemson

Reputation: 2539

SELECT id, date_start, date_end FROM thetable WHERE not (date_end < p_date_start OR p_date_end < date_start )

Upvotes: 2

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95582

Depending on your dbms, you might be able to use the OVERLAPS operator.

select * from your_table
where (date '2011-01-15', date '2011-01-18') overlaps (date_start, date_end)

Upvotes: 3

escargot agile
escargot agile

Reputation: 22379

Even simpler:

SELECT id, date_start, date_end 
FROM thetable 
WHERE date_start <= p_date_end 
AND date_end >= p_date_start

Upvotes: 8

Related Questions