mssqltips logo

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

By:   |   Updated: 2018-12-26   |   Comments (2)   |   Related: 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 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 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.





Tuesday, September 24, 2019 - 11:17:43 AM - Aaron Bertrand Back To Top

Your code is more complex but it is the exact same logic as mine. Try this out for all 5 existing reservations:

CREATE TABLE #res(AirplaneID int, ResStart datetime, ResEnd datetime);

INSERT #res(AirplaneID, ResStart, ResEnd) VALUES
(1'20190924 10:00''20190924 11:00'), -- completely within -- returns 1
(2'20190924 08:00''20190924 09:30'), -- overlap begin -- returns 1
(3'20190924 09:30''20190924 12:30'), -- overlap end -- returns 1
(4'20190924 08:00''20190924 13:00'), -- completely surrounds -- returns 1
(5'20190924 13:00''20190924 14:30'); -- no overlap -- returns 0

DECLARE @AirplaneID  int = 1-- repeat for 2, 3, 4, 5
        @NewResStart datetime = '20190924 09:00'
        @NewResEnd   datetime = '20190924 12:00';

SELECT COUNT(*FROM #res 
WHERE AirplaneID = @AirplaneID
  AND -- mylogic:
  (@NewResEnd >= ResStart
  AND @NewResStart <= ResEnd);

SELECT COUNT(*FROM #res 
WHERE AirplaneID = @AirplaneID
AND -- your logic:
(
    (@NewResStart BETWEEN ResStart AND ResEnd)
    OR (@NewResEnd BETWEEN ResStart AND ResEnd)
    OR (ResStart  BETWEEN @NewResStart AND @NewResEnd)    
    OR (ResEnd  BETWEEN @NewResStart AND @NewResEnd)
);
GO
DROP TABLE #res;

Tuesday, September 24, 2019 - 10:36:21 AM - Dan DeFord Back To Top

This is really great and helped me with an issue I was struggling with - Thank you!

I see one small issue with your answer, though. In my use case, I'm writing an aircraft scheduling website using PHP and MySQL for the flying club I belong to. I want to ensure that a pilot can't schedule a specific aircraft at the same time as someone else is using it. It appears that if I were to use your solution as is, if someone had a plane reserved from 10am to 11am, and then I came along and tried to reserve the same plane from 9am to noon (e.g. my start and end times are outside of the window that the other pilot has it reserved) it would come back as 'OK'.

Here's what I ended up with. I opted to use a count rather than just 'OK' or not. It's easier in code to deal with an integer than text (e.g. if the integer is greater than 0, then a conflict exists - and it should never be more than 1):

SET @aircraft_id = '[AircraftTailNumber]';
SET @reservation_start  = '2019-09-22 09:00:00';
SET @reservation_end   = '2019-09-22 12:00:00';
SELECT COUNT(*) FROM reservations
WHERE [email protected]_id 
AND (

    (@reservation_start BETWEEN reservation_start AND reservation_end)     OR (@reservation_end BETWEEN reservation_start AND reservation_end)     OR (reservation_start BETWEEN @reservation_start AND @reservation_end)         OR (reservation_end BETWEEN @reservation_start AND @reservation_end) );

Using this strategy, if there is a conflict where the previous reservation is completely within or completely encompasses my desired reservation, it will return a 1. Or if my start or end time are within a preor reservation, it will also return with a 1.

Obviously, using BETWEEN may cause some issues if a reservation ends at the same moment another begins (it's inclusive), but I'll deal with that in code.

Anyway, thanks again for your help. It got me on the right path.



download

























get free sql tips

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