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

Answer 2

Unfortunately, you can't look for missing rows (you can for rows that don't exist, but it's not the same).

You had a good start, querying the calendar table and left joining to the reservations, but you made a mistake by putting the filtering condition in the WHERE clause where it will return no rows instead of in the join ON clause.

SELECT calendar.dt,
   COUNT(DISTINCT reservations.id) AS reserved,
   COUNT(DISTINCT items.id) AS inventory
FROM calendar
LEFT JOIN reservations
   ON calendar.dt >= reservations.start_date 
   AND calendar.dt <= reservations.end_date
   AND reservations.sku = 'ABC123'
LEFT JOIN items ON items.sku=reservations.sku
GROUP BY calendar.dt
HAVING inventory > reserved OR reserved = 0
ORDER BY calendar.dt ASC 
LIMIT 1

This query looks for the earliest date in the calendar table where the existing number of items in the items table is more than the number of reservations for those dates (ie, first date where you have stock available).

(EDIT: Substitute 'id' with the primary column in those tables)

Answer 3

I learned a really valuable lesson from a SQL expert a long time ago.

Don't let what you have blind you to what you need. Think about where reservations are used in the real world.

• Hotels
• Airlines
• Cruise ships
• Arenas
• Football stadiums

Every one of these has a fixed inventory of seats on a particular day. Each of those seats might be either open or reserved--tied to a specific customer.

You have a fixed inventory of items on a particular day. Each of those items might be either available or reserved--tied to a specific customer.

I think your life would be a lot easier if you created a table of availability rather than a table of reservations.

create table availability (
  sku varchar(15) not null,
  sn varchar(15) not null,
  av_date date not null,
  customer_id integer, --references customers (customer_id)
  primary key (sku, sn, av_date),
  foreign key (sku, sn) references items (sku, sn)
);

insert into availability values
('1', '1', '2011-01-01', 1),  -- reserved by customer_id 1
('1', '1', '2011-01-02', 1),
('1', '1', '2011-01-03', 1),
('1', '1', '2011-01-04', NULL), -- not yet reserved
('1', '1', '2011-01-05', NULL),
('1', '1', '2011-01-06', NULL),
('1', '1', '2011-01-07', NULL),
('1', '1', '2011-01-08', NULL),
('1', '1', '2011-01-09', NULL),
('1', '1', '2011-01-10', NULL),
('1', '2', '2011-01-01', NULL),
('1', '2', '2011-01-02', 2),
('1', '2', '2011-01-03', 2),
('1', '2', '2011-01-04', 3),
('1', '2', '2011-01-05', 3),
('1', '2', '2011-01-06', NULL),
('1', '2', '2011-01-07', NULL),
('1', '2', '2011-01-08', NULL),
('1', '2', '2011-01-09', NULL),
('1', '2', '2011-01-10', NULL);

(For production, I'd build a stored procedure to populate the availability table.)

Now it's just dead simple to select the earliest available date for a given sku.

select sku, sn, av_date, customer_id 
from availability 
where sku = '1' and customer_id is null
order by av_date asc
limit 1

Answer 4

The request start and end dates are the periods of time over which someone wants to reserve the given item. (E.g., "I want to reserve the widget sometime between July and September")

Revised to handle existing stock on-hand. Although, it isn't clear how that is actually stored. Do you have an on-hand value for each SKU or is each item given its own row?

Select Min( C.dt ) As EarliestAvailableDate
From Calendar As C
    Left Join Reservations As R
        On R.StartDate <= C.dt
            And R.EndDate >= C.dt
            And R.Sku = 'ABC123'
Where C.dt Between '[Request Start Date]' And '[Request End Date]'
    And R.record_number Is Null
    And Exists  (
                Select 1
                From Items As I1
                    Left Join Reservations As R1
                        On R1.StartDate <= C.dt
                            And R1.EndDate >= C.dt
                            And R1.Sku = I1.SKU
                Where I.SKU = 'ABC123'
                Having Count(*) > Count(R1.record_number)
                )

If your items table actually stores an on-hand value for the number of a given SKU in stock, then yo can simply change the query like so:

Select Min( C.dt ) As EarliestAvailableDate
From Calendar As C
    Left Join Reservations As R
        On R.StartDate <= C.dt
            And R.EndDate >= C.dt
            And R.Sku = 'ABC123'
Where C.dt Between '[Request Start Date]' And '[Request End Date]'
    And R.record_number Is Null
    And Exists  (
                Select 1
                From Items As I1
                    Left Join Reservations As R1
                        On R1.StartDate <= C.dt
                            And R1.EndDate >= C.dt
                            And R1.Sku = I1.SKU
                Where I.SKU = 'ABC123'
                Having I1.OnHandCount > Count(R1.record_number)
                )