返回正确结果和错误结果的房价 [英] 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屋！