Table: Staff_Target_Range
The Staff_Target_Range table holds ranges of dates where different Staff targets (budgets) are effective. This table defines the date range, the actual target hours for each day of the week within this range is stored in the Staff_Target_Range_Day table. These ranges are specific to a staff and are coupled to a firm year.
Unlike tables defined in the past which would only store either [ending_date] or [upper_date_exclusive] this table redundantly stores both of these as well as [lower_date_inclusive] for the performance benefits and defines [upper_date_exclusive] as the value the other two are validated against.
Columns
NameTypeConstraintsDescription
staff_target_range_KEYintNOT NULLAssigned by COMMON_dp_Allocate_Unique_Identifiers. Manufactured primary key for this table.
staff_KEYintNOT NULLLinks this record to a particular staff.
lower_date_inclusivesmalldatetimeNOT NULLREDUNDANT: For performance we store the date that this range becomes effective. It can be computed from [upper_date_exclusive] and [firm_date_KEY].
ending_datesmalldatetimeNOT NULLREDUNDANT: For backwards compatibility and ease of display on reports and screens, this is 24 hours prior to the [upper_date_exclusive].
upper_date_exclusivesmalldatetimeNOT NULLThis is the important date - this is the date that the next range begins and therefore where this ends.
billable_percentdecimal (7, 6)NOT NULLThe billable target hours are calculated from the target hours specified in [Staff_Target_Range_Day] * this percentage.
billable_amountdecimal (17, 2)NOT NULLIf [billable_amount_is_overridden] is TRUE, this is the total billable amount to be spread across all target billable hours for the date range.
billable_amount_is_overriddenbitNOT NULLWhen TRUE, indicates that the value of [billable_amount] is used, otherwise a rate is computed from an associated [Staff_Target_Range_Rate] record.
Primary key
NameColumnsDescription
PK_Staff_Target_Range$staff_target_range_KEYstaff_target_range_KEYCLUSTERED
Unique constraints
NameColumnsDescription
UK_Staff_Target_Range__staff_KEY__upper_date_exclusivestaff_KEY, upper_date_exclusive
Indexes
NameColumnsDescription
IX_Staff_Target_Range$staff_KEY$lower_date_inclusive$upper_date_exclusivestaff_KEY, lower_date_inclusive, upper_date_exclusive
Foreign keys
NameColumnsReferenced tableDescription
FK_Staff_Target_Range__Staff__staff_KEYstaff_KEYStaff
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Staff_Target_Range_Day__Staff_Target_Range__staff_target_range_KEYstaff_target_range_KEYStaff_Target_Range_Day
FK_Staff_Target_Range_Rate__Staff_Target_Range__staff_target_range_KEYstaff_target_range_KEYStaff_Target_Range_Rate

Schema diagram