Hotel Booking Rates SQL Problem

Hi I've been trying to get my own system for checking rooms rates going after reading other questions here on StackOverfl开发者_运维知识库ow.

My query works fine and produces correct number of days when there is no overlap of rates in the reservation dates but when is an overlap i get an extra day on my second row of results.

For example a person arrives on the 2011-04-14 and leaves 2011-04-16 (2 Days). There is a rate change from 66 to 70 on the 15th so he should have 1 day at the 66 rate and 1 day at the 70 rate.

I have tried without TIMEDATE - just DATE but same thing happens.

Query

    SELECT rates.rate_id, rate_start_date, rate_end_date, rate_price, 
(DATEDIFF( IF (rate_end_date > '2011-04-16 14:00:00' , '2011-04-16 14:00:00', rate_end_date),
IF ( rate_start_date < '2011-04-14 12:00:00' , '2011-04-14 12:00:00' , rate_start_date )) +1 ) 
AS days FROM rates 
WHERE rate_start_date <= '2011-04-16 14:00:00' AND rate_end_date > '2011-04-14 12:00:00' 
ORDER BY rate_price ASC

rates table

rate_id rate     rate_start_date        rate_end_date
1       70      2011-04-15 00:00:00   2011-05-31 23:59:59
2       80      2011-06-01 00:00:00   2011-06-30 23:59:59
3      100      2011-07-01 00:00:00   2011-08-31 23:59:59
4       80      2011-09-01 00:00:00   2011-09-30 23:59:59
5       70      2011-10-01 00:00:00   2011-10-31 23:59:59
6       45      2011-11-01 00:00:00   2011-12-31 23:59:59
0       66      2011-01-01 00:00:00   2011-04-14 23:59:59

result

rate_id  rate_start_date         rate_end_date       rate    days
0       2011-01-01 00:00:00    2011-04-14 23:59:59  66  1
1       2011-04-15 00:00:00    2011-05-31 23:59:59  70  2 <----this should be 1 day

I would really appreciate any help or an explaination of why my query gives me the extra day of the second row of results.

Thanks

Between 2011-04-16 16:00 and 2011-04-15 00:00 there is one day and 16 hours, therefore your DATEDIFF(...)+1 is returning (correctly) 1+1 days.

The issue here is the mismatch between the time when the guest arrives/leaves (around noon) and the time when the rate changes (at midnight).

You have to check your requirements, but probably you could ignore the last partial day and have the guest "leave" at 2011-04-15 23:59:59 for rate calculation purposes. In the same way, have the guest "arrive" at 2011-04-14 00:00:00. The extra hours in the first day will compensate the missing hours in the last day.

If I understand your algorithm correctly, you want to include first day even partially, so I would suggest you to use instead of + 1 for each rate do this only for the first one.

Try to use this (MSSQL):

+ CASE WHEN rate_start_date <= '2011-04-14 12:00:00' THEN 1 ELSE 0 end

or (MYSQL):

+ IF (rate_start_date <= '2011-04-14 12:00:00', 1 ,0)

Thanks for your answers, Martin your produced 2 rows but no days...Emilio your answer got me thinking about how I had set up the rates. I changed the rate table to date format instead of datetime and made the rate_end_date the same day as the next rate_start_date.

0   66  2011-01-01  2011-04-15
1   70  2011-04-15  2011-06-01
2   80  2011-06-01  2011-07-01
3   100 2011-07-01  2011-09-01
4   80  2011-09-01  2011-10-01
5   70  2011-10-01  2011-11-01
6   45  2011-11-01  2012-01-01

then dropped the +1 and

SELECT rates.rate_id, rate_start_date, rate_end_date, rate_price, 
(DATEDIFF( IF (rate_end_date > '2011-04-16' , '2011-04-16', rate_end_date), 
IF ( rate_start_date < '2011-04-14' , '2011-04-14' , rate_start_date )) ) 
AS days FROM rates WHERE rate_start_date <= '2011-04-16' 
AND rate_end_date > '2011-04-14' 
ORDER BY rate_price ASC

produced

rate_id   rate_start_date   rate_end_date   rate  days    
    0   2011-01-01  2011-04-15  66  1
    1   2011-04-15  2011-06-01  70  1

and a query with no overlapping rates from the 1st to the 8th of April:

SELECT rates.rate_id, rate_start_date, rate_end_date, rate_price, 
(DATEDIFF( IF (rate_end_date > '2011-04-08' , '2011-04-08', rate_end_date), 
IF ( rate_start_date < '2011-04-01' , '2011-04-01' , rate_start_date )) ) 
AS days FROM rates WHERE rate_start_date <= '2011-04-08' 
AND rate_end_date > '2011-04-01' 
ORDER BY rate_price ASC

produces:

rate_id   rate_start_date    rate_end_date   rate  days
    0     2011-01-01       2011-04-15     66    7

thanks agiain for your help!