1

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?

jerrygarciuh
  • 21,158
  • 26
  • 82
  • 139

1 Answers1

1

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.

Community
  • 1
  • 1
crennie
  • 674
  • 7
  • 18
  • 1
    Thank you! There is a small edit to the third line to account for rows where end date has not yet been set: WHERE (r1.start_date < r2.end_date OR r2.end_date = '0000-00-00') – jerrygarciuh Feb 14 '14 at 20:34
  • 1
    Good catch! I didn't think about in-progress rentals. Updated the answer – crennie Mar 03 '14 at 18:51