Hi,
I’m trying to build a small booking system. I have a pricing table with different rooms (for now there are only two rooms) based on the tourism season.
There are 6 different seasons with different pricing and min. stays (name of fields):
- price_high_season: date range(July 1 to August 31) (min. 7 nights)
- price_christmas_season: date range(December 24 to January 9) (min. 7 nights)
- price_easter_season: date range(April 16 to May 1) (min. 7 nights)
- price_shoulder_high_season: multiple date ranges(June 1 to June 30, September 1 to September 30), (min. 5 nights)
price_shoulder_low_season: multiple date range(March 1 to March 31, April 1 to April 15, May 2 to May 31, October 1 to October 31) (min. 4 nights)
price_low_season: multiple date ranges(January 10 to January 31, February 1 to February 28, November 1 to November 30, December 1 to December 23) (min. 3 nights)
When I start to input a booking from a form in a ‘booking’ table, I would like to calculate the total price based on the date range selected. Let’s say a booking starts and has 2 price_shoulder_low_season days and 3 price_high_season days. Each room type has different pricing too where the only manual input would be the price_low_season cell (the lowest value), the other season prices are calculated as an incremental percentage increase of the price_low_season.
If the booking ranges in a single season, that would be easy to calculate. However, occasionally, especially close to high seasons, most bookings might start and end in a different season.
Would anyone have an idea how to approach this?
PS: this is my first ever engagement with this community, apologies beforehand if I didn’t manage to clearly get my problem across. Any help would be appreciated.
