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:
Yes, that's what I was talking about, and that is the serious problem with trying to represent an interval within a row in SQL Server.
You can do a function based check constraint that applies one of the available overlap detections, such as the simple (and SQL Server 2000 friendly). As mentioned, it's expensive. There may be better solutions for 2000, and I think there are for 2005, but I don't know them.
Essentially:
CROSS JOIN the table to itself (call them Outer and Inner)
WHERE Inner.UniqueRowKey <> Outer.UniqueRowKey (one row doesn't overlap with itself)
AND Inner.KeyThatShouldNotOverlap = Outer.KeyThatShouldNotOverlap (so we only look at row sets that, in combination, can overlap when they shouldn't)
AND Inner.effdate <= Outer.termdate (one starts before/equal to when the other ends)
AND Inner.termdate >= Outer.effdate (and that one also ends after/equal to when the other starts)
Add in as many fields in composite keys as you need.
If you don't have a truly unique row identifier... add one to the table, build one, concatenate fields to get one, or do some AND/OR within nested parenthesis as appropriate.