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.

Answer 2

I used the DATEDIFF function to know the number of days between 2 dates. But since this function returns the number of days excluding the last day (e.g. DATEDIFF(d, '2007-04-29', '2007-04-30') return 1 instead of 2), I used a rate table like this:

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

This is the query I used. The inner of the two SELECTs calculates an effective end date for the customer's stay according to the season (either the end date of the season or it's date prior to it's check out date).

I used 02-05-2007 as the customer's stay end date instead of 03-05-2007 since usually a customer does does not pay for the day he checks out.

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

This gives me this result:

SeasonName NumberOfDays  Rate
Low        2             100.00
High       3             150.00

I hope it helps :)

Answer 3

Disclaimer: This is not the most efficient one, but it's the more clear, and if you have the price list cached in an array, the performance hit will be meaningless.

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

Answer 4

Use an auxiliary calendar table:

SELECT S1.client_ID, H1.Rate, C1.dt
  FROM HotelRates AS H1
       INNER JOIN Calendar AS C1
          ON C1.dt BETWEEN H1.SartDate AND H1.EndDate
       INNER JOIN Stays AS S1
          ON C1.dt >= check_in_date AND 
             C1.dt < check_out_date;