none
finding overlapping schedule in a table RRS feed

  • Question

  • Hi,

    I am new in SQL developing and have a question to find overlapping schedule based on Startdate/EventStatus/FunctionRoom/StartTime and End Time.

    <style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

    S.No EventStatus StartDate StartTime EndTime FunctionRoom Flag
    1 Accepted 2019-06-05 4:30 AM 9:00 PM Ball Room
    2 Accepted 2019-06-05 5:30 AM 8:00 PM Ball Room
    3 Tentative 2019-06-06 10:00 AM 5:00 PM Ball Room1
    4 Accepted 2019-06-07 3:00 PM 7:00 PM Ball Room2
    5 Accepted 2019-06-08 12:00 PM 5:00 PM Ball Room 3
    6 Tentative 2019-06-08 12:00 AM 6:00 AM Ball Room 4

    Need to fill Flag column Y if any overlap schedule find other wise N

    Hope i will get SQL statment here and i tried below statement and it's gave different result

    update a set Flag ='Y' from [Employee].[dbo].[EventTable]as a Inner Join [Employee].[dbo].[EventTable_Insert] as b
    On a.StartDate = b.Startdate and a.FunctionRoom =b.FunctionRoom and a.EventStatus = b.EventStatus
    where ((a.StartTime between b.StartTime and b.EndTime) and (a.EndTime between b.StartTime and b.EndTime) and (b.StartTime between a.StartTime and a.EndTime))

    Thanks 

    Venkat


    Regards Venkat

    Tuesday, August 20, 2019 11:54 AM

Answers

  • Without your actual data, it's hard to see what's happening..

    Please post a concise and complete script. Include table DDL preferably as table variable and sample data INSERT statements as a single, runnable T-SQL script.

    Use the <> button in the toolbar to embed your code. This increases readability and code could be easily copied.

    • Marked as answer by VENKAT YADAV Monday, September 2, 2019 3:39 AM
    Wednesday, August 28, 2019 10:51 AM

All replies

  • Well, start by posting a concise and complete example, include table DDL and sample data as INSERT statements as single, runnable T-SQL script. This makes helping you simpler.

    Then you should craft a SELECT first, which does the calulcus before writing an UPDATE. Detecting an overlap is pretty straight forward, just do it once with paper and pencil. An overlap is not (no overlap). And the condition for no overlap is trivial.

    E.g.

    DECLARE @Sample TABLE (
        EventID INT IDENTITY NOT NULL PRIMARY KEY ,
        EventStatus NVARCHAR(255) NOT NULL ,
        EventDate DATE NOT NULL ,
        EventStartTime TIME NOT NULL ,
        EventEndTime TIME NOT NULL ,
        RoomName NVARCHAR(255) NOT NULL ,
        OverlapBit BIT NULL
    );
    
    INSERT INTO @Sample ( EventStatus ,
                          EventDate ,
                          EventStartTime ,
                          EventEndTime ,
                          RoomName )
    VALUES ( 'Accepted', '2019-06-05', ' 4:30 AM', '9:00 PM', 'Ball Room' ) ,
           ( 'Accepted', '2019-06-05', ' 5:30 AM', '8:00 PM', 'Ball Room' ) ,
           ( 'Tentative', '2019-06-06', '10:00 AM', '5:00 PM', 'Ball Room1' ) ,
           ( 'Tentative', '2019-06-06', '2:00 PM', '6:00 PM', 'Ball Room1' ) ,
           ( 'Accepted', '2019-06-07', ' 3:00 PM', '7:00 PM', 'Ball Room2' ) ,
           ( 'Accepted', '2019-06-08', '12:00 PM', '5:00 PM', 'Ball Room3' ) ,
           ( 'Tentative', '2019-06-08', '12:00 AM', '6:00 AM', 'Ball Room4' );
    
    SELECT *
    FROM   @Sample O
           CROSS APPLY (   SELECT COUNT(*) AS OverlapCount
                           FROM   @Sample I
                           WHERE  I.EventID != O.EventID
                                  AND I.RoomName = O.RoomName
                                  AND I.EventDate = O.EventDate
                                  AND NOT (   I.EventStartTime > O.EventEndTime
                                              OR I.EventEndTime < O.EventStartTime )) Q;
    
    SELECT * ,
           IIF(
               EXISTS (   SELECT *
                          FROM   @Sample I
                          WHERE  I.EventID != O.EventID
                                 AND I.RoomName = O.RoomName
                                 AND I.EventDate = O.EventDate
                                 AND NOT (   I.EventStartTime > O.EventEndTime
                                             OR I.EventEndTime < O.EventStartTime )) ,
               1 ,
               0) AS OverlapExists
    FROM   @Sample O;
    
    UPDATE O
    SET    O.OverlapBit = IIF(
                              EXISTS (   SELECT *
                                         FROM   @Sample I
                                         WHERE  I.EventID != O.EventID
                                                AND I.RoomName = O.RoomName
                                                AND I.EventDate = O.EventDate
                                                AND NOT (   I.EventStartTime > O.EventEndTime
                                                            OR I.EventEndTime < O.EventStartTime )) ,
                              1 ,
                              0)
    FROM   @Sample O;
    
    SELECT *
    FROM   @Sample S;

    In many scenarios EXISTS() can be the faster solution in performance terms. So you should favor it over the COUNT(*) approach.

    p.s. for starters: get your copy of T-SQL Fundamentals from Itzik Ben-Gan





    • Edited by Stefan Hoffmann Tuesday, August 20, 2019 12:45 PM
    • Marked as answer by VENKAT YADAV Thursday, August 22, 2019 3:38 AM
    • Unmarked as answer by VENKAT YADAV Friday, August 23, 2019 9:35 AM
    Tuesday, August 20, 2019 12:39 PM
  • Try this too:

    update a

    set Flag = case when b.EventID is null then 'N' else 'Y' end

    from EventTable as a

    left Join EventTable as b

           on  a.EventID <> b.EventID

           and a.FunctionRoom = b.FunctionRoom

           and a.StartDate = b.Startdate

           and a.EventStatus = b.EventStatus -- ?

           and ( a.StartTime between b.StartTime and b.EndTime

              or b.StartTime between a.StartTime and a.EndTime )

     

    • Edited by Viorel_MVP Tuesday, August 20, 2019 1:57 PM
    Tuesday, August 20, 2019 1:52 PM
  • Thanks and not get expected result.

    Regards Venkat

    Thursday, August 22, 2019 12:00 AM
  • Thanks Stefan,

    It is working.. thanks for Help .


    Regards Venkat

    Thursday, August 22, 2019 3:38 AM
  • Cause the overlap criteria is incorrect.

    An overlap does not exists when End of Test range (et) is lesser than the Start of the Reference range (sr) or when the Start of the Test range (st) is greater then the End of the Reference range (er):

    et < sr OR er < st

    An overlap is the negation:

    NOT ( et < sr OR er < st )

    And optional equals signs depending on the boundaries (with or without).




    Thursday, August 22, 2019 12:00 PM
  • Hi Stefan,

    sorry to get back to you,

    i am getting below result after running above query , please see below screen shot ..

    Thanks .. :)

    


    Regards Venkat

    Friday, August 23, 2019 9:34 AM
  • Post a complete and concise script. Take mine as template. I cannot read anything important from the picture. Especially the source data..

    When I need to guess: remove the equals. Cause per definition an event must end before a new one can start.

    So an event should go from 2:00 pm to 3:59 pm, when precision is minutes. Displaying it as 2:00 pm - 4:00 pm is a formatting issue in the front-end or you do a DATEADD(MINUTE, 1, endDateColumn) in the SELECT list of the outer (final) query.

    p.s. another option could be using the equal on both sides of the range (interval).

    p.p.s. sketch out the cases on paper. It's all about whether the interval boundaries belong to the interval (closed interval) or not (open interval). 

    My original answer uses open intervals (left and right open). Here you need to look into your data model what you want to use. From your used sample data it seems like left open intervals. 

    Friday, August 23, 2019 8:47 PM
  • Hi Stefan,

    Please see below script and result.

    Script :

    SELECT *
    FROM   [Employee].[dbo].[CoveEventTable_Insert] O
           CROSS APPLY (   SELECT COUNT(*) AS OverlapCount
                           FROM   [Employee].[dbo].[CoveEventTable_Insert] I
                         WHERE  I.EventID != O.EventID
              AND I.EventStatus = O.EventStatus
      AND I.FunctionRoom = O.FunctionRoom
                                  AND I.StartDate = O.StartDate
                                  AND NOT (   DATEADD(MINUTE, 1, I.StartTime) > O.EndTime
                                              OR I.EndTime < DATEADD(MINUTE, 1, O.StartTime) )) Q;

    SELECT * ,
           IIF(
               EXISTS (   SELECT *
                          FROM   [Employee].[dbo].[CoveEventTable_Insert] I
                       WHERE  I.EventID != O.EventID
             AND I.EventStatus = O.EventStatus
                                 AND I.FunctionRoom = O.FunctionRoom
                                 AND I.StartDate = O.StartDate
                                 AND NOT (   DATEADD(MINUTE, 1, I.StartTime) > O.EndTime
                                             OR I.EndTime < DATEADD(MINUTE, 1, O.StartTime) )) ,
               1 ,
               0) AS OverlapExists
    FROM   [Employee].[dbo].[CoveEventTable_Insert] O;

    UPDATE O
    SET    O.Flag = IIF(
                              EXISTS (   SELECT *
                                         FROM   [Employee].[dbo].[CoveEventTable_Insert] I
                                       WHERE  I.EventID != O.EventID
                                                AND I.EventStatus = O.EventStatus
    AND I.FunctionRoom = O.FunctionRoom
                                                AND I.StartDate = O.StartDate
                                                AND NOT (   DATEADD(MINUTE, 1, I.StartTime) > O.EndTime
                                                            OR I.EndTime < DATEADD(MINUTE, 1, O.StartTime) )) ,
                              1 ,
                              0)
    FROM   [Employee].[dbo].[CoveEventTable_Insert] O;
    END

    Result:

    EventID StartDate StartTime EndTime FunctionRoom EventName GuestCount EventStatus Flag
    2 2018-5-6            4:30 AM                        8:00 PM                        Grand Metting Room2 GreenTamato Dinner 49 Definite            0
    3 2018-5-6            5:30 AM                        9:00 PM                        Grand Meeting Room2 GreenTamato Dinner 49 Definite            0


    Regards Venkat

    Tuesday, August 27, 2019 2:02 AM
  • Without your actual data, it's hard to see what's happening..

    Please post a concise and complete script. Include table DDL preferably as table variable and sample data INSERT statements as a single, runnable T-SQL script.

    Use the <> button in the toolbar to embed your code. This increases readability and code could be easily copied.

    • Marked as answer by VENKAT YADAV Monday, September 2, 2019 3:39 AM
    Wednesday, August 28, 2019 10:51 AM
  • This should do what you want:

    DECLARE @Sample TABLE (
        EventID INT IDENTITY NOT NULL PRIMARY KEY ,
        EventStatus NVARCHAR(255) NOT NULL ,
        EventDate DATE NOT NULL ,
        EventStartTime TIME NOT NULL ,
        EventEndTime TIME NOT NULL ,
        RoomName NVARCHAR(255) NOT NULL ,
        OverlapBit BIT NULL
    );
    
    INSERT INTO @Sample ( EventStatus ,
                          EventDate ,
                          EventStartTime ,
                          EventEndTime ,
                          RoomName )
    VALUES ( 'Accepted', '2019-06-05', ' 4:30 AM', '9:00 PM', 'Ball Room' ) ,
           ( 'Accepted', '2019-06-05', ' 5:30 AM', '8:00 PM', 'Ball Room' ) ,
           ( 'Tentative', '2019-06-06', '10:00 AM', '5:00 PM', 'Ball Room1' ) ,
           ( 'Tentative', '2019-06-06', '2:00 PM', '6:00 PM', 'Ball Room1' ) ,
           ( 'Accepted', '2019-06-07', ' 3:00 PM', '7:00 PM', 'Ball Room2' ) ,
           ( 'Accepted', '2019-06-08', '12:00 PM', '5:00 PM', 'Ball Room3' ) ,
           ( 'Tentative', '2019-06-08', '12:00 AM', '6:00 AM', 'Ball Room4' ),
    
    	   ( 'Definite', '2019-06-08', ' 2:00 AM', '4:00 PM', 'Golden BallRoom' ) ,
    	   ( 'Definite', '2019-06-08', ' 4:00 PM', '5:00 PM', 'Golden BallRoom' ) ,
    	   ( 'Definite', '2019-06-08', ' 5:00 PM', '6:00 PM', 'Golden BallRoom' ) ,
    	   ( 'Definite', '2019-06-08', ' 6:00 PM', '7:00 PM', 'Golden BallRoom' ) ,
    	   ( 'Definite', '2019-06-08', ' 7:00 PM', '7:30 PM', 'Golden BallRoom' ) ,
    	   ( 'Definite', '2019-06-08', ' 7:30 PM', '11:45 PM', 'Golden BallRoom' ),
    	   ( 'Definite', '2019-06-08', ' 8:30 PM', '9:00 PM', 'Golden BallRoom' ) 
    
    UPDATE e1
        SET OverlapBit = 1
    from @Sample e1
        INNER JOIN @Sample e2
        ON e2.EventID <> e1.EventID
        AND e2.EventStatus = e1.EventStatus
        and e2.EventDate = e1.EventDate
        and e2.RoomName = e1.RoomName
        and e2.EventStartTime < e1.EventEndTime
        and e1.EventStartTime < e2.EventEndTime
    
    
    SELECT *
    FROM @Sample
    

    Wednesday, August 28, 2019 2:48 PM
    Moderator