SQL to query the maximum number of available items for a specific period (reservation system)

For a reservation system there is an inventory table and each item has a quantity (e.g. there are 20 chairs). Now the user can make a reservation for a specific period (e.g. 5 chairs for two hours “2010-11-23 15:00” – “2010-11-23 17:00”; another reservation could be for several days “2010-11-24 11:00” – “2010-11-26 14:00”).

What’s the best way to check, how many items are still available for the requested period?

  • Storing ampersand in database
  • Should I use a push or pull subscription if using RMO?
  • Get current year of the 12 month with month wise count data in SQL Server?
  • Include in .net Application Setup File .. SQL SERVER 2005 Express
  • SQL 2005 - Search stored procedures for text (Not all text is being searched)
  • DB cannot be opened because it is version 655. This server supports version 612
  • The user should enter the time he wants to make a reservation (from, until) and he should see how many inventory items are still available for this period.

    table "inventory"
    -------------------
    inventory_id (int) 
    quantity (int)
    
    table "reservation"
    -------------------
    reservation_id (int)
    inventory_id (int)
    quantity (int)
    from (datetime)
    until (datetime)
    

    The reservations can be overlapping, but for a point in time, only inventory.quantity items should be reserved.

    Simple Example:

    We have 40 chairs.

    The following reservations exist:

    R1 2010-11-23 14:00 - 2010-11-23 15:30 -> 5 chairs reserved
    R2 2010-11-23 15:00 - 2010-11-23 16:00 -> 10 chairs reserved
    R3 2010-11-23 17:00 - 2010-11-23 17:30 -> 20 chairs reserved
    

    A user makes several reservation requests (queries):

    Q1 2010-11-23 15:00 - 2010-11-23 17:00 -> 25 chairs are available
    Q2 2010-11-23 15:45 - 2010-11-23 17:00 -> 30 chairs are available
    Q3 2010-11-23 16:30 - 2010-11-23 18:00 -> 30 chairs are available
    Q4 2010-11-23 15:10 - 2010-11-23 15:20 -> 25 chairs are available
    Q5 2010-11-23 13:30 - 2010-11-23 17:30 -> 20 chairs are available
    

    How would I query the maximum available quantity for a requested period? Or is a different table design needed? The target database systems are Oracle and SQL-Server.

    Update:

    I tried to “visualize” the reservations R1 and R2 and the queries Q1 – Q5 without changing the original examples. I added Q4 and Q5 as additional examples. av shows the available count.

           R1  R2  R3  av
    13:30              40                  Q5
    14:00   5          35                  Q5
    14:30   5          35                  Q5
    15:00   5  10      25  Q1              Q5
    15:10   5  10      25  Q1          Q4  Q5
    15:20   5  10      25  Q1              Q5
    15:30      10      30  Q1              Q5
    15:45      10      30  Q1  Q2          Q5
    16:00              40  Q1  Q2          Q5
    16:30              40  Q1  Q2  Q3      Q5
    17:00          20  20          Q3      Q5
    av                     25  30  20  25  20
    

  • CREATE VIEW must be the only statement in the batch
  • Selecting overlapping time ranges
  • Retrieve number of columns in SQL Table - C#
  • How to import text file and save data into database?
  • Backup SQL Schema Only?
  • Add empty row to query results if no results found
  • 2 Solutions collect form web for “SQL to query the maximum number of available items for a specific period (reservation system)”

    You could try something like this (full working example)

    DECLARE @inventory TABLE(
        inventory_id int, 
        quantity int
    )
    
    DECLARE @reservation TABLE(
        reservation_id int,
        inventory_id int,
        quantity int,
        [from] datetime,
        until datetime
    )
    
    INSERT INTO @inventory SELECT 1, 40
    
    INSERT INTO @reservation SELECT 1, 1, 5, '2010-11-23 14:00 ', '2010-11-23 15:30'
    INSERT INTO @reservation SELECT 1, 1, 10, '2010-11-23 15:00 ', '2010-11-23 16:00'
    
    DECLARE @Start DATETIME,
            @End DATETIME
    
    SELECT  @Start = '2010-11-23 15:00',
            @End = '2010-11-23 17:00'
    
    SELECT  TotalUsed.inventory_id,
            i.quantity - ISNULL(TotalUsed.TotalUsed,0) Available
    FROM    @inventory i LEFT JOIN
            (
                SELECT  inventory_id,
                        SUM(quantity) TotalUsed
                FROM    @reservation
                WHERE   [from] BETWEEN @Start AND @End
                OR      until BETWEEN @Start AND @End
                GROUP BY inventory_id
            ) TotalUsed ON  TotalUsed.inventory_id = i.inventory_id
    
    
    SELECT  @Start = '2010-11-23 15:45',
            @End = '2010-11-23 17:00'
    
    SELECT  TotalUsed.inventory_id,
            i.quantity - ISNULL(TotalUsed.TotalUsed,0) Available
    FROM    @inventory i LEFT JOIN
            (
                SELECT  inventory_id,
                        SUM(quantity) TotalUsed
                FROM    @reservation
                WHERE   [from] BETWEEN @Start AND @End
                OR      until BETWEEN @Start AND @End
                GROUP BY inventory_id
            ) TotalUsed ON  TotalUsed.inventory_id = i.inventory_id
    

    Results

    inventory_id Available
    ------------ -----------
    1            25
    
    
    inventory_id Available
    ------------ -----------
    1            30
    

    Using SQLServer syntax:

    SELECT i.inventory_id,
           MAX(i.quantity) - COALESCE(SUM(r.quantity), 0) AS available            
    FROM INVENTORY i     
    LEFT JOIN RESERVATIONS r 
    ON (r.inventory_id = i.inventory_id AND
        r.[from] <= @End AND
        r.until >= @Start)           
    GROUP BY i.inventory_id
    

    I assume the supplied structures are a simplified version of the actual structures being used – if not, I recommend against using keywords such as FROM as column names.

    EDIT: new query, assuming bookings are only to the nearest minute and never more than one week long:

    with number_cte(n, n2) as 
     (select n, n+1 n2 from (select 0 n) m union all select n+1 n, n2+1 n2 
      from number_cte where n < datediff("mi",@start,@end))
    SELECT i.inventory_id, max(i.quantity) - COALESCE(max(a.alloc), 0) AS available 
    from INVENTORY as i  
    join
    (select n.datesel, r.inventory_id, sum(r.quantity) alloc from
     (select dateadd("mi",n,@Start) datesel from number_cte) as n  
     JOIN RESERVATIONS r 
     ON n.datesel between r.[from] AND r.until 
     GROUP BY n.datesel, r.inventory_id) a 
    on i.inventory_id = a.inventory_id
    GROUP BY i.inventory_id option (maxrecursion 10080)
    

    This would actually be easier in Oracle, as you could use a connect by level rather than a CTE – if you are going to have reservations longer than one week, you will need to increase the maxrecursion number accordingly.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.