mysql - Help with SQL query to find next available date for a reservation system

Question

I am attempting to write an SQL query for a reservations system to find the earliest available rental date for an item.

Items are categorized in the database by SKU. There can be multiple copies of an item, each uniquely identified in the database by its serial number.

When searching for the earliest available rental date for an item, it doesn't matter which serial number is chosen; simply the next one available.

The database has 2 tables; "Reservations" and "Items". There is also a Calendar table of several thousand YYYY-MM-DD future dates to work with.

The Reservations table contains columns; "record_number","sku", "serial_number", "start_date", "end_date" plus customer data. This is where each reservation is recorded as it is made.

The Items table contains columns; "sku" and "serial_number". This is an inventory of all rental items in the system.

I've worked the problem for over 2 days, but my SQL knowledge isn't enough to solve this puzzle.

I've progressed as far as generating a list of dates that have at least one reservation for a particular SKU:

   SELECT calendar.dt
     FROM calendar
LEFT JOIN reservations ON calendar.dt >= reservations.start_date 
                      AND calendar.dt <= reservations.end_date
    WHERE reservations.sku = 'ABC123'

I can sub-query the above into a "NOT IN ..." select statement but that only accomplishes finding dates having NO reservations for a particular SKU. I need to find the first date where at least one item is available.

I have imagined joining the dates of the Calendar table with the SKUs from the Items table with the reservation numbers of the Reservation table looking for "NULL" in the reservation_record, indicating no reservation exists for that date and serial-number combination. But I have been unable to write such a query that works.

Questions are welcome.

Answer 1

The following should get you going. you may want to adjust my sample of "Current_Date()" function for whatever may be your reservation start date and going out so many days....

This uses MySQL inline variables in the query. The inner query is a simple prepare of a reservation (@r) variable based on some starting date ( current_date() ), and joins to the item table. By doing no join clause, it would otherwise grab one date for every item. In my scenario, I'm only considering going out 30 days, so I've applied a limit of the first 30 items. No basis other than give me enough records so I don't have to create a temp table of 30 records (or however many days you want to go out). This creates an aliased query "JustDates" and has a single column "OpenDate". This is the basis of date ranges to test for.

This is now joined to the items table, but no condition creates a Cartesian to say for each date, compare with every item... per the WHERE clause, I am only concerned with items having SKU of "ABC123" weather they have 10 serial #s or 100. This would now give me a possible 300 or 3000 (10 serial items @ 30 days, or 100 serial items @ 30 days.

Now that I have a "range" of all individual serial numbers and possible days to check availability, I can now query against the reservations system. So, via a sub-select, and NOT IN for a given matching SKU, SERIAL #, and the POSSIBLE Date being found in reservations, I only want to keep those where the given OpenDate is NOT found. I've simulated your table structures and put in a handful of items, multiple serial numbers and staggared reservation date ranges and it works great...

Obviously, I would ensure indexes on sku / serial for performance. The only additional change I might make is when querying against the reservations, to exclude any reservations where the end date is prior to the starting date in question for YOUR query, and optionally, no Start Date > the LAST date you are considering. If you have a ton of reservations spanning years, who cares about something ancient, or something way in the future from the date range in question.

select  items.sku,
        items.serial_number,
        JustDates.OpenDate
    from 
        ( SELECT 
                 @r:= date_add(@r, interval 1 day ) OpenDate
            FROM 
                 (select @r := current_date()) vars,
                items limit 30 ) JustDates,
        items
    where 
            sku = "ABC123"
        and sku not in ( select sku from Reservations
                            where items.sku = reservations.sku
                              and items.serial_number = reservations.serial_number
                              and justDates.OpenDate >= reservations.start_date
                              and justDates.OpenDate <= reservations.end_date )
    order by 
       items.serial_number,
       justDates.OpenDate