UFN_SALESORDERITEM_GETREGISTEREDEVENTSWITHCONFLICTINGTIMINGS

            CREATE function dbo.UFN_SALESORDERITEM_GETREGISTEREDEVENTSWITHCONFLICTINGTIMINGS
            (
                @CONSTITUENTID as uniqueidentifier,
                @EVENTID as uniqueidentifier
            )
            returns table
            as
            return
            (
                with xmlnamespaces('urn:blackbaud.RE7.XDATA' as XDATA)
                select distinct
                    CONFLICTINGEVENT.ID,
                    CONFLICTINGEVENT.NAME
                from dbo.SALESORDERITEM
                    inner join dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = SALESORDER.CONSTITUENTID
                    cross apply SALESORDERITEM.DATA.nodes('/ShelbyEventRegTran/XDATA:Registrations/XDATA:RegistrationInformation') REGISTRATIONINFORMATION(ELEMENT)
                    inner join dbo.BBNCEVENTIDMAP on BBNCEVENTIDMAP.ID = REGISTRATIONINFORMATION.ELEMENT.value('(XDATA:Event/XDATA:BackOfficeID)[1]', 'int')
                    inner join dbo.EVENT CONFLICTINGEVENT on CONFLICTINGEVENT.ID = BBNCEVENTIDMAP.EVENTID
                    inner join dbo.EVENT REGISTEREDEVENT on REGISTEREDEVENT.ID = @EVENTID
                where SALESORDER.CONSTITUENTID = @CONSTITUENTID
                    and SALESORDER.SALESMETHODTYPECODE = 2
                    and SALESORDERITEM.TYPECODE = 6
                    and CONFLICTINGEVENT.ID <> @EVENTID
                    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
                      )
                    )
                    and exists (
                              select 1
                              from REGISTRATIONINFORMATION.ELEMENT.nodes('XDATA:Fees/XDATA:RegistrationFeeInformation/XDATA:Guests/XDATA:RegistrantInformation') REGISTRANTINFORMATION(ELEMENT)
                              where REGISTRANTINFORMATION.ELEMENT.value('substring((XDATA:Name)[1], 1, 154)', 'nvarchar(154)') = CONSTITUENT.NAME
                    )
            )