I have a booking table for cars that can be hired. There are multiples cars and multiple bookings. I want to create a query to check for any overlaps (bookings where the same car has been hired and the dates clash) in bookings.
At the moment I have the following query:
SELECT bi.id, car_id, booking_id
from booking_bookingitem bi
SELECT count(*) FROM booking_bookingitem bt
date_start - interval '1 days', date_end + interval '1 days'
) OVERLAPS (now(), now() + INTERVAL '30 days')
AND bt.car_id = bi.car_id) > 1
ORDER BY car_id, booking_id
I have added 1 day offset either side of the start (date_start) and end (date_end) to allow for the car to be returned, cleaned, etc. The 30 day interval is to check bookings 30 days in to the future.
I think I'm close but I am getting some abnormalities when testing.
Can anyone spot what I've done wrong, or is there a better way to do this?
Thanks in advance!