酒店房价不同季节 [英] Hotel Room Rates for different seasons
问题描述
SeasonName SartDate EndDate Rate
低01-01-2007 30-04-2007 100.00
高01-05-2007 31-08-2007 150.00
峰01-09-2007 31-12-2007 200.00
客户的入住日期为29-04-2007，退房日期为03-05-2007。我需要计算每个季节的确切夜数，并计算总金额。
IDE是VB6。任何帮助将非常感谢。
感谢
Tom
感谢响应。我需要SQL提取信息。至于日期有效期，我们假定费率适用于午夜（00:00）。希望我已经澄清了。
Tom
酒店和预订系统，小时的时间是无关紧要的
，因为结算。一切都是夜晚收费的。 （除非您计划经营一个按小时收费的地方！;-)）入住和退房是操作考虑。
如果你真的想写一个真正的预订系统，不要使用存储的过程。
它违反了拥有数据库的目的。
另外，写出这样的日期是2007-04-29是非常好的方式，因为并不是每个人都是从同一个地方，这是一个国际标准。还要注意，如果你把它变成一个字符串，它仍然会被正确排序！
你需要制作一个calandar表，因为MySQL没有内置的功能做到这一点
此过程将为您建立日期。
drop table if exists calendar;
创建表日历
（
date_ date主键
）;
drop procedure fill_calendar;
分隔符$$
创建过程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）;
结束while;
end $$
分隔符;
调用fill_calendar（'2007-1-1'，'2007-12-31'）;
from： http://www.ehow.com/how_7571744_mysql-calendar-tutorial.html
降表如果存在率;
创建表速率
（
季节varchar（100）主键，
start_date日期引用日历（date_），
end_date日期引用日历（date_），
rate float
）;
插入费率值（低，2007-01-01，2007-04-30，100.00）;
插入费率值（'高'，'2007-05-01'，'2007-08-31'，150.00）;
插入费率值（'Peak'，'2007-09-01'，'2007-12-21'，200.00）;
从价格中选择*
season start_date end_date rate
低2007-01-01 2007-04-30 100
高2007-05-01 2007-08-31 150
峰2007-09-01 2007-12-21 200
我将忽略您在问题中提供的日期，假设客户不及时向后退。
选择
date_，从$ $ $ $ $ $ $ $
加入
date_> = start_date和date_< = end_date
其中date_ between'2007-04-29'和'2007-5-01'
;
date_ rate
2007-04-29 100
2007-04-30 100
2007-05-01 150
选择
sum （rate）
从日历
加入率
on date_> = start_date和date_< = end_date
其中date_ between'2007-04 -29'和'2007-5-01'
sum（rate）
350
而且，正如你所看到的那样，sql是非常简洁和可读的，而无需使用函数或过程。这将能够正确扩展并处理更复杂的问题。此外，它可以使用参考检查，因为数据是基于表的。
I have a database (MySQL) with a table containing date ranges (as startdate and enddate) and a rate field. The date range implies different seasons (low, high etc.). The scenario is such that a person checks in the hotel and his duration of stay is in two seasons. A sample data is like below:
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
The client's Check In Date is 29-04-2007 and Check Out Date is 03-05-2007. I need to calculate the exact number of nights for each season and also calculate the total amount.
The IDE is VB6. Any help will be extremely appreciated.
Thanks
Tom
Thanks for the response. I need the SQL to extract the information. As for the date validity, lets assume the rate applies till midnight (00:00). Hope i have clarified.
Tom
Having worked in a hotel and written the reservation system, hourly time is irrelevant as far as billing goes. Everything is always charged by night. (Unless you plan to run a place that charges by the hour! ;-)) Check-in and check-out are operational considerations.
Do not use stored procuedures if you actually want to write a real reservation system. It defeats the purpose of having a database.
Also, writing out dates like this is 2007-04-29 is really great way because not every one is from the same place and this is an international standard. Also notice, if you were to turn this into a string it will still be sorted correctly!
You need make a calandar table as MySQL does not have in built in functions to do it. This procedure will build up dates for you.
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');
from: 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
I'm going to ignore the dates you have given in your question and the assume the client is not travelling backwards in time.
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
And, as you can see the sql is quite concise and readable without resorting to functions or procedures. This will be able to scale properly and handle more complex questions. Also, it enables referential checking to be used since the data is table based.
这篇关于酒店房价不同季节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
