By: Aaron Bertrand | Comments (2) | Related: > Dates
Problem
I often see people struggle with logic related to datetime range and interval queries. A common example is trying to determine if two events overlap, either fully or partially. Given an event that happens, did another event happen to cross that window in any way?
There are six different logical scenarios where two events could potentially be deemed to overlap, and many of the queries I see out in the wild treat all six of these scenarios separately. Only four of those scenarios might/can overlap. And when you boil it down, all four scenarios can be captured in two conditions. In this tip I'm going to show the six scenarios, and why two conditions capture all four of the actual overlapping scenarios.
Solution
The six scenarios I mentioned above are best described using a diagram. Given an event, Event1, with start time S1 and end time E1, and a second event as input, Event2, with start time @S2 and end time @E2, the possibilities are as follows:
Treating Event1 as a window, the logic is determining whether Event2's window overlaps in any way. The first scenario represents the case where Event2 started and finished before Event1 started, so we can discard it, as this is not an overlap. The last scenario represents the case where Event2 started and finished after Event1 finished, so this is another we can discard. The remaining four scenarios – shaded in green – represent cases where Event2 started before or after Event1 started and Event2 finished before or after Event1 finished. For these scenarios, I often see four pairs of conditional logic, like:
WHERE (@S2 < S1 AND @E2 >= S1) -- partial overlap at beginning
OR (@S2 >= S1 AND @E2 <= E1) -- complete overlap
OR (@S2 > S1 AND @E2 < E1) -- complete containment
OR (@S2 <= E1 AND @E2 > E1) -- partial overlap at end
But we can express it simpler than that, because all of these scenarios share two facts in common: Event2 finished after Event1 started *and* Event2 started before Event1 ended. Again, represented on a diagram:
All that really matters is that Event2 ends at the same time or after the start of Event1 – it doesn't matter if it ends on the early boundary, inside, on the later boundary, or after. Similarly, Event2 needs to start before or when Event1 ends – it doesn't matter if it starts before Event2, on the early boundary, inside, or on the later boundary.
This simplifies the logic to:
WHERE (@E2 >= S1 AND @S2 <= E1)
Now, how can we use this logic? Let's say we have a 5-minute period of high CPU activity, and we know that several backups took place around the same time, but we're not sure if they overlap.
We can check this by using the following:
DECLARE @S2 datetime2(0) = '2018-12-21 13:51:21',
@E2 datetime2(0) = '2018-12-21 13:56:21';
SELECT * -- for brevity, not best practice!
FROM msdb.dbo.backupset
WHERE @E2 >= backup_start_date
AND @S2 <= backup_finish_date;
This should return all backups that either started during the CPU activity, ended during the CPU activity, or started before *and* ended after.
We can also do the inverse, and use this logic to ensure that an event does *not* take place inside the windows of existing events. Let's say we have a table full of training classes, and we want to book a new training class. We need to ensure that, before we create this new class, there aren't any existing classes that will conflict.
CREATE TABLE dbo.TrainingClasses
(
ClassID int,
StartDate date,
EndDate date
);
INSERT dbo.TrainingClasses(ClassID, StartDate, EndDate)
VALUES (1, '2019-01-07', '2019-01-10'),
(2, '2019-01-14', '2019-01-16'),
(3, '2019-01-17', '2019-01-18');
Now, I want to schedule a 3-day training class in January. I first try the 9th - 11th:
DECLARE @S2 date = '2019-01-09', @E2 date = '2019-01-11';
SELECT 'Ok'
WHERE NOT EXISTS
(
SELECT 1 FROM dbo.TrainingClasses
WHERE @E2 >= StartDate
AND @S2 <= EndDate
);
Result:
------------------
0 row(s) affected.
Let's try again for two weeks later:
DECLARE @S2 date = '2019-01-23', @E2 date = '2019-01-25';
SELECT 'Ok'
WHERE NOT EXISTS
(
SELECT 1 FROM dbo.TrainingClasses
WHERE @E2 >= StartDate
AND @S2 <= EndDate
);
Result:
----
Ok
I could change the logic around to avoid NOT EXISTS but, like DST and time zones, the fewer curveballs I throw myself, the better.
Summary
Solving for overlapping windows can be a challenging problem in any language. I hope the illustrations above help you see the problem space in a different way.
Next Steps
Read on for related tips and other resources:
- SQL Server DateTime Best Practices
- SQL Server Date Time Calculation Examples
- Handle Weeks that Overlap Years in a SQL Server Date Dimension
- SQL Servers Lag and Lead Functions to Help Identify Date Differences
- Creating a date dimension or calendar table in SQL Server
- All date-related tips
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
