robert robert - 7 months ago 14
SQL Question

Find available dates

I have two tables (1. orders and 2. cars):

Cars

Cars

Orders

Orders

I'm trying to find all cars that are available at a given date. In this case I want to find all available cars between 2016-05-03 and 2016-05-05. I check for cars that are

NOT BETWEEN
said date or cars that have not been registered in an order yet (
orders.car_id IS NULL
). Here is the query:

SELECT destination, COUNT(destination) AS 'available cars'
FROM cars
LEFT JOIN orders ON cars.id = orders.car_id

WHERE (orders.car_id IS NULL

OR (

date_to NOT BETWEEN '2016-05-03' AND '2016-05-05'
AND date_from NOT BETWEEN '2016-05-03' AND '2016-05-05'

)

)

AND destination = 'Kristiansand' GROUP BY destination


The problem is with the Audi A1 with id = 8. As you can see, it is registered on two appointments, one from
2016-05-03
to
2016-05-05
and one from
2016-04-29
to
2016-04-30
.

Since the second pair of dates at the end of April are
NOT BETWEEN
the given dates in the query, the A1 is an available car which is far from true.


I'm trying to fetch all cars available for rental outside of the given
dates in Kristiansand.

Answer

Let's say you have 2 periods T1 and T2 to check to see if they overlap you do this check (T1.start <= T2.end) AND (T1.end >= T2.start).

so try this below query, (it checks and makes sure that there doesn't exist an order of the same car that overlap the specified period

SET @startdate = '2016-05-03',@enddate = '2016-05-05';

SELECT c.destination,COUNT(c.destination) as available_cars
FROM cars c
WHERE NOT EXISTS (SELECT 1 
                  FROM orders o
                  WHERE o.car_id = c.id
                  AND o.date_from <= @enddate
                  AND o.date_to >= @startdate)
AND c.destination = 'Kristiansand'
GROUP BY c.destination

http://sqlfiddle.com/#!9/9340e3/4

You can remove the SET statement and hardcode in your @enddate and @startdate