Find big enough gaps in booking table

Question

A rental system uses a booking table to store all bookings and reservations:

booking | item | startdate        | enddate
1       | 42   | 2013-10-25 16:00 | 2013-10-27 12:00
2       | 42   | 2013-10-27 14:00 | 2013-10-28 18:00
3       | 42   | 2013-10-30 09:00 | 2013-11-01 09:00
…

Let’s say a user wants to rent item 42 from 2013-10-27 12:00 until 2013-10-28 12:00 which is a period of one day. The system will tell him, that the item is not available in the given time frame, since booking no. 2 collides.

Now I want to suggest the earliest rental date and time when the selected item is available again. Of course considering the user’s requested period (1 day) beginning with the user’s desired date and time.

So in the case above, I’m looking for an SQL query that returns 2013-10-28 18:00, since the earliest date since 2013-10-27 12:00 at which item 42 will be available for 1 day, is from 2013-10-28 18:00 until 2013-10-29 18:00.

So I need to to find a gap between bookings, that is big enough to hold the user’s reservation and that is as close a possible to the desired start date.

Or in other words: I need to find the first booking for a given item, after which there’s enough free time to place the user’s booking.

Is this possible in plain SQL without having to iterate over every booking and its successor?

Answer 1

If you can't redesign your database to use something more efficient, this will get the answer. You'll obviously want to parameterize it. It says find either the desired date, or the earliest end date where the hire interval doesn't overlap an existing booking:

Select
    min(startdate)
From (
    select
        cast('2013-10-27 12:00' as datetime) startdate
    from
        dual
    union all
    select
        enddate
    from
        booking
    where
        enddate > cast('2013-10-27 12:00' as datetime) and
        item = 42
    ) b1
Where
    not exists (
        select 
             'x'
        from
            booking b2
        where
            item = 42 and
            b1.startdate < b2.enddate and
            b2.startdate < date_add(b1.startdate, interval 24 hour)
    );

Example Fiddle