UFN_REGISTRATION_GETREGISTEREDEVENTSWITHCONFLICTINGTIMINGS

Returns a table of user registered events that conflict with given event timings
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN
@EVENTID	uniqueidentifier	IN
Definition

Copy


            CREATE function dbo.UFN_REGISTRATION_GETREGISTEREDEVENTSWITHCONFLICTINGTIMINGS
            (
                @CONSTITUENTID as uniqueidentifier,
                @EVENTID as uniqueidentifier
            )
            returns table
            as
            return
            (
                select
                    CONFLICTINGEVENT.ID,
                    CONFLICTINGEVENT.NAME
                from 
                    dbo.EVENT CONFLICTINGEVENT
                    inner join dbo.EVENT REGISTEREDEVENT on REGISTEREDEVENT.ID = @EVENTID
                    inner join dbo.REGISTRANT on REGISTRANT.EVENTID = CONFLICTINGEVENT.ID
                where
                    CONFLICTINGEVENT.ID <> @EVENTID
                    and REGISTRANT.CONSTITUENTID = @CONSTITUENTID
                    and REGISTRANT.WILLNOTATTEND = 0
                    and (
                              (    --registered event starts between conflicting event start/end times

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

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

                          DATEDIFF(mi, REGISTEREDEVENT.STARTDATETIME, CONFLICTINGEVENT.STARTDATETIME) > 0 and DATEDIFF(mi, REGISTEREDEVENT.ENDDATETIME, CONFLICTINGEVENT.STARTDATETIME) < 0 and DATEDIFF(mi, REGISTEREDEVENT.STARTDATETIME, CONFLICTINGEVENT.ENDDATETIME) > 0 and DATEDIFF(mi, REGISTEREDEVENT.ENDDATETIME, CONFLICTINGEVENT.ENDDATETIME) < 0
                      )
                      or
                      (    --registered event starts at the same time as conflicting event

                          DATEDIFF(mi, CONFLICTINGEVENT.STARTDATETIME, REGISTEREDEVENT.STARTDATETIME) = 0
                      )
                    )
            )