SQL consecutive occurrences for availability based query

Question

I am a bit stuck trying to create a pretty complex on SQL, and more specifically MySQL.

The database deals with car rentals, and the main table of what is a snowflake patters looks a bit like:

  id   | rent_start | rent_duration | rent_end     |  customerID |    carId
  -----------------------------------------------------------------------------------
  203  | 2016-10-03 |       5       |  2016-11-07  |    16545    |    4543
  125  | 2016-10-20 |       9       |  2016-10-28  |    54452    |    5465  
  405  | 2016-11-01 |       2       |  2016-01-02  |    43565    |    346

My goal is to create a query that allows given

1) A period range like, for example: from 2016-10-03 to 2016-11-03 2) A number of days, for example: 10

allows me to retrieve the cars that are actually available for at least 10 CONSECUTIVE days between the 10th of October and the 11th. A list of IDs for those cars is more than enough... I just don't really know how to setup a query like that.

If it can help: I do have a list of all the car IDs in another table.

Either way, thanks!

score 0 · Accepted Answer · answered Oct 16 '16 at 17:58

I think it is much simpler to work with availability, rather than rentals, for this purpose.

So:

select r.car_id, r.rent_end as avail_start,
       (select min(r2.rent_start
        from rentals r2
        where r2.car_id = r.car_id and r2.rent_start > r.rent_start
       ) as avail_end
from rentals r;

Then, for your query, you need at least 10 days. You can use a having clause or subquery for that purpose:

select r.*
from (select r.car_id, r.rent_end as avail_start,
             (select min(r2.rent_start
              from rentals r2
              where r2.car_id = r.car_id and r2.rent_start > r.rent_start
             ) as avail_end
      from rentals r
     ) r
where datediff(avail_end, avail_start) >= $days;

And finally, you need for that period to be during the dates you specify:

select r.*
from (select r.car_id, r.rent_end as avail_start,
             (select min(r2.rent_start
              from rentals r2
              where r2.car_id = r.car_id and r2.rent_start > r.rent_start
             ) as avail_end
      from rentals r
     ) r
where datediff(avail_end, avail_start) >= $days and
      ( (avail_end > $end and avail_start < $start) or
        (avail_start <= $start and avail_end >= $start + interval 10 day) or
        (avail_start > $start and avail_start + interval 10 day <= $end)
      );

This handles the various conditions where the free period covers the entire range or starts/ends during the range.

There are no doubt off-by-one errors in this logic (is a car available the same date it returns). The this should give you a solid approach for solving the problem.

By the way, you should also include cars that have never been rented. But that is not possible with the tables you describe in the question.

Works super well, and yes the problem you mentioned (room without bookings) is actually one. — DeadEnd, Oct 16 '16 at 21:09
Another one is that it counts the first available date as the end of the first rental. That means that if a car has been rented only once, or booked to be rented only once any previous date will be considered as not available. — DeadEnd, Oct 16 '16 at 21:10

SQL consecutive occurrences for availability based query

1 Answers1