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
Name | Type | Constraints | Description
staff_target_range_KEY | int | NOT NULL | Assigned by COMMON_dp_Allocate_Unique_Identifiers. Manufactured primary key for this table.
staff_KEY | int | NOT NULL | Links this record to a particular staff.
lower_date_inclusive | smalldatetime | NOT NULL | REDUNDANT: For performance we store the date that this range becomes effective. It can be computed from [upper_date_exclusive] and [firm_date_KEY].
ending_date | smalldatetime | NOT NULL | REDUNDANT: For backwards compatibility and ease of display on reports and screens, this is 24 hours prior to the [upper_date_exclusive].
upper_date_exclusive | smalldatetime | NOT NULL | This is the important date - this is the date that the next range begins and therefore where this ends.
billable_percent | decimal (7, 6) | NOT NULL | The billable target hours are calculated from the target hours specified in [Staff_Target_Range_Day] * this percentage.
billable_amount | decimal (17, 2) | NOT NULL | If [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_overridden | bit | NOT NULL | When 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
Name | Columns | Description
PK_Staff_Target_Range$staff_target_range_KEY | staff_target_range_KEY | CLUSTERED
Unique constraints
Name | Columns | Description
UK_Staff_Target_Range__staff_KEY__upper_date_exclusive | staff_KEY, upper_date_exclusive |
Indexes
Name | Columns | Description
IX_Staff_Target_Range$staff_KEY$lower_date_inclusive$upper_date_exclusive | staff_KEY, lower_date_inclusive, upper_date_exclusive |

Foreign keys
Name | Columns | Referenced table | Description
FK_Staff_Target_Range__Staff__staff_KEY | staff_KEY | Staff |

Incoming foreign keys
Name | Columns | Referencing table | Description
FK_Staff_Target_Range_Day__Staff_Target_Range__staff_target_range_KEY | staff_target_range_KEY | Staff_Target_Range_Day |
FK_Staff_Target_Range_Rate__Staff_Target_Range__staff_target_range_KEY | staff_target_range_KEY | Staff_Target_Range_Rate |