Table: Staff_Target_Range_Day
Holds the target working hours for each day of the week that applies during the date range in Staff_Target_Range.
NOTE: primary key is non-standard, standard key column is a unique constraint for performance issues.
Columns
NameTypeConstraintsDescription
staff_target_range_day_KEYintNOT NULLAssigned by COMMON_dp_Allocate_Unique_Identifiers. Manufactured key for this table.
staff_target_range_KEYintNOT NULLLinks this record to the Staff_Target_Range record is is for.
week_daytinyint (3)NOT NULLThis is the day of the week as a number between 1 and 7 where the week starts on Monday. Monday = 1, Tuesday = 2, ..., Saturday = 6, Sunday = 7.
The expression to convert this value [based on @@DATEFIRST == 1] to the current @@DATEFIRST setting is: [week_day] = (((DATEPART(WEEKDAY, GETDATE()) - 1) + (@@DATEFIRST - 1)) % 7) + 1
target_hoursdecimal (4, 2)The number of hours expected to be worked on this day of the week. The value must be between 0.00 and 24.00.
Primary key
NameColumnsDescription
PK_Staff_Target_Range_Day$staff_target_range_KEY$week_daystaff_target_range_KEY, week_dayCLUSTERED
Unique constraints
NameColumnsDescription
UK_Staff_Target_Range_Day__staff_target_range_day_KEYstaff_target_range_day_KEYBackward compatible table key must be unique (instead of being a primary key as it normally is.)
Foreign keys
NameColumnsReferenced tableDescription
FK_Staff_Target_Range_Day__Staff_Target_Range__staff_target_range_KEYstaff_target_range_KEYStaff_Target_Range

Schema diagram