Building Working Hours Calendar

[#1]

I'm trying to build a table that has the daily working hours for each Facility, Shift and Date in my source table and I'm not sure how to go about doing it.  The source table has records with fields:  Facility, Shift, ScheduleIndicator, Date and Hours.

Here is how it looks:

The ScheduleIndicator field is of particular importance because if determines whether the record is a one-time record or a repeating record.  Here is the translation table for ScheduleIndicator:

If the ScheduleIndicator field is 0, then all records get the number of hours indicated.  If the ScheduleIndicator is 1-7, then each day of the week (Monday-Friday respectively) gets that number of hours and if the ScheduleIndicator is 9, then that specific date gets the number of hours indicated (ScheduleIndicator = 1-7 or 9 is an override value that overrides the default set in the ScheduleIndicator = 0 record ).  The Date for ScheduleIndicator values 0-7 does not matter and is always displayed as 1/1/1900 since these are repeating date records.

Because the ScheduleIndicator = 0 records sets the default number of hours, the load table will be missing some records where the value of the default record is accepted and not overridden (See the gap between 1/9 and 1/4 in the sample above)

What I need is to build a table with fields Facility, Shift, Date and Hours where the value of Hours is equal to the Hours value when ScheduleIndicator = 0 (for all dates) unless there is a record for the same Facility, Shift where ScheduleIndicator is 1-7, or 9 and then the hours will be equal to that value.  I'm guessing I need to read the max and min values of Date excluding 1/1/1900 for a date range.

I've attached a sample of the source file for one Facility