CODEWITHSUNDEEP
mysqlsqldategroup-by
efex09
efex09

Reputation: 419

Query required cars for a date range

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.

Upvotes: 1

Views: 253

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272296

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

Upvotes: 1

Related Questions