酒店预订价格SQL问题 [英] Hotel Booking Rates SQL Problem

问题描述

您好，我一直在试图得到我自己的系统，以检查房间费率后阅读其他问题在这里StackOverflow。

Hi I've been trying to get my own system for checking rooms rates going after reading other questions here on StackOverflow.

我的查询工作正常，并产生正确的天数，当保留日期没有重复率，但是重叠，我在第二行获得额外的一天的结果。

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.

例如，一个人到达2011-04-14并离开2011-04-16（2天）。第15天的费率从66到70，所以他应该有66天的1天和70天的1天。

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.

我已经没有TIMEDATE - 只需DATE，但同样的事情发生。

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

查询

    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

表

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

结果

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.

感谢

推荐答案

感谢您的答案，Martin生成了2行，但没有日... Emilio我想起了我如何设置利率。我将比率表更改为日期格式，而不是datetime，并使rate_end_date与下一个rate_start_date相同。

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

然后删除+1和

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

产生

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

和第1到第8个没有重叠率的查询4月：

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

产生：

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

感谢agiain的帮助！

thanks agiain for your help!

这篇关于酒店预订价格SQL问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！