计算给定日期之间的价格 [英] calculate price between given dates

问题描述

Hotel_id   Room_id   Room_type   Start_date   End_date     Price
----------------------------------------------------------------
   13        2        standard   2012-08-01   2012-08-15   7000
   13        2        standard   2012-08-16   2012-08-31   7500
   13        2        standard   2012-09-01   2012-09-30   6000
   13        3         luxury    2012-08-01   2012-08-15   9000
   13        3         luxury    2012-08-16   2012-08-31   10000
   13        3         luxury    2012-09-01   2012-09-30   9500

这是我表的结构和数据.

Hi this is the structure and data of my table.

我需要为酒店预订创建一个mysql查询，该查询将与数据库用户输入的数据相匹配:

I need to create a mysql query for hotel booking, that would match in database user entered data:

1. 他们想要签入和签出的日期
2. 房间类型

例如:

如果用户根据这些日期(2012-08-30至2012-09-04)选择带豪华客房的酒店 8月30日和31日的总费用为(10000 * 2)+ 9月1日，2日和3日的费用为(9500 * 3)(不包括第4天的退房日) 这意味着总价格将为20000 + 28500 = 48500

If user selects Hotel with luxury room based on these dates (2012-08-30 to 2012-09-04) the total cost would be (10000*2) for 30th and 31st Aug + (9500*3) for 1st,2nd and 3rd Sep(4th checkout day don't include) that means total price will be 20000+28500=48500

因此，查询应根据Hotel_id，Room_id，Start_date，End_date和Price过滤总价格

So query should filter total price based on the Hotel_id,Room_id,Start_date,End_date and Price

谢谢

推荐答案

使用以下解决方案:

SELECT     SUM(
               CASE WHEN a.Start_date = b.min_sd AND a.Start_date <> b.max_sd THEN
                        (DATEDIFF(a.End_date, '2012-08-30')+1) * a.Price
                    WHEN a.Start_date = b.max_sd AND a.Start_date <> b.min_sd THEN
                        DATEDIFF('2012-09-04', a.Start_date) * a.Price
                    WHEN (a.Start_date,a.Start_date) IN ((b.min_sd,b.max_sd)) THEN
                        (DATEDIFF('2012-09-04', '2012-08-30')+1) * a.Price
                    WHEN a.Start_date NOT IN (b.min_sd, b.max_sd)             THEN
                        (DATEDIFF(a.End_date, a.Start_date)+1) * a.Price
               END 
           ) AS totalprice
FROM       rooms a
CROSS JOIN (
           SELECT MIN(Start_date) AS min_sd,
                  MAX(Start_date) AS max_sd
           FROM   rooms
           WHERE  Room_type   = 'luxury'     AND
                  End_date   >= '2012-08-30' AND
                  Start_date <= '2012-09-04'
           ) b
WHERE      a.Room_type   = 'luxury'     AND
           a.End_date   >= '2012-08-30' AND
           a.Start_date <= '2012-09-04'

分别用输入的开始和结束日期替换2012-08-30和2012-09-04的出现.

Replace occurances of 2012-08-30 and 2012-09-04 with your input start and end dates respectively.

这将说明开始日期和结束日期在同一月份，也可能跨越多个月.

This will account for start and end dates being in the same month as well as spanning across multiple months.

SQLFiddle演示

这篇关于计算给定日期之间的价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！