Query required cars for a date range

Question

I have two tables in database. 'car', 'reservation'

table 'car' has following columns

id -> primary key

name -> name of the car like SUV, STANDARD, ECONOMY

count -> Number of cars available

table 'reservation' has following columns

id -> Reservation id auto increment

date_out -> Reservation start date, ex: '2019-01-01 00:00:00'

date_in -> Reservation end date, ex: '2019-01-05 23:59:59'

car_name -> One of the car name from table 'car'

I want to query the database to show availability/shortage of cars of each type for a date range.

So output should be similar to this table.

----date----- car_name availability/shortage

2019-01-01 ----SUV---- 1

2019-01-01 ECONOMY -1

negative sign indicates shortage

positive sign indicated availability

What I have done

This query works only for a single date and car name specified.

Set @forDate = '2019-02-14';
Set @car = 'STANDARD';


Set @total_reservation = (select count(*) as total_reservation from (
select date_out, date_in, car_type from reservation 
where date_in > @forDate and date_out < @forDate
and car_type = @car
)AS t1);

select @total_reservation;

Set @car_count = (select count from car where name = @car);

select @car_count;

select @car_count - @total_reservation;

I am having problem in converting the query for a date range input by user.

Why don't you use `BETWEEN` to get data of specific date range. — Muhammad Waheed, Feb 13 '19 at 07:59
Still struggling? See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 13 '19 at 08:14

Salman A · Answer 1 · 2019-02-14T07:07:12.793

You can use a table of dates. In the following example I will simply type the dates:

SELECT datelist.dt, car.name, car.count - COUNT(reservation.id) AS available
FROM (
    SELECT '2019-02-14' AS dt UNION ALL -- check availability from date
    SELECT '2019-02-15' UNION ALL
    SELECT '2019-02-16' UNION ALL
    SELECT '2019-02-17' UNION ALL
    SELECT '2019-02-18' -- check availability to date
) AS datelist
CROSS JOIN car
LEFT JOIN reservation ON datelist.dt BETWEEN reservation.date_out AND reservation.date_in
      AND car.name = reservation.car_name
GROUP BY datelist.dt, car.name, car.count

In MySQL 8 you can use a recursive cte to build the dates:

WITH RECURSIVE datelist AS (
    SELECT '2019-02-14' + INTERVAL 0 DAY AS dt
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM datelist WHERE dt < '2019-02-18'
)
SELECT datelist.dt, car.name, car.count - COUNT(reservation.id) AS available
FROM datelist
CROSS JOIN car
LEFT JOIN reservation ON datelist.dt BETWEEN reservation.date_out AND reservation.date_in
      AND car.name = reservation.car_name
GROUP BY datelist.dt, car.name, car.count

Query required cars for a date range

1 Answers1