CODEWITHSUNDEEP
mysqlsqldate
user4495602
user4495602

Reputation:

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.

Upvotes: 0

Views: 109

Answers (4)

Vahe Shadunts
Vahe Shadunts

Reputation: 1966

This is correct, because of join you have theoretically you have 2 different AUDIs in the result set. I can suggest to use BETWEEN instead of NOT BETWEEN, get the ordered cars, and exclude them from cars table using statement NOT IN with subquery, example below.

SELECT destination, COUNT(destination) AS 'available cars' 
FROM cars 
WHERE cars.id NOT IN (
    SELECT DISTINCT car_id FROM orders WHERE 
    date_to BETWEEN '2016-05-03' AND '2016-05-05' 
    AND date_from BETWEEN '2016-05-03' AND '2016-05-05'
)

NOTE: This query not tested, we can do it if you provide SQLFiddle or something like that.

P.S. There are more optimal ways to do the same, with NOT EXISTS statement or you can use JOIN with subquery.

Upvotes: 0

Tin Tran
Tin Tran

Reputation: 6202

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

Upvotes: 0

Atoadaso
Atoadaso

Reputation: 133

I think it would be easier to find which cars are not available and then exclude them from the list. Something like this:

SELECT destination, COUNT(destination) AS 'available cars'
FROM cars
LEFT JOIN orders ON cars.id = orders.car_id 
        AND (date_to BETWEEN '2016-05-03' AND '2016-05-05' 
        OR date_from BETWEEN '2016-05-03' AND '2016-05-05')

WHERE orders.car_id IS NULL
    AND destination = 'Kristiansand' GROUP BY destination

Upvotes: 0

Michael Arrison
Michael Arrison

Reputation: 1553

Change your thinking from exclusionary:

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

to inclusionary:

  (date_from < '2016-05-03' AND date_to < '2016-05-05') OR
  (date_from > '2016-05-03' AND date_to > '2016-05-05')

Upvotes: 0

Related Questions