Select dates between date range and sum over a column

Question

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)

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.

I think the total price should be `900`, no? – Tim Biegeleisen Apr 17 '17 at 06:59 — Tim Biegeleisen, Apr 17 '17 at 06:59

Tim Biegeleisen · Accepted Answer · 2017-04-17T07:34:56.920

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:

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.

Select dates between date range and sum over a column

1 Answers1

SQLFiddle