The company has commissioned me to analyze the occupancy percentage of a car rental company.
In the contracts table I have a row for each contract and with the data of: start date-time, end date-time and total contract hours.
contract start date end date total hours
255/893 31/12/2022 15:00 02/01/2023 15:00 48
100/236 25/01/2023 9:00 26/01/2023 9:00 24
100/564 07/01/2023 9:00 08/01/2023 8:00 23
100/999 30/01/2023 12:00 01/02/2023 14:30 50,5
A priori, it seems that calculating what the occupancy percentage would be should not be difficult.
The problem occurs when I use a date segmentation in my report.
I have a calendar table that relates to the contract table by the start date.
If in this segmentation framework January 2023 table will be filtered with contracts 100/236, 100/564 and 100/999.
In the case of contract 100/999 I have managed to calculate how many contract hours have been made in January 2023. From 30-01-2023 12:00 to 31-01-23 23:59.
I can then the occupancy percentage with respect to the total hours selected in the segmentation without major problem.
Unfortunately, there is one contract that escapes me. Contract 255/893 has a start date of 31-12-23 so it is outside the filter of segmentation. However, in January 2023, this contract has been in force from 01-01-23 00:00 until 02-01-2023 15:00
In summary, how could you calculate the hours that each contract has been in effect within the segmentation date range regardless of the start or end date of the contract?
