MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE hotel (
  hotel_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  hotel_name VARCHAR(255) NOT NULL
);
INSERT INTO hotel VALUES (1, 'hotel 1'), (2, 'hotel 2');

CREATE TABLE room (
  room_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  hotel_id INT UNSIGNED NOT NULL,
  FOREIGN KEY (hotel_id) REFERENCES hotel (hotel_id)
);
INSERT INTO room VALUES (1, 1), (2, 1), (3, 2), (4, 2);

CREATE TABLE room_price (
  room_id INT UNSIGNED NOT NULL,
  start_date DATE NOT NULL,
  price DECIMAL(6, 2) NOT NULL,
  PRIMARY KEY (room_id, start_date),
  FOREIGN KEY (room_id) REFERENCES room (room_id)
);
INSERT INTO room_price VALUES
  (1, '2023-11-01', '71'),
  (1, '2023-12-01', '81'),
  (2, '2023-11-01', '72'),
  (2, '2023-12-02', '82'),
  (3, '2023-11-01', '73'), -- note only single price during booking for room 3
  (4, '2023-11-01', '74'),
  (4, '2023-12-04', '84'),
  (4, '2023-12-11', '94');

Records: 2  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0



Records: 8  Duplicates: 0  Warnings: 0


/* Add a calendar table */
CREATE TABLE calendar (dt DATE NOT NULL PRIMARY KEY);

CREATE PROCEDURE `filldates`(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO calendar (dt) VALUES (dateStart);
    SET dateStart = dateStart + INTERVAL 1 DAY;
  END WHILE;
END
CALL filldates('2023-11-01', '2023-12-31');
SELECT COUNT(*) FROM calendar;


COUNT(*)


61


/* checkin 2023-11-26 and checkout 2023-12-15 */
SET @checkin = '2023-11-26', @checkout = '2023-12-15';

SELECT r.room_id, c.dt, (
  SELECT price
  FROM room_price
  WHERE room_id = r.room_id
  AND start_date <= c.dt
  ORDER BY start_date DESC
  LIMIT 1
) AS price
FROM calendar c
CROSS JOIN room r
WHERE c.dt >= @checkin AND c.dt < @checkout;


room_id
dt
price


1
2023-11-26
71.00

2
2023-11-26
72.00

3
2023-11-26
73.00

4
2023-11-26
74.00

1
2023-11-27
71.00

2
2023-11-27
72.00

3
2023-11-27
73.00

4
2023-11-27
74.00

1
2023-11-28
71.00

2
2023-11-28
72.00

3
2023-11-28
73.00

4
2023-11-28
74.00

1
2023-11-29
71.00

2
2023-11-29
72.00

3
2023-11-29
73.00

4
2023-11-29
74.00

1
2023-11-30
71.00

2
2023-11-30
72.00

3
2023-11-30
73.00

4
2023-11-30
74.00

1
2023-12-01
81.00

2
2023-12-01
72.00

3
2023-12-01
73.00

4
2023-12-01
74.00

1
2023-12-02
81.00

2
2023-12-02
82.00

3
2023-12-02
73.00

4
2023-12-02
74.00

1
2023-12-03
81.00

2
2023-12-03
82.00

3
2023-12-03
73.00

4
2023-12-03
74.00

1
2023-12-04
81.00

2
2023-12-04
82.00

3
2023-12-04
73.00

4
2023-12-04
84.00

1
2023-12-05
81.00

2
2023-12-05
82.00

3
2023-12-05
73.00

4
2023-12-05
84.00

1
2023-12-06
81.00

2
2023-12-06
82.00

3
2023-12-06
73.00

4
2023-12-06
84.00

1
2023-12-07
81.00

2
2023-12-07
82.00

3
2023-12-07
73.00

4
2023-12-07
84.00

1
2023-12-08
81.00

2
2023-12-08
82.00

3
2023-12-08
73.00

4
2023-12-08
84.00

1
2023-12-09
81.00

2
2023-12-09
82.00

3
2023-12-09
73.00

4
2023-12-09
84.00

1
2023-12-10
81.00

2
2023-12-10
82.00

3
2023-12-10
73.00

4
2023-12-10
84.00

1
2023-12-11
81.00

2
2023-12-11
82.00

3
2023-12-11
73.00

4
2023-12-11
94.00

1
2023-12-12
81.00

2
2023-12-12
82.00

3
2023-12-12
73.00

4
2023-12-12
94.00

1
2023-12-13
81.00

2
2023-12-13
82.00

3
2023-12-13
73.00

4
2023-12-13
94.00

1
2023-12-14
81.00

2
2023-12-14
82.00

3
2023-12-14
73.00

4
2023-12-14
94.00


/* checkin 2023-11-26 and checkout 2023-12-15 */
SET @checkin = '2023-11-26', @checkout = '2023-12-15';

SELECT r.room_id, COUNT(*) AS nights, (
  SELECT price
  FROM room_price
  WHERE room_id = r.room_id
  AND start_date <= c.dt
  ORDER BY start_date DESC
  LIMIT 1
) AS price
FROM calendar c
CROSS JOIN room r
WHERE c.dt >= @checkin AND c.dt < @checkout
GROUP BY room_id, price;


room_id
nights
price


1
5
71.00

1
14
81.00

2
6
72.00

2
13
82.00

3
19
73.00

4
8
74.00

4
7
84.00

4
4
94.00


/* checkin 2023-11-26 and checkout 2023-12-15 */
SET @checkin = '2023-11-26', @checkout = '2023-12-15';

SELECT room_id, SUM(price) AS price
FROM (
  SELECT r.room_id, c.dt, (
    SELECT price
    FROM room_price
    WHERE room_id = r.room_id
    AND start_date <= c.dt
    ORDER BY start_date DESC
    LIMIT 1
  ) AS price
  FROM calendar c
  CROSS JOIN room r
  WHERE c.dt >= @checkin AND c.dt < @checkout
) AS t
GROUP BY room_id;


room_id
price


1
1489.00

2
1498.00

3
1387.00

4
1556.00

room_id	dt	price
1	2023-11-26	71.00
2	2023-11-26	72.00
3	2023-11-26	73.00
4	2023-11-26	74.00
1	2023-11-27	71.00
2	2023-11-27	72.00
3	2023-11-27	73.00
4	2023-11-27	74.00
1	2023-11-28	71.00
2	2023-11-28	72.00
3	2023-11-28	73.00
4	2023-11-28	74.00
1	2023-11-29	71.00
2	2023-11-29	72.00
3	2023-11-29	73.00
4	2023-11-29	74.00
1	2023-11-30	71.00
2	2023-11-30	72.00
3	2023-11-30	73.00
4	2023-11-30	74.00
1	2023-12-01	81.00
2	2023-12-01	72.00
3	2023-12-01	73.00
4	2023-12-01	74.00
1	2023-12-02	81.00
2	2023-12-02	82.00
3	2023-12-02	73.00
4	2023-12-02	74.00
1	2023-12-03	81.00
2	2023-12-03	82.00
3	2023-12-03	73.00
4	2023-12-03	74.00
1	2023-12-04	81.00
2	2023-12-04	82.00
3	2023-12-04	73.00
4	2023-12-04	84.00
1	2023-12-05	81.00
2	2023-12-05	82.00
3	2023-12-05	73.00
4	2023-12-05	84.00
1	2023-12-06	81.00
2	2023-12-06	82.00
3	2023-12-06	73.00
4	2023-12-06	84.00
1	2023-12-07	81.00
2	2023-12-07	82.00
3	2023-12-07	73.00
4	2023-12-07	84.00
1	2023-12-08	81.00
2	2023-12-08	82.00
3	2023-12-08	73.00
4	2023-12-08	84.00
1	2023-12-09	81.00
2	2023-12-09	82.00
3	2023-12-09	73.00
4	2023-12-09	84.00
1	2023-12-10	81.00
2	2023-12-10	82.00
3	2023-12-10	73.00
4	2023-12-10	84.00
1	2023-12-11	81.00
2	2023-12-11	82.00
3	2023-12-11	73.00
4	2023-12-11	94.00
1	2023-12-12	81.00
2	2023-12-12	82.00
3	2023-12-12	73.00
4	2023-12-12	94.00
1	2023-12-13	81.00
2	2023-12-13	82.00
3	2023-12-13	73.00
4	2023-12-13	94.00
1	2023-12-14	81.00
2	2023-12-14	82.00
3	2023-12-14	73.00
4	2023-12-14	94.00