Returning Total Booking Price based on Seasonal Pricing

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.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.