CODEWITHSUNDEEP
phpmysqlsqldate
user1898807
user1898807

Reputation: 21

How to find available cars between date ranges

Thare two tables:
cars 

id | name          | count_cars  
1  | Volvo S60     | 3  
2  | Tesla model 3 | 2

reservations 

id | car_id | date_from        | date_to  
1  | 1      | 2017-05-02 10:00 | 2017-05-05 10:00  
2  | 1      | 2017-05-01 10:00 | 2017-05-03 10:00  
3  | 2      | 2017-05-03 10:00 | 2017-05-04 10:00

The first table is cars list, where count is number of cars this model. The second table is list of reservations of cars.

Problem:
The user enters two dates, and the system must provide a list of the cars that will be available between these dates.
I want, having dates that the user enters and the dates of booking cars from the second table, determine how many and which cars are booked for these dates.
There are situations when the car is partially reserved between dates, in such cases the car also needs to be considered booked.

How through one sql request to receive the list and quantity of the available cars?
car_id|count_available

Upvotes: 0

Views: 360

Answers (2)

Sebastian Lerch
Sebastian Lerch

Reputation: 424

What about a 

SELECT r.car_id, count(r.car_id)
FROM cars c, reservations r
WHERE r.date_from <= :user_input_from 
AND r.date_to >= :user_input_to
AND r.car_id = c.id group by r.car_id;

EDIT: As suggested below changed to <= and >=.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This is a typical overlaps query. You can use not exists:

select c.*
from cars c
where not exists (select 1
                  from reservations r
                  where r.carid = c.id and
                        r.startdate <= $enddate and
                        r.enddate >= $startdate
                 );

A overlap between two time frames occurs when one starts before the second ends and the first ends after the second starts.

Note that the <= and >= might really be < and/or >, depending on how you count the ends of the periods.

Upvotes: 1

Related Questions