hi all,
i have two tables:
Datetable
| Date | Isworkingday |
| 24-5-2024 | 1 |
| 25-05-2024 | 0 |
| 26-05-2024 | 0 |
| 27-05-2024 | 1 |
| 28-05-2024 | 1 |
| 29-05-2024 | 1 |
| 30-05-2024 | 1 |
| 31-05-2024 | 1 |
| 01-06-2024 | 0 |
And table: cases
| CaseId | Startdate | Enddate |
| C001 | 01-02-2024 | 30-04-2024 |
| C002 | 01-04-2024 | 10-04-2024 |
| C003 | 01-05-2024 | 27-06-2024 |
| C004 | 10-05-2024 | 30-06-2024 |
| C005 | 01-06-2024 | 10-07-2024 |
| C006 | 01-06-2024 | 03-06-2024 |
| | | |
I want to calculate the following results:
Number of active cases: # of cases with at least 1 day in the selected period (startdatum <= max of selected date, enddate >= min of selected date)
Number of active days: sum of all days between startdate case (or min of selected date if startdate < min selected date) and enddate (or max of selected date if enddate > max selected date), but only the days that have a 1 in column isworkingday in the date table
Average duration: number of active days / number of active cases
Do you have a suggestion? the cases table has over 500k rows.
Thanks in advance,
Regards, Frank
