I have been trying to get how to think about the following problem for months now. So I decided I need help to learn how to look at the problem correctly.
I built a "simple" database (let's say simpler than what I deal with in real life) from scratch with fake everything.
The "simple" question I am trying to answer is the following:
How many workdays each activity were effectively held
The rules are the following:
- If a teacher is absent, activity doesn't occur on that day.
- If an installation is unavailable, activity doesn't occur on that day (even if teacher is present)
- If a teacher is absent installation is deemed unavailable (cuz he has the keys)
- Installations unavailibility and Teachers absence may overlap
We only consider workdays. For this, a table exists which has for each unique date a workday number, dates that are holidays and weekends will repeat the last business day date workday number until the next business day date. (See file; much easier to understand by looking at it!)
Durations are calculated by substracting smallest workday number from biggest workday number.
My idea was to deduct the number of days of installation unavailibility and teacher absence from the Activity duration span. However, since some installation unavailibilities and teacher's absence overlap, I can't just add these together.
Find all records of Abs status (1 record is a span of days; start of status, end of status) for Teacher (TeacherAttendance) that teaches the activity
Find all records of Unavail. status (1 record is a span of days; start of status, end of status) for Installation (InstallationAvailability) that service the activity
I conceptualized what needs to be done, but I can't figure out how to do it in FileMaker so that I have a field called Activity Duration Net with the info for the activity in the activity table...
Do this for each activity:
(If teacher absence span record starts within Activity Span, list all WorkDays included in [teacher absence record] span.
if Teacher Abs StartDAte >= Activity StartDate and < Activity EndDate, List all WorkDay numbers between Teacher Abs StartDate and EndDate
(If installation unavailibility span record starts within Activity Span, list all Work Days included in [installation unavailibility record]span.
if Installation Unav. StartDate >= Activity StartDAte and < Activity EndDate List all WorkDay numbers between Installation Unav. StartDate and EndDAte
(now add together the list of workday numbers included in the Teacher absence span and the list of workday numbers incuded in the Installation unavailibility span. Remove duplicate number. Count the unique values: that is the number of days an activity could not be held)
Count (keepUniqueValues(Concatenate List TeachersAbsWorkDaysNumbers and List Installation Unavail. WorkDayNumbers)
Find workday number for Activity StartDate
Find workday number for Activity EndDate
End Number - Start Number = Activity Duration
Activity Duration - Number of Days that Activity could not be held = Activity Duration Net.
TestDaysDates.fmp12 (292 KB)