0

Here my table structure:

___Rooms

|--------|----------|
| ROO_Id | ROO_Name |
|--------|----------|
|      1 |     Blue |
|      2 |      Red |
|      3 |   Yellow |
|--------|----------|

___Rates

|--------|---------------|-------------|-----------|-------------------|---------------|
| RAT_Id | RAT_DateStart | RAT_DateEnd | RAT_Price | RAT_RoomsAffected | RAT_RoomsList |
|--------|---------------|-------------|-----------|-------------------|---------------|
|     90 |    2019-04-01 |  2019-05-01 |    139.00 |               all |               |
|     91 |    2019-05-01 |  2019-12-31 |    159.00 |              list |           1,2 |
|     92 |    2019-05-01 |  2019-12-31 |    129.00 |              list |             3 |
|--------|---------------|-------------|-----------|-------------------|---------------|

___Availabilities

|--------|------------|------------|------------|
| AVA_Id | AVA_RoomId | AVA_Status | AVA_Date   |
|--------|------------|------------|------------|
|    203 |          1 |       Open | 2019-04-01 |
|    204 |          1 |       Open | 2019-04-02 |
|    205 |          1 |       Open | 2019-04-03 |
|    206 |          1 |       Open | 2019-04-04 |
|    207 |          1 |       Open | 2019-04-05 |
|    208 |          1 |      Close | 2019-04-06 |
|    209 |          1 |      Close | 2019-04-07 |
|--------|------------|------------|------------|

Here's a quick description of these 3 tables:

  • _Rooms contains informations about the rooms I can rent in my hotel.
  • ___Rates contains the rate informations I can apply for a special period and for rooms (if RAT_RoomsAffected is set to all it means that the rate can be applied for all the rooms I have in my hotel. If RAT_RoomsAffected is set to list, the rate could be applied only the the rooms id in the RAT_RoomsList field).
  • ___Availabilities list per day the possibility (Open) to book this room or the no possibility to book a room (Close).

My problem is the following:

I would like to list the rooms that respect the two following conditions:

  • This rooms should be with the Open status (AVA_Status) for the dates a client wants to book in my hotel. For example, from the 2019-04-02 to the 2019-04-04 (2 nights).

  • This rooms should be linked with a rate in _Rates table. So depending the client dates of stay I need to loop into this table and find a rate for this period.

  • Finally the rates found should match the ROO_Id found in the first step. A ROO_Id could match if RAT_RoomsAffected = all or when this ROO_Id is found into the RAT_RoomsList list.


The desired output should be this one from the 2019-04-02 to the 2019-04-04:

|--------|----------|-----------|
| ROO_Id | ROO_Name | RAT_Price |
|--------|----------|-----------|
|      1 |     Blue |    139.00 |
|--------|----------|-----------|

This is the SQLFiddle to help you: https://www.db-fiddle.com/f/kPY6FmKw1SZJjTHUbLj5Pe/0

Thanks so much for any help I will receive.

1
SELECT ro.ROO_Id, ro.ROO_Name, ra.RAT_Price
FROM ___Rooms ro, ___Rates ra, ___Availabilities av
WHERE (FIND_IN_SET(ro.roo_id, ra.RAT_RoomsList) OR ra.RAT_RoomsList='')
  AND av.AVA_Status = 'Open'
  AND av.AVA_RoomId = ro.roo_id
  AND av.AVA_Date BETWEEN GREATEST(ra.RAT_DateStart, '2019-04-02') 
                      AND LEAST(ra.RAT_DateEnd, '2019-04-04')
GROUP BY ro.ROO_Id, ro.ROO_Name, ra.RAT_Price
HAVING COUNT(*) = 1 + DATEDIFF('2019-04-04', '2019-04-02')

fiddle

  • Hello. I really like your approach. Supposing I have 2 rates that match the dates (1 for the first night and 1 for the second night), how can I deal with this please? See here: db-fiddle.com/f/dRAC4vE18X8jKLV12Fyx15/0. I've just add (91, '2019-04-02', '2019-05-01', '139.00', 'all', '') in the ___Rates table. – Testy Apr 12 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.