USP_DATALIST_EVENTSWITHCONFLICTINGTIMINGS

Returns a list of all events that have conflicting start/end times with given event.
Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


            CREATE procedure dbo.USP_DATALIST_EVENTSWITHCONFLICTINGTIMINGS(@EVENTID uniqueidentifier)
            as
                set nocount on;

                declare @REGISTEREDEVENTSTARTDATETIME datetime
                declare @REGISTEREDEVENTENDDATETIME datetime

                select 
            @REGISTEREDEVENTSTARTDATETIME = STARTDATETIME, 
            @REGISTEREDEVENTENDDATETIME = ENDDATETIME 
        from 
            dbo.EVENT
        where 
            ID = @EVENTID

                select 
                    E.ID, 
                    E.NAME, 
                    E.STARTDATETIME, 
                    E.ENDDATETIME, 
                    BBNC.ID AS BBNCEVENTID
                from 
                    dbo.EVENT E 
                    inner join dbo.BBNCEVENTIDMAP BBNC on E.ID = BBNC.EVENTID
                where 
                    E.ISACTIVE = 1 
                    and BBNC.EVENTID <> @EVENTID
            and (
                      (    --registered event starts between conflicting event start/end times

                          DATEDIFF(mi, E.STARTDATETIME, @REGISTEREDEVENTSTARTDATETIME) > 0 and DATEDIFF(mi, E.ENDDATETIME, @REGISTEREDEVENTSTARTDATETIME) < 0
                      )
                      or
                      (    --registered event ends between conflicting event start/end times

                          DATEDIFF(mi, E.STARTDATETIME, @REGISTEREDEVENTENDDATETIME) > 0 and DATEDIFF(mi, E.ENDDATETIME, @REGISTEREDEVENTENDDATETIME) < 0
                      )
                      or
                      (    --conflicting event starts and ends between registered event start/end times

                          DATEDIFF(mi, @REGISTEREDEVENTSTARTDATETIME, E.STARTDATETIME) > 0 and DATEDIFF(mi, @REGISTEREDEVENTENDDATETIME, E.STARTDATETIME) < 0 and DATEDIFF(mi, @REGISTEREDEVENTSTARTDATETIME, E.ENDDATETIME) > 0 and DATEDIFF(mi, @REGISTEREDEVENTENDDATETIME, E.ENDDATETIME) < 0
                      )     
                      or
                      (    --registered event starts at the same time as conflicting event

                          DATEDIFF(mi, E.STARTDATETIME, @REGISTEREDEVENTSTARTDATETIME) = 0
                      )
            )