Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using T-SQL to find events that overlap (or don't) in SQL Server


By:   |   Last Updated: 2018-12-26   |   Comments   |   Related Tips: More > 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:

6 ways 2 events can compare in terms of overlap

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:

Boiling it down to 2 actual criteria

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:



Last Updated: 2018-12-26


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools