Prev: Nested query with combined key
Next: How to use scalar functions?
From: Dan Holmes on 17 Mar 2010 09:04
I have seen this posted somewhere but i can't find it. The following table is an attempt to model a contract and its
effective dates. I don't want them to overlap but they do not have to be contiguous.

Have i covered all the points of data integrity? I feel like i am missing something with regard to the
PreviousContractDate.


CREATE TABLE dbo.CoordinatedTripAgencyContractDates (
SharedAgencyID INT NOT NULL
CONSTRAINT FK_CoordinatedTripAgencyContractDates_SharedAgencyID FOREIGN KEY (SharedAgencyID)
REFERENCES CoordinatedTripAgencies (SharedAgencyID)
--contracts are allowed to have gaps
--this allows the data to remain in the table for historical purposes yet allow the
--ability for the agency to no longer be part of the coordination activities
, PreviousContractEndDate DATETIME NOT NULL
, ContractStartDate DATETIME NOT NULL
, ContractEndDate DATETIME NOT NULL
--prevent overlaps
, CONSTRAINT Valid_CoordinatedTripAgencyContractDates_ContractStartDate CHECK (PreviousContractEndDate < ContractStartDate)
--make sure the contract/agreement starts before it ends
, CONSTRAINT Valid_CoordinatedTripAgencyContractDates_ContractEndDate CHECK (ContractEndDate>=ContractStartDate)
, CONSTRAINT PK_CoordinatedTripAgencyContractDates PRIMARY KEY (SharedAgencyID, ContractStartDate)
);

thanks
danny
From: Plamen Ratchev on 17 Mar 2010 11:32
Here is an article by Alex Kuznetsov with details:
http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Nested query with combined key
Next: How to use scalar functions?