计算重叠日期范围的价格

Question

计算重叠日期范围的价格

12

我有一个简单的名为HotelRate的表格

HID  |  START_DATE  |   END_DATE    |   PRICE_PER_DAY
--------------------------------------
1        01/1/2015       10/1/2015       100
1        11/1/2015       20/1/2015       75
1        21/1/2015       30/1/2015       110

如果用户查询从2015年5月1日到2015年1月25日的总价，最简单的计算酒店房间价格的方法是什么？

我已经查看了以下内容：

但是这些对我来说都不太有意义。

我尝试了几个查询，但感觉像瞎子打灯笼。有人能建议我一种简单而优雅的方式吗？

@JamesZ

运行第一个查询后，我得到：

start_date end_date   duration    price_per_day
---------- ---------- ----------- -------------
2015-01-01 2015-01-10 5           100
2015-01-11 2015-01-20 9           75
2015-01-21 2015-01-30 4           110

对于第一个范围，5是可以的，对于第二个范围，应该是10，对于第三个范围，应该是5

如何计算天数：开始日期和结束日期之间的夜晚总数，与天数差相同。

05-Jan-15   06-Jan-15   1 Night
06-Jan-15   07-Jan-15   1 Night
07-Jan-15   08-Jan-15   1 Night
08-Jan-15   09-Jan-15   1 Night
09-Jan-15   10-Jan-15   1 Night
10-Jan-15   11-Jan-15   1 Night
11-Jan-15   12-Jan-15   1 Night
12-Jan-15   13-Jan-15   1 Night
13-Jan-15   14-Jan-15   1 Night
14-Jan-15   15-Jan-15   1 Night
15-Jan-15   16-Jan-15   1 Night
16-Jan-15   17-Jan-15   1 Night
17-Jan-15   18-Jan-15   1 Night
18-Jan-15   19-Jan-15   1 Night
19-Jan-15   20-Jan-15   1 Night
20-Jan-15   21-Jan-15   1 Night
21-Jan-15   22-Jan-15   1 Night
22-Jan-15   23-Jan-15   1 Night
23-Jan-15   24-Jan-15   1 Night
24-Jan-15   25-Jan-15   1 Night
               Count : 20 Night

- Parimal Raj

1

这是MySQL还是MS SQL Server？ - Mureinik

@Mureinik Sql Server - Parimal Raj

5个回答

3

你需要一个日历表，但每个数据库都应该有一个。实际的实现是与用户和DBMS特定相关的（例如MS SQL Server），因此搜索“calendar table”+你的DBMS可能会揭示一些适用于你的系统的源代码。

select HID, sum(PRICE_PER_DAY)
from calendar_table as c
join HotelRate
  on calendar_date between START_DATE and END_DATE
group by HID

- dnoeth

1

如果您已经有一个日期表格，这个问题就很容易解决。还没有？下面有两个函数可以帮助您入手。这是如何使用它们的：

-- Arguments can be passed in any order
SELECT * FROM dbo.RangeDate('2015-12-31', '2015-01-01');
SELECT * FROM dbo.RangeSmallInt(10, 0);

SELECT A.HID, SUM(A.PRICE_PER_DAY)
FROM dbo.RangeDate('2000-01-01', '2020-12-31') Calendar
JOIN HotelRate A
    ON Calendar.D BETWEEN A.START_DATE and A.END_DATE
GROUP BY A.HID;

您可以使用RangeDate函数作为日历，也可以使用它来构建自己的日历功能/表格。

-- Generate a range of up to 65,536 contiguous DATES
CREATE FUNCTION dbo.RangeDate (   
    @date1 DATE = NULL
  , @date2 DATE = NULL
)   
RETURNS TABLE   
AS   
RETURN (
    SELECT D = DATEADD(DAY, A.N, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END)
    FROM dbo.RangeSmallInt(
        CASE WHEN @date1 IS NOT NULL AND @date2 IS NOT NULL THEN 0 END
      , ABS(DATEDIFF(DAY, @date1, @date2))
    ) A
);

-- Generate a range of up to 65,536 contiguous BIGINTS
CREATE FUNCTION dbo.RangeSmallInt (
    @n1 BIGINT = NULL
  , @n2 BIGINT = NULL
)
RETURNS TABLE
AS
RETURN (
    WITH Numbers AS (
        SELECT N FROM(VALUES
            (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256
        ) V (N)
    )    
    SELECT TOP (
               CASE
                   WHEN @n1 IS NOT NULL AND @n2 IS NOT NULL THEN ABS(@n2 - @n1) + 1
                   ELSE 0
               END
           )
           N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 + CASE WHEN @n1 <= @n2 THEN @n1 ELSE @n2 END
    FROM Numbers A, Numbers B
    WHERE ABS(@n2 - @n1) + 1 < 65537
);

- Kittoes0124

1

你可以使用这个方法来计算每个周期的价格，然后将它们加起来得到总费用。它使用一个case语句来确定每个周期有多少天，所以在你的例子中，这是5、9和4天:

Declare @startdate date = '2015-01-05',
        @todate date = '2015-01-25'

Select sum(price_per_period) as TotalPrice -- The cost for all periods is summed to give a total
from
-- First it works out the number of days in the period with a case statement and then
-- multiplies this by the daily rate to get the total for that period
(Select price_per_day * case when Start_date <= @startdate then DATEDIFF(day, @startdate,end_date) else  
    case when Start_date > @startdate and end_date < @todate then DATEDIFF(day, start_date,end_date) else 
        case when Start_date > @startdate and end_date >= @todate then  DATEDIFF(day, start_date, @todate) end
        end
    end price_per_period

 from pricetable
 where (Start_date between @Startdate and @todate) or 
      (end_date between @Startdate and @todate)
) a

这消除了单独创建日历表的需要。

SQL Fiddle: http://www.sqlfiddle.com/#!6/25e63/4/0

- Christian Barron

1

这应该足够快，因为你首先生成日历，然后仅使用连接。此外，可以使用分组集来实现每个酒店的总价格：

数据定义：

create table HotelRate(HID int, START_DATE date, END_DATE date, PRICE_PER_DAY int);

insert into HotelRate values
(1, '20150101', '20150110', 100),
(1, '20150111', '20150120', 75),
(1, '20150121', '20150130', 110),
(2, '20150101', '20150110', 10),
(2, '20150111', '20150120', 5),
(2, '20150121', '20150130', 50)

查询：

declare @sd date = '20150105' , @ed date = '20150125'

;with c as(select @sd d union all select dateadd(dd, 1, d) from c where d < @ed)
select h.HID, h.START_DATE, h.END_DATE, sum(PRICE_PER_DAY) PRICE
from c join HotelRate h on c.d >= h.START_DATE and c.d < h.END_DATE
group by grouping sets((h.HID, h.START_DATE, h.END_DATE),(h.HID))

输出：

HID START_DATE  END_DATE    PRICE
1   2015-01-01  2015-01-10  500
1   2015-01-11  2015-01-20  675
1   2015-01-21  2015-01-30  550
1   (null)      (null)      1725
2   2015-01-01  2015-01-10  50
2   2015-01-11  2015-01-20  45
2   2015-01-21  2015-01-30  250
2   (null)      (null)      345

这可以通过使用记数表进行进一步优化。如果在数据库中创建日历表，甚至可以立即实现优化。

这里有一个fiddle http://sqlfiddle.com/#!3/25e7bc/1 假设您已经创建了一些日历表Calendar(d date)，其中包含从1900-01-01开始到2100-01-01结束的日期。在日期列上为Calendar和HotelRange表添加索引。然后上述查询可重写为:

select h.HID, h.START_DATE, h.END_DATE, sum(PRICE_PER_DAY) PRICE
from Calendar c join HotelRate h on c.d >= h.START_DATE and c.d < h.END_DATE
where c.d between @sd and @ed
group by grouping sets((h.HID, h.START_DATE, h.END_DATE),(h.HID))

- Giorgi Nakeuri

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- James Z · Accepted Answer

这样做应该就可以了：

declare @startdate date, @enddate date

set @startdate = '20150105'
set @enddate = '20150125'

select
  start_date,
  end_date,
  datediff(
    day, 
    case when @startdate > start_date then @startdate else start_date end, 
    case when @enddate < end_date then @enddate else end_date end) as duration,
  price_per_day
from
  reservation
where
  end_date >= @startdate and
  start_date <= @enddate

这只是处理重叠范围的情况，如果预订开始时间是正确的，则使用它，否则使用搜索条件，结束日期也是同样。天数和价格是分开的，但是您可以将它们相乘以获得结果。

SQL Fiddle: http://sqlfiddle.com/#!3/4027b3/1 编辑，获取总和的方法：

declare @startdate date, @enddate date

set @startdate = '20150105'
set @enddate = '20150125'

select
  sum(datediff(
    day, 
    case when @startdate > start_date then @startdate else start_date end, 
    case when @enddate < end_date then @enddate else end_date end)  
  * price_per_day)
from
  reservation
where
  end_date >= @startdate and
  start_date <= @enddate