SQL查询帮助以查找预订系统的下一个可用日期 [英] Help with SQL query to find next available date for a reservation system

问题描述

我正在尝试为预订系统编写SQL查询，以查找某项物品的最早可用租赁日期.

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

项目通过SKU在数据库中分类.一个项目可以有多个副本，每个副本在数据库中均通过序列号唯一标识.

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.

数据库有2个表； 预订"和项目".还有一个日历表，其中包含数千个YYYY-MM-DD将来的日期.

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.

预定"表中包含列； "record_number"，"sku"，"serial_number"，"start_date"，"end_date"以及客户数据.在此记录每个预订的位置.

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.

Items表包含列； "sku"和"serial_number".这是系统中所有租赁项目的清单.

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

我已经解决问题2天了，但是我的SQL知识不足以解决这个难题.

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

到目前为止，我已经生成了一个日期列表，该日期列表至少具有一个特定SKU的保留:

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'

我可以将上面的子查询为"NOT IN ..."选择语句，但这只能完成查找没有特定SKU保留日期的日期.我需要找到至少有一个可用项的第一个日期.

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.

我曾想过将Calendar表的日期与Items表中的SKU结合在一起，其中Reservation表的保留号在Reservation_record中寻找"NULL"，指示该日期和序列号组合不存在任何保留.但是我一直无法编写这样的查询.

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.

欢迎提问.

推荐答案

以下内容将助您一臂之力.您可能想要调整我的"Current_Date()"函数示例，以适应您的预订开始日期和出库时间如此之长....

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....

这在查询中使用MySQL内联变量.内部查询是基于某个开始日期(current_date())的保留(@r)变量的简单准备，并连接到项目表.通过不执行join子句，否则它将为每个项目获取一个日期.在我的情况下，我只考虑过30天，因此我对前30个项目应用了限制.除了给我足够的记录外，没有其他依据，因此我不必创建包含30条记录的临时表(或者您想出去的天数).这将创建一个别名查询"JustDates"，并具有一个列"OpenDate".这是要测试的日期范围的基础.

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.

现在已连接到项目表，但是没有条件会创建笛卡尔表示的每个日期，并与每个项目进行比较...根据WHERE子句，我只关心SKU为"ABC123"的项目，有10个序列号或100.现在，我可以得到300或3000(30天为10个序列项，或30天为100个序列项.

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.

现在，我已经拥有所有单个序列号的范围"以及可以检查可用天数的时间，现在我可以查询预订系统了.因此，通过子选择，对于给定的匹配SKU，SERIAL#和在预订中找到可能的日期，NOT IN来说，我只想保留那些未找到给定OpenDate的商品.我已经模拟了您的表格结构，并放入了一些物品，多个序列号和滞留的预订日期范围，而且效果很好...

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...

很显然，我会确保sku/serial上的索引性能.我唯一可能做的其他更改是在查询保留时，以排除结束日期早于您要查询的开始日期的任何保留，并且可以选择不包括开始日期>您正在考虑的最后日期.如果您有很多跨越多年的保留意见，那么谁会在乎有关日期范围的某些古老的东西或将来的某种方式.

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

这篇关于SQL查询帮助以查找预订系统的下一个可用日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！