Jason Whitney (10/1/2009)
I have run into this before, and the article gives the first half of the solution. The second part is what I can't figure out; how to avoid overlapping time-frames.The supplied solution doesn't enforce data integrity for overlapping dates. For the illustration given it may not need to, but for many applications these date ranges must be mutually exclusive. For example a patient comes in to the hospital as an outpatient (O) and has a complication and gets admitted as an inpatient (I). The data is temporal, but the dates/times cannot overlap. How would I model this?
The only solution I have been able to come up with is a new data type for interval data. (Hopefully we will see this in the next version of SQL) I tried a CLR, but I was never able to get it to work quite right. My database has failed me! :crazy:
The safest solution is to use triggers. The next safest solution is to use stored procs. With triggers you would look for an overlap in the inserted table in comparison to the existing table. Something like:
If Exists(Select *
From dbo.PromotedProducts As PP
Join inserted As I
On I.PromotionId = PP.PromotionId
And I.ProductId = PP.ProductId
And I.Start <= PP.EndDate
And I.EndDate >= PP.StartDate )
Raiserror(...