Select dates between date range and column sum
I have the following tables:
Suppose the first table is an order 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 prices for different types of rooms. Prices change at different times.
+---------------+-----------+------------+------------+-------+
| 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 of the booking if the price will not be the same throughout the entire booking period? (in my case, the total cost would be 700)
I have read the following links: MySQL: select all dates between date range and get the date data of the table and Select data from the date range between two dates , but I don’t understand how to solve my problem as records in the price table only contain date ranges rather than explicit dates as in the first link.
source to share
The trick with your question is to formulate a join condition between tables booking
and prices
. Once that doesn't work, simple aggregation gives you the result you want. The join condition must be true when any point in the given price range falls within the range of the arrival or departure date.
Then, to count the days, we use a larger arrival or start date and a smaller departure or 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 is 900. This is correct because:
2 days x 150 = 300
3 days x 200 = 600
total = 900
Demo here:
SQLFiddle
And here's another demo that highlights the case where the stay covers the entire price range.
source to share