I have the following tables:
我有以下表格:
Let's assume the first table represents a booking made by a customer for a hotel.
让我们假设第一张表代表一个客户为一家酒店所做的预订。
+------------+-----------+-----------+--------------+----------------+
| booking_id | client_id | room_type | arrival_date | departure_date |
+------------+-----------+-----------+--------------+----------------+
| 1 | 1 | 1 | 2016-05-30 | 2016-06-03 |
+------------+-----------+-----------+--------------+----------------+
The second table contains the prices for different types of room. The prices vary for different periods of time.
第二张表包含不同类型房间的价格。价格随时间的不同而变化。
+---------------+-----------+------------+------------+-------+
| room_price_id | room_type | start_date | end_date | price |
+---------------+-----------+------------+------------+-------+
| 1 | 1 | 2016-03-01 | 2016-05-31 | 150 |
| 2 | 1 | 2016-06-01 | 2016-08-31 | 200 |
+---------------+-----------+------------+------------+-------+
My question is: how can I calculate the total cost for a booking if the price is not the same during the whole period of the booking? (in my case the total cost would be 700)
我的问题是:如果在整个预订期间价格不一致，我如何计算预订的总成本?(就我而言，总成本是700英镑)
I read the following links: MySQL: Select all dates between date range and get table data matching dates and Select data from date range between two dates, but I don't get it on how to solve my problem since the entries in the table with the prices contain only date ranges and not explicit dates as in the first link.
我读下面的链接:MySQL:选择日期范围和表数据匹配之间的所有日期日期和选择日期范围两个日期之间的数据,但我不明白如何解决我的问题,因为表中的条目的价格只包含日期范围和不明确的日期如第一个链接。
1 个解决方案
#1
4
The trick with your question is in formulating the join condition between the booking and prices tables. Once this is out of the way, a simple aggregation gets you the result you want. The join condition should be true whenever any point in a given price range falls within the range of the arrival or departure date.
你的问题的诀窍在于制定预订和价格表之间的连接条件。一旦解决了这个问题，一个简单的聚合就可以得到您想要的结果。当给定价格范围内的任何点在到达或离开日期范围内时，联接条件应该为真。
Then, to count days we use the greater of the arrival date or the start date, and the lesser of the departure date or the end date.
然后，计算天数，我们使用更大的到达日期或开始日期，以及较小的出发日期或结束日期。
SELECT
b.booking_id,
SUM((DATEDIFF(LEAST(b.departure_date, p.end_date),
GREATEST(b.arrival_date, p.start_date)) + 1) * p.price) AS total
FROM booking b
INNER JOIN prices p
ON b.room_type = p.room_type AND
(p.start_date BETWEEN b.arrival_date AND b.departure_date OR
p.end_date BETWEEN b.arrival_date AND b.departure_date)
GROUP BY b.booking_id;
The output for your sample data is 900. This is correct because:
示例数据的输出是900。这是正确的,因为:
2 days x 150 = 300
3 days x 200 = 600
total = 900
Demo here:
演示:
SQLFiddle
And here is another demo which highlights the case where a stay overlaps an entire price range.
这是另一个演示，强调了在整个价格范围内的停留。
#1
4
The trick with your question is in formulating the join condition between the booking and prices tables. Once this is out of the way, a simple aggregation gets you the result you want. The join condition should be true whenever any point in a given price range falls within the range of the arrival or departure date.
你的问题的诀窍在于制定预订和价格表之间的连接条件。一旦解决了这个问题，一个简单的聚合就可以得到您想要的结果。当给定价格范围内的任何点在到达或离开日期范围内时，联接条件应该为真。
Then, to count days we use the greater of the arrival date or the start date, and the lesser of the departure date or the end date.
然后，计算天数，我们使用更大的到达日期或开始日期，以及较小的出发日期或结束日期。
SELECT
b.booking_id,
SUM((DATEDIFF(LEAST(b.departure_date, p.end_date),
GREATEST(b.arrival_date, p.start_date)) + 1) * p.price) AS total
FROM booking b
INNER JOIN prices p
ON b.room_type = p.room_type AND
(p.start_date BETWEEN b.arrival_date AND b.departure_date OR
p.end_date BETWEEN b.arrival_date AND b.departure_date)
GROUP BY b.booking_id;
The output for your sample data is 900. This is correct because:
示例数据的输出是900。这是正确的,因为:
2 days x 150 = 300
3 days x 200 = 600
total = 900
Demo here:
演示:
SQLFiddle
And here is another demo which highlights the case where a stay overlaps an entire price range.
这是另一个演示，强调了在整个价格范围内的停留。