获取重叠日期的房价

admin

获取重叠日期的房价

sql

我有这种结构的表：

id  accom_id    room_type_id    date_from   date_to        price_per_room   
3   1           2               2017-09-01  2017-09-10     70.00    
5   1           2               2017-09-11  2017-09-20     100.00

可以说我想从2017-09-07到2017-09-15停留。因此，对于DATEDIFF，我需要计算价格为70天，价格为100天。最后，我想显示总数。

谁能帮我建立这个查询？我希望它清楚地问什么！

阅读 198

2021-06-07

共1个答案

admin

假设没有定义重叠范围，并且假定所有给定范围都是包含性的，我们可以使用CTE然后通过简单的聚合来获取数据：

declare @t table (date_from date,date_to date, price_per_room int)
insert into @t (date_from,date_to,price_per_room) values
('20170901','20170910',70.00 ),
('20170911','20170920',100.00)
declare @Start date
declare @End date
select @Start = '20170907',@End = '20170915'

;With IncludedPeriods as (
    select
        CASE WHEN @Start > date_from THEN @Start ELSE date_from END as fromDT,
        CASE WHEN @End < date_to THEN @End ELSE date_to END as ToDT,
        price_per_room
    from
        @t
    where
        date_from <= @End and
        @Start <= date_to
)
select
    SUM(price_per_room * (1 + DATEDIFF(day,fromDT,ToDT)))
from
    IncludedPeriods

请注意，DATEDIFF由于要计算转换，因此我们要在结果中添加一个，但是我假设从 ‘20170911’ 到
‘20170911’的这段时间应类似地算作一天和更长的时间。

不像其中一些试图枚举各种“箱子”为重叠其他的答案的，这里采用的简单的规则-两个周期重叠，如果第二端部之前，首先开始和如果所述第一端部之前的第二开始-
这是逻辑在所施加的whereCTE中的子句。为了确定重叠的程度，我们使用两个开始日期中的较晚日期和两个结束日期中的较早日期-
这就是CASE表达式的作用。如果我们有按日期运行的标量MIN和MAX函数，则我希望使用它们，但SQL Server内置没有此类函数。

2021-06-07