Hi I have been looking to get my very own system for checking rooms rates pursuing reading through other questions here on StackOverflow.

My query works fine and produces correct length of time when there's no overlap of rates within the reservation dates however when is definitely an overlap i recieve an additional day on my small second row of results.

For instance an individual arrives around the 2011-04-14 leaving 2011-04-16 (a couple of days). There's an interest rate vary from 66 to 70 around the 15th so he must have one day in the 66 rate and one day in the 70 rate.

I've attempted without TIMEDATE - just DATE but same factor 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'd really appreciate any help or perhaps an explaination of why my query provides me with the additional day's the 2nd row of results.

Thanks

Between 2011-04-16 16:00 and 2011-04-15 00:00 there's eventually and 16 hrs, therefore your DATEDIFF(...)+1 is coming back (properly) 1+1 days.

The problem this is actually the mismatch between your time once the guest arrives/leaves (around noon) and also the time once the rate changes (at night time).

You need to look at your needs, but most likely you can disregard the last partial day and also have the guest "leave" at 2011-04-15 23:59:59 for rate calculation reasons. In the same manner, possess the guest "arrive" at 2011-04-14 00:00:00. The additional hrs in the very first day will compensate the missing hrs within the last day.

Basically understand your formula properly, you need to include first day even partly, so I recommend you to employ rather than + 1 for every rate do that just for the first.

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)

Interesting solutions, Martin your created 2 rows but no days...Emilio the way to go got me considering the way i had setup the rates. I transformed the speed table up to now format rather than datetime making the speed_finish_date within 24 hours because 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

created

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

along with a query without any overlapping rates in the first towards the eighth 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 the help!