calculating days of a period overlapping another period of days

[tidymonkey81]

Good day everybody, this is my first post as I'm new to Access.

I am writing a booking system for a guest house business and in order for accurate pricing I need to be able to determine the rates for a booking period which may cover more than one rate-period. For instance, if a booking period starts in a low season and accrues 5 days before crossing into a high season and then accruing 3 days I need to bill for 5 low season-priced days and 3 high season-priced days. I have fields in the following tables

tblBookings.StartDate
tblBookings.EndDate


tblSeasons.SeasonStart
tblSeasons.SeasonEnd
tblSeasons.SeasonType

First, should I set this up differently?

If not, how do I go about adding columns to my query that counts the number of peak, off peak and (if you look at the picture attached you'll understand why) carnival days in a single booking period?

I started looking at the SWITCH function but it doesn't seem to do what I need it to do.

Thanks!

[tidymonkey81]

anyone with any hints?