Have you ever seen a table where each row had a ‘start date’ and an ‘end date’? I’m sure most of us have seen one or created one.
These tables tend to pose a key problem (no pun intended) if the following business logic applies: “If two or more rows represent different date ranges for a single item, the ranges cannot overlap.” In other words, the date range must be unique from the start date until the end date for any particular item represented by the table.
Note: examples in this post were creating using MS Sql Server and SSMS.
Let’s use the example of a retail promotion, say 50% off donuts:
Figure 1 – dbo.Promotion
Figure 2 – dbo.PromotionCampaign
The difficulty lies in preventing another row from being inserted for the same promotion with an overlapping date range. For example, the additional rows in figure 3 all overlap. The start date for one is in the middle of the existing promotion, the end date for another is in the middle, the entire range of another is contained within the range of the original, and yet another spans beyond both the beginning and end of the original.
Figure 3- dbo.PromotionCampaign with overlapping date ranges
If we needed to find all valid donut promotions for January 4th, then we would get five results for this one promotion. This would be especially bad if our point of sale system actually applied the promotion 5x over!
I’ve seen several attempted solutions to this problem. Sometimes the developer will attempt to make the date range part of the key (for example, [PromotionId] and [Promotion Start]). Or they may use a trigger. Most often, they will try to control how the table is accessed by creating a stored procedure that handles the issues before returning data.
Unfortunately, these solutions fall short. Including the date fields as part of the key or a unique index will not prevent overlapping ranges (in fact, all of the examples above would still be valid even if one or both of the date fields were included in a unique constraint).
Forcing data access through a stored procedure to resolve overlapping ranges makes data access cumbersome (ever tried to join to a sproc?), and can become a performance hurdle. And if anything attempts to access the table directly, then the logic in the stored procedure must be duplicated and then maintained in multiple places.
What would be ideal is a table constraint that can check for overlapping dates and prevent them from being inserted in the first place. But how to devise a bit of SQL code that can test for overlapping dates (that doesn’t involve cursors, preferably)?
There is a way to test this in a simple SQL query. It’s easiest to understand if we diagram the issue first. Here we can see the original row (in red), and all the possible types of overlapping ranges:
Two things that can be noted from this diagram:
- For every possible overlapping range, the starting date is less than or equal to the end date of the original.
- For every possible overlapping range, the ending date is greater than or equal to the start date of the original.
Both of these are true for any overlapping range.
Using this logic, we can create a script that will test if a potential date range would overlap any existing ranges.
declare @promotionStart datetime = '1/1/2015 00:00:00', @promotionEnd datetime ='1/7/2015 23:59:59'; select * from dbo.PromotionCampaign pc where pc.PromotionId = 1 and @promotionStart <= pc.PromotionEnd and pc.PromotionStart <= @promotionEnd;
So now we just need to take this a step further, and attach this logic to the table somehow. Some people might want to put it in a trigger, and you can, but I think it is a bit cleaner to make it a check constraint. This will be a two-step process. First, encapsulate the logic in a UDF:
create function dbo.ValidatePromotionCampaignDates ( @promotionId int, @promotionStart datetime, @promotionEnd datetime ) returns bit as begin declare @Valid bit = 1; if exists( select * from dbo.PromotionCampaign pc where pc.PromotionId = @promotionId and @promotionStart <= pc.PromotionEnd and pc.PromotionStart <= @promotionEnd ) set @Valid = 0; return @Valid; end
Then, utilize the UDF in a check constraint on the table:
alter table dbo.PromotionCampaign with nocheck add constraint CK_PromotionCampaign_ValidateDates check (dbo.ValidatePromotionCampaignDates(PromotionId, PromotionStart, PromotionEnd) = 1);
Now, if you attempt to add another date range for a promotion that overlaps an existing range for that promotion, you will get a failure message, which is the desired behavior:
The INSERT statement conflicted with the CHECK constraint "CK_PromotionCampaign_ValidateDates". The conflict occurred in database "MHTEST", table "dbo.PromotionCampaign". The statement has been terminated.
And there you have it!