I have a table like this:
tblRate (PkRateId, UserId, Rate, Start, End, Active, CreatedDate, ModifiedDate)
Now, I want to create the following rules, but don't know how. Please help:
1. the table should reject entries (at the SQL Server level) when there is already a UserId with overlapping Start and End times.
For example, if the table contained:
1, User24, $20, 10/17/2006, 10/17/2008, y, 9/26/2006, 9/28/2007
2, user25, $25, 10/17/2006, null, y, 9/26/2006, 9/28/2007
I could not enter in a new record:
User24, $24, 10/24/2007, 10/25/2007, n, 9/30/2006, 9/30/2007
Because User24 already has an active rate. I should, however, be able to update where the PkRateId is 1 and change 10/17/2006 to 10/25/2007 if I wanted (or any other field for that matter).
Start Date is always before End Date
EndDate can only be Null for any rate if "Active" is "y"
There can only be one "y" in Active for any one user.
Btw: PkRateId is Primary Key with an identity specification.