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

Posted byMenolPosted inDatabase-Concepts, General Computing, SQL ServerTags:, ,

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

    Reply

  2. 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

    Reply

  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

    Reply

Leave a comment