ILT – How to find if two time frames overlap or collide?

14/06/2013

Let’s assume that time frame 1 starts from Start1 and ends on End1
(Time Frame 1 = Start1 – End1)
Similarly Time frame 2 = Start2 – End 2

Time Frame 1 and Frame 2 Over Lap IF:
Start1 <= End2 AND End1 >= Start2

Time Frames do not overlap IF:
End2 < Start1 OR Start2 > End1

Following is the code of an SQL stored procedure in which I used this concept:

```</pre>
USE [XXXX]
GO
/****** Object: UserDefinedFunction [scheduling].[funcCheckResourceAvailability] Script Date: 14/06/2013 14:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Menol
-- Create date: 14/06/2013
-- Description: Check availability of a resource
-- =============================================
ALTER FUNCTION [scheduling].[funcCheckResourceAvailability]
( @resourceID int, @datetimeStart datetime, @datetimeEnd datetime)
RETURNS bit
AS
BEGIN
-- Declare the return variable here
DECLARE @ret bit;
declare @tmp int;

SELECT @tmp = count(*) from [scheduling].[resourceAllocation] ra where ra.resourceID = @resourceID and (ra.[start] <= @datetimeEnd and ra.[end] >= @datetimeStart);

return case when @tmp > 0 then 0 else 1 end;

END
<pre>
```

Menol
ILT

4 thoughts on “ILT – How to find if two time frames overlap or collide?”

1. Hi Menol,

Your logic is correct with an assumption that Start1 Start2.

——S1———–S2–overlap–E1——–E2—–> Time

——S2———–S1–overlap–E2——–E1—–> Time

Hope you got the idea.

Like

2. Menol says:

Hi Sandun,

I think this logic works without any assumption.

I presume you meant that the logic doesn’t work for the second scenario.

Let’s consider your 2nd Scenario:

——S2———–S1–overlap–E2——–E1—–>

Formula says Start1 = Start2 and it evaluates to true for both of your scenarios
(is Scenario 2, for example, your S1 is = S2)

It’s much clearer when we use real times. Let’s consider following example.

—-S2——-S1—O/L——-E2—–E1—>
——10:00———–12:00–overlap–14:00——–18:00—–>

[Time frame1 started at S2 (10.00) and ended at E2 (14:00)]
similarly
[Time frame2 started at S1 (12:00) and ended at E1 (18:00)]

now the formula should validate to true IF these actually overlap
Formula :
Start1 = Start2

Assign your labels to the formula:
S2 = S1 — This logically evaluates to true

Now let’s assign the actual values to the formula to get a clearer picture
10 = 12 — This does evaluate to true.

It’s very difficult to evaluate because the scenario 2 has used S2 as the actual S1 so our eyes work against the logic 🙂

Thanks for the comment, and hey, you almost burnt my brain with confusing labels 😉

Menol

Like

3. Hi Menol,

I tried the explanation you’ve given and I’ve got the expected results. Sorry for the loss of your brain cells for my question 😉

Like

1. Menol says:

🙂 🙂 Thanks Sandun. It was good exercise for the rusting brain 🙂

Like