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 and the target hours for each day of the week within this range. 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 * 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.
billing_rate_type_KEYintLinks to a Billing_Rate_Type record. The staff must have this rate used for the entire time the range is applicable.
target_hours_sundaydecimal (4, 2)NOT NULLThe number of hours expected to be worked on this day of the week. The value must be between 0.00 and 24.00.
target_hours_mondaydecimal (4, 2)NOT NULLThe number of hours expected to be worked on this day of the week. The value must be between 0.00 and 24.00.
target_hours_tuesdaydecimal (4, 2)NOT NULLThe number of hours expected to be worked on this day of the week. The value must be between 0.00 and 24.00.
target_hours_wednesdaydecimal (4, 2)NOT NULLThe number of hours expected to be worked on this day of the week. The value must be between 0.00 and 24.00.
target_hours_thursdaydecimal (4, 2)NOT NULLThe number of hours expected to be worked on this day of the week. The value must be between 0.00 and 24.00.
target_hours_fridaydecimal (4, 2)NOT NULLThe number of hours expected to be worked on this day of the week. The value must be between 0.00 and 24.00.
target_hours_saturdaydecimal (4, 2)NOT NULLThe 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$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$billing_rate_type_KEYbilling_rate_type_KEY
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__Billing_Rate_Type__billing_rate_type_KEYbilling_rate_type_KEYBilling_Rate_Type
FK_Staff_Target_Range__Staff__staff_KEYstaff_KEYStaff

Schema diagram