I think the attached demo base does what you want. In pseudocode, it implements the following algorithm:
If (CheckIn and CheckOut are in the same month) {
.... CostOfLodging = Diff(CheckInCheckOut) * Cost[Month]
.... }
else {
.... CostOfLodging =
........ Diff(CheckIn,EndCheckInMonth) * Cost[CheckInMonth] +
........ (Diff(CheckOut,BeginningCheckOutMonth)+1) * Cost[CheckOutMonth]
.... }
I’ve made the assumption prices vary month by month; that is, that you don’t charge premium rates during certain weeks or weekends.
The solution as implemented makes use of two additional tables, Months and RateCard. Months contains 12 records per year, with three data-entry fields per record: Month number (1 - 12), number of days per month (it auto-corrects February, in case you forget a leap-year), and the per-night price charged during that month. Each month also needs to be linked to the appropriate RateCard record, of which there is one per year. In the demo, records 1 through 12 are linked to RateCard record ‘2017’; next year, you would create 12 new month records, also numbered 1 through 12, and link them to RateCard entry ‘2018’.
To use the base, you simply enter CheckIn and CheckOut dates plus the RateCard. The system extracts the appropriate per-day cost for CheckIn month and CheckOut month; if the months are the same, it calculates the number of elapsed days and multiplies that times the per-day cost. If CheckIn and CheckOut are different months, it extracts the number of days in CheckIn month, calculates the elapsed days in CheckIn month times the cost for that month, does the same for the number of days in CheckOut month (plus 1, to capture the day between CheckIn month and the first day of CheckOut month), and sums the two subtotals.
The base as implemented does not support 1) stays of greater than a month, 2) stays that straddle a RateCard (that is, from December to January), or 3) error-checking that CheckOut is later than CheckIn. All three could be added relatively easily.
I hope this gets you started, at least!
