Hi guys 'n gals, I'm having an issue wrapping my head around a check constraint that I need to set on a table in my database.
Table: OnCall
Columns: OnCall_PKey (identity), Person_Key, StartDate, EndDate
When a new record is entered, I need a check constraint to make sure that the person entered does not already exist in the table with an overlapping time period:
If in the new record, the start date or the end date fall between the start date and the end date for an existing record having the person key in the new record then the record fails the check.
Example:
One existing data row from my table:
1496, 06/12/2007, 12/12/2007
I try to add:
1496, 09/12/2007, 15/12/2007
The record fails because the new date range overlaps the existing record in the table. No person can have overlapping time periods, however, a person can have multiple time slots in the table, it's just that none of the time slots may overlap.
Any pointers, will be gratefully received.
Table: OnCall
Columns: OnCall_PKey (identity), Person_Key, StartDate, EndDate
When a new record is entered, I need a check constraint to make sure that the person entered does not already exist in the table with an overlapping time period:
If in the new record, the start date or the end date fall between the start date and the end date for an existing record having the person key in the new record then the record fails the check.
Example:
One existing data row from my table:
1496, 06/12/2007, 12/12/2007
I try to add:
1496, 09/12/2007, 15/12/2007
The record fails because the new date range overlaps the existing record in the table. No person can have overlapping time periods, however, a person can have multiple time slots in the table, it's just that none of the time slots may overlap.
Any pointers, will be gratefully received.
Comment