Table contains data about hotel rooms.
We can see the same Room available for different date periods - at different Prices. AccmdMenTypeCode - is the Code that shows how many people can stay in this type of room.
A person wants to stay in the room from 2025-01-05 to 2025-04-01 (87 days). I already calculated OverlapDaysCount field that shows days count for every date period between Start and End. Now we need to have all the cases when we can accomodate the person in the room for 87 days. And calculate the Price for the Room.
| Id | HotelCode | AccomodationPeriodBegin | AccomodationPeriodEnd | RoomType | AccmdMenTypeCode | Price | OverlapDaysCount |
|---|---|---|---|---|---|---|---|
| 1 | MONTE | 2025-01-10 | 2025-03-10 | DGV | 01010003 | 99 | 60 |
| 2 | MONTE | 2025-01-10 | 2025-03-10 | DGV | 01010003 | 99 | 60 |
| 3 | MONTE | 2025-03-06 | 2025-05-14 | DGV | 01010003 | 106 | 27 |
| 4 | MONTE | 2025-03-06 | 2025-05-14 | DGV | 01010003 | 106 | 27 |
| 5 | MONTE | 2024-07-01 | 2025-07-15 | DGV | 01010003 | 109 | 87 |
| 6 | MONTE | 2024-07-01 | 2025-07-15 | DGV | 01010003 | 109 | 87 |
| 7 | MONTE | 2025-01-10 | 2025-03-10 | DGV | 01010312 | 99 | 60 |
| 8 | MONTE | 2025-01-10 | 2025-03-10 | DGV | 01010312 | 99 | 60 |
| 9 | MONTE | 2025-03-06 | 2025-05-14 | DGV | 01010312 | 106 | 27 |
| 10 | MONTE | 2025-03-06 | 2025-05-14 | DGV | 01010312 | 106 | 27 |
| 11 | MONTE | 2024-07-01 | 2025-07-15 | DGV | 01010312 | 109 | 87 |
| 12 | MONTE | 2024-07-01 | 2025-07-15 | DGV | 01010312 | 109 | 87 |
For example, he can stay for 60 days (till 2025-03-10) in the Room with Id = 1 at Price 99, then in the Room with Id = 3 for 27 days at Price 106.
In total, Price will be (60 * 99) + (27 * 106) = 8,802
OR he can stay in the Room with Id = 5 all the 87 days, but with Price of 109 - in total it will be 87 * 109 = 9,483
We need to offer him all the possible options so that he can choose. Expected request result would be something like this
| HotelCode | RoomType | AccmdMenTypeCode | TotalPrice | OverlapDaysCount |
|---|---|---|---|---|
| MONTE | DGV | 01010003 | 8802 | 87 |
| MONTE | DGV | 01010003 | 9483 | 87 |
| MONTE | DGV | 01010312 | 9483 | 87 |
Here is the table with the data. I try very hard, but it doesn't work either. I'm sure that I need to change my approach.
Any ideas?
I think I've figured out the conditions. It's necessary that HotelCode, RoomType and the range (AccomodationPeriodBegin - AccomodationPeriodEnd) do not have duplicate AccmdMenTypeCode-s. Otherwise it makes confusion for users and difficulties when retrieving data from the DB.
Also, as @Dave.Gugg rightly pointed out, there were inaccuracies with the test data related to the date range.
I already fixed test data and changed requests to DB.
1 - detailed version with GroupIds
2 - simplier version
I'd appreciate your feedback