Average x function combined with date table

hi all,

i have two tables:

Datetable

And table: cases

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