返回正确结果和错误结果的房价 [英] Room rates returning with correct results and wrong results
问题描述
大家好.我在这里遇到了一个大问题,我仍在努力寻找解决方案.我有一个预订系统,它只显示酒店价格.一旦客户要求预订房间,我们会通过联系酒店手动检查空房情况.所以可用性部分我们不想实现没有网站.然而,费率系统有一个小问题.例如,如果我搜索 2014 年 2 月 27 日至 2014 年 3 月 2 日之间属于两个日期范围的房价,我会得到两个结果,其中一个结果未完全完成.这是我的数据库
Hi all the experts out there. Am having a big issue here and am still working to find a solution. I have a reservation system in which it shows the hotel rates only. once a customer request a room we check availability manually by contacting the hotel. So the availability part we do not want to implement no the website. However the rate system is having a little problem. For example if i search for room rates from 2014-02-27 to 2014-03-02 which falls in to two date ranges i get two results in which one result is not fully completed. Here is my database
room id room_name rate_starts rate_ends single_room double_room
1 standard 2014-01-01 2014-02-28 150 200
1 standard 2014-03-01 2014-03-05 200 250
1 standard 2014-03-06 2014-03-31 300 350
2 Garden Villa 2014-01-01 2014-02-28 300 400
这是我的查询
SELECT room_id, room_name,
SUM(`single_room` * DateDiff(
Least(rate_ends + INTERVAL 1 DAY, '$ends'),
Greatest(rate_starts, '$starts')
)) AS Total
FROM
room_rate
WHERE
hotel_id = '$hotel_id' AND
rate_ends >= '$starts' AND
rate_starts <= '$ends'
GROUP BY room_id
这是我的结果
Standard room 500
Garden Villa 600
我期待这个答案标准间500
Am expecting this answer Standard room 500
这里有一些关于我真正喜欢的东西的解释.我的搜索是从 2014-02-27 到 2014-03-02,这在数据库中属于两个日期范围.标准间(150 X 02 = 300 + 200 X 01 = 200)= 500花园别墅(300 X 02 = 600 + DB 中未提供此日期范围)= 600
Here is a little explanation on what am really in to. My search is from 2014-02-27 to 2014-03-02 which is falling in to two date ranges in the database. Standard room (150 X 02 = 300 + 200 X 01 = 200) = 500 Garden villa (300 X 02 = 600 + this date range is not provided in DB) = 600
这意味着花园别墅的价格没有按照我的搜索要求完全完成.所以我不应该显示花园别墅的价格.我希望那里的所有天才都会考虑为我找到解决此问题的方法.
this means that the Garden villa rate is not fully completed as per my search request. So i should not display the garden villa rates. I hope all the genius people out there would consider finding me a solution to this issue.
推荐答案
SELECT room_id, room_name,
SUM(single_room * DateDiff(
Least(rate_ends + INTERVAL 1 DAY, '$ends'),
Greatest(rate_starts, '$starts')
)) AS Total,
sum(DateDiff(
Least(rate_ends + INTERVAL 1 DAY, '$ends'),
Greatest(rate_starts, '$starts')
)) as num_days
FROM
room_rate
WHERE
rate_ends >= '$starts' AND
rate_starts <= '$ends'
GROUP BY
room_id
having
sum(DateDiff(
Least(rate_ends + INTERVAL 1 DAY, '$ends'),
Greatest(rate_starts, '$starts')
)) = datediff('$ends', '$starts')
这个答案工作正常,感谢@user1455836三周的脑力劳动终于解决了
This answer is working fine and all thanks goes to @user1455836 three weeks of mind work finally solved
这篇关于返回正确结果和错误结果的房价的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!