mysql - Hotel Room Rates for different seasons

Question

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

Answer 1

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.