不同季节的酒店房价

Question

我有一个数据库(MySQL)，其中有一个包含日期范围(如开始日期和结束日期)和一个费率字段的表。日期范围表示不同的季节(低谷、高峰期等)。场景是这样的:一个人在酒店登记入住，他的停留时间是两个季节。示例数据如下：

SeasonName         SartDate            EndDate           Rate
Low                01-01-2007          30-04-2007        100.00
High               01-05-2007          31-08-2007        150.00
Peak               01-09-2007          31-12-2007        200.00

客户的入住日期是29-04-2007，退房日期是03-05-2007。我需要计算每个季节的确切夜数，并计算总金额。

集成开发环境是VB6。任何帮助都将不胜感激。

谢谢

汤姆

感谢您的回复。我需要SQL来提取信息。至于日期有效期，让我们假设汇率适用到午夜(00:00)。希望我已经说清楚了。

汤姆

Answer 1

在酒店工作并编写预订系统后，每小时的时间就账单而言无关紧要。一切都是在晚上充电的。(除非你计划经营一个按小时收费的地方！;-))签到和签出是操作上的考虑因素。

如果你真的想写一个真正的预订系统，就不要使用存储过程。它违背了拥有数据库的目的。

此外，像这样写出日期是2007-04-29真的是很好的方式，因为不是每个人都来自同一个地方，这是一个国际标准。还要注意的是，如果你把它转换成一个字符串，它仍然会被正确排序！

你需要制作一个日历表格，因为MySQL没有内置的函数来做这件事。此过程将为您建立日期。

drop table if exists calendar;
create table calendar 
( 
    date_       date        primary key
);

drop procedure fill_calendar;

delimiter $$
create procedure fill_calendar(start_date date, end_date date)
begin
  declare date_ date;
  set date_=start_date;
  while date_ < end_date do
    insert into calendar values(date_);
    set date_ = adddate(date_, interval 1 day);
  end while;
end $$
delimiter ;

call fill_calendar('2007-1-1', '2007-12-31');

来自：http://www.ehow.com/how_7571744_mysql-calendar-tutorial.html

drop table if exists rates;
create table rates
(
    season          varchar(100)    primary key,
    start_date      date            references calendar(date_),
    end_date        date            references calendar(date_),
    rate            float
);
insert into rates values ('Low',    '2007-01-01',   '2007-04-30',   100.00);
insert into rates values ('High',   '2007-05-01',   '2007-08-31',   150.00);
insert into rates values ('Peak',   '2007-09-01',   '2007-12-21',   200.00);

select * from rates;
season  start_date      end_date        rate
Low     2007-01-01      2007-04-30      100
High    2007-05-01      2007-08-31      150
Peak    2007-09-01      2007-12-21      200

我将忽略你在问题中给出的日期，并假设客户没有在时间上倒退。

select
    date_, rate
    from calendar
    join rates
        on date_ >= start_date and date_ <= end_date

    where date_ between '2007-04-29' and '2007-5-01'
;
date_   rate
2007-04-29      100
2007-04-30      100
2007-05-01      150

select
    sum(rate)

    from calendar
    join rates
        on date_ >= start_date and date_ <= end_date

    where date_ between '2007-04-29' and '2007-5-01'
sum(rate)
350

而且，正如您所看到的，sql非常简洁和可读，不需要借助函数或过程。这将能够适当地扩展和处理更复杂的问题。此外，由于数据是基于表的，因此可以使用引用检查。

Answer 2

我使用DATEDIFF函数来知道两个日期之间的天数。但由于此函数返回不包括前一天的天数(例如，DATEDIFF(d, '2007-04-29', '2007-04-30')返回1而不是2)，因此我使用了如下比率表：

SeasonName         StartDate           EndDate           Rate
Low                01-01-2007          01-05-2007        100.00
High               01-05-2007          01-09-2007        150.00
Peak               01-09-2007          01-01-2008        200.00

这是我使用的查询。两个选择的内部根据季节(季节的结束日期或其在结帐日期之前的日期)计算客户停留的有效结束日期。

我使用02-05-2007作为客户的停留结束日期，而不是03-05-2007，因为通常客户不会为他结账的那一天付款。

SELECT SeasonName, DATEDIFF(d, '2007-04-29', EffectiveEndDate) as NumberOfDays, Rate
FROM (
    SELECT SeasonName, 
    CASE 
    WHEN EndDate < '2007-05-02' THEN EndDate
    ELSE '2007-05-02' 
    END AS EffectiveEndDate, 
    Rate
    FROM HotelRate
    WHERE (StartDate <= '2007-04-29' and EndDate > '2007-04-29')
    or (StartDate <= '2007-05-02' and EndDate > '2007-05-02')
) as SubSelect

这给了我这样的结果：

SeasonName NumberOfDays  Rate
Low        2             100.00
High       3             150.00

我希望它能有所帮助:)

Answer 3

免责声明：这不是最有效的，但它更清晰，如果您将价目表缓存在数组中，对性能的影响将毫无意义。

Dim numberOfDays As Integer, i As Integer
Dim CheckInDate As Date, CheckOutDate As Date, CurrDate As Date
Dim TotalRate As Currency
TotalRate = 0
CheckInDate = DateSerial(2007, 4, 29)
CheckOutDate = DateSerial(2007, 5, 3)
''// -1 asumming the last day is checkout day
numberOfDays = DateDiff("d", CheckInDate, CheckOutDate) - 1 
For i = 0 To numberOfDays
   CurrDate = DateAdd("d", i, CheckInDate)
   TotalRate = TotalRate + CalculateRateForDay(CurrDate)
Next