Question 290127 in Mysql Question posted in
The official MySQL documentation can be found here.

How to query MySQL database to get price for each night in a date range?

I have these three tables:

hotel
hotel_id
hotel_name
room
room_id
hotel_id
room_price
room_id
start_date
price

Where ‘start_date’ stores the date the price changed to ‘price’.

I know how to query the database to get the price for a given room_id and given date for the hotel stay. So I can do it, however, when it comes to doing this for a long date range, this becomes impractical.

Therefore how can I query the database so that I get an output like

room_id date price
1 check_in x1
… … x2
… … …
1 check_out xm
2 check_in y1
… … y2
… … …
2 check_out ym
… … …
… … …
… … …
n check_in z1
… … z2
… … …
n check_out zm

Where ‘check_in’ here is the first night chosen by customer and ‘check_out’ is the last night selected by customer

This would make it a lot easier to calculate total prices for a given room instead of having to do each day individually.

NB: I am using MySQL 5.7

Tags:

2

Answers


  1. 0

    You could either create your date series dynamically, as shown in the answer suggested by Bagus Tesa, or maintain a calendar table. Given that dates play a big part in booking/reservations apps, I would suggest creating a simple calendar table.

    For this example I am using a calendar table with just a single dt (DATE) column, which you can easily populate with a stored proc (or any other method you choose):

    CREATE TABLE calendar (dt DATE NOT NULL PRIMARY KEY);

DELIMITER $$
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$$
DELIMITER ;

CALL filldates('2023-01-01', '2039-12-31');

    Then it is a simple case of a CROSS JOIN between the dates for the booking and rooms, and a correlated subquery to retrieve the price per room per day:

    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;

    Note: although the keyword CROSS has no significance in MySQL (it’s just an INNER join with no ON clause), I think it makes the intent clear.

    Here’s a db<>fiddle.

    Login or Signup to reply.
  2. 0

    The following query relies on join and group by instead of sub query. It took inspiration from generating date series for the date reference. You can replace the date generation with a concrete date table as user1191247 answer suggested.

    select rp.room_id as room_id,
       date_series.date as date,
       any_value(rp.price) as price
  from
  (select *
     from room_price
     order by start_date desc
  ) as rp
  join
  (select * from 
    (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) date from
     (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
    where date between '2023-01-01' and '2023-05-31'
  ) as date_series
  on rp.start_date <= date_series.date
  group by rp.room_id, date_series.date
  order by rp.room_id, date_series.date;

    See fiddle: https://www.db-fiddle.com/f/umGDya94iodhMcJbTdvkt6/0

    The cornerstone to circumvent SELECT list is not in GROUP BY clause and contains non-aggregated column error is using Any_Value(). You could have the similar effect by disabling ONLY_FULL_GROUP_BY for the session or permanently.

    However, do aware that the Any_Value() behave differently between Mysql 5.7 and Mysql 8.0 – which kind of a surprise.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search