获取重叠日期的房价

Question

我在这个结构里有桌子：

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。最后，我想展示一下总数。

有人能帮我构建这个查询吗？我希望这是清楚的要求！

Answer 1

假设没有定义重叠范围，并且假定所有给定的范围都是包容性的，那么我们可以使用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’之间的句点应该类似地算作一天或更长的周期。

与其他一些试图枚举各种重叠“案例”的答案不同，这使用了简单规则--如果第一个句点在第二个结束之前开始，而第二个句点在第一个结束之前开始，则两个句点重叠--这是在CTE内的where子句中应用的逻辑。为了确定重叠的程度，我们采用两个开始日期中的后一个日期和两个结束日期中的更早日期--这就是CASE表达式正在做的事情。如果我们有按日期操作的标量MIN和MAX函数，我更愿意使用这些函数，但是没有内置到Server中的函数。

Answer 2

在Server中，您可以这样做：

select dateadd(d, number, '20170801')
from master.dbo.spt_values
where type='P' and (number <= datediff(d, '20170801', '20170810'))

计算总天数(在where子句中)，选择从0到总天数的数字(master.dbo.spt_values在Server中是一个有用的表)。否则，您可以创建一个从0到?的提升数字的表。然后你把这个数字加到起始日期。所以你有一整天的预订时间：

2017-08-01 
2017-08-02 
2017-08-03 
2017-08-04 
2017-08-05 
2017-08-06 
2017-08-07 
2017-08-08 
2017-08-09 
2017-08-10 

然后，您可以在date_from和date_to上加入给定的date_to，然后计算您的和。

编辑:就为了完成它..。:-)

SELECT SUM(price_per_room)
FROM master.dbo.spt_values
    LEFT OUTER JOIN room_prices AS rp ON (date_from <= DATEADD(d, number, my_start_date) AND DATEADD(d, number, my_start_date) <= date_to)
WHERE type='P' AND (number <= DATEDIFF(d, my_start_date, my_end_date))

Answer 3

您可以使用日历或日期表来处理这类事情。

在不执行创建表的实际步骤的情况下，您可以使用具有如下所示的公共表表达式的临时日期表：

declare @fromdate date = '20170907';
declare @thrudate date = '20170915';
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
select total_price = sum(price_per_room)
from dates d
  inner join t
    on d.date >= t.date_from
   and d.date <= t.date_to

rextester演示：http://rextester.com/MCF52261

返回：

+-------------+
| total_price |
+-------------+
|         780 |
+-------------+

要了解更详细的价格和日期，您可以将上面的select换成这个：

select 
    fromdate = min(date)
  , thrudate = max(date)
  , days = count(*)
  , price_per_room = avg(price_per_room)
  , total = sum(price_per_room)
from dates d
  inner join t
    on d.date >= t.date_from
   and d.date <= t.date_to
group by grouping sets ((price_per_room),())

rextester演示：http://rextester.com/NKZD1468

返回：

+------------+------------+------+----------------+-------+
|  fromdate  |  thrudate  | days | price_per_room | total |
+------------+------------+------+----------------+-------+
| 2017-09-07 | 2017-09-10 |    4 | 70             |   280 |
| 2017-09-11 | 2017-09-15 |    5 | 100            |   500 |
| 2017-09-07 | 2017-09-15 |    9 | 86             |   780 |
+------------+------------+------+----------------+-------+

​

编号和日历表参考：

​

• 生成一个没有循环的集合或序列-2-
• “数字”或“统计”表:它是什么以及它如何替换一个循环--杰夫·莫登
• 在Server 2008中创建日期表/维度- David Stein
• 日历表-为什么你需要一个-大卫斯坦因
• 在中创建日期维度或日历表