CODEWITHSUNDEEP
mysqlsql
jerrygarciuh
jerrygarciuh

Reputation: 21988

Detecting date overlaps without going day by day

I am working on a project that involves managing information about what driver had a car on a particular day. Ostensibly the assignments should always have an end_date at least one day prior to the next start_date.

Table is like

+----+--------+-----------+------------+----------+
| id | car_id | driver_id | start_date | end_date |
+----+--------+-----------+------------+----------+

There is a lot of human input from folks who are not really invested in this process, editing of old rows, and overlaps occur.

Now I can easily imagine running many queries using GROUP BY car_id for a given date and seeing if you have more than one row for a car on date x.

What I would love to sort out is a single query that indicates all row ids which have an overlapping dates for any one car. Can anyone point me in the right direction?

Upvotes: 1

Views: 43

Answers (1)

crennie
crennie

Reputation: 674

This query will return pairs of rows that overlap:

select r1.id, r2.id from rentals r1 join rentals r2
on r1.car_id = r2.car_id and r1.id != r2.id
where (r1.start_date < r2.end_date or r2.end_date is null)
and r1.end_date > r2.start_date;

This just compares each date range to every other date range for the same car_id.

More info on overlapping dates.

Upvotes: 1

Related Questions