UFN_CONFLICTCHECK_CONFLICTSEXISTFORMULTIPLETIMES

For a large amount of non overlapping events with dates, return all events in conflict.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@EVENTS	xml	IN
@LOCATIONS	xml	IN
@RESOURCES	xml	IN
@STAFFRESOURCES	xml	IN
Definition

Copy


        CREATE function dbo.UFN_CONFLICTCHECK_CONFLICTSEXISTFORMULTIPLETIMES
        (
            @EVENTS xml,
            @LOCATIONS xml,
            @RESOURCES xml,
            @STAFFRESOURCES xml
        )
        returns @CONFLICTS table
        (
            EVENTID uniqueidentifier
        )
        as 
        begin

            declare @EVENTDATES xml

            set @EVENTDATES =
            (
                select
                    EVENTID as EVENTID,
                    dbo.UFN_DATE_ADDHOURMINUTE(dbo.UFN_DATE_GETEARLIESTTIME(STARTDATE), STARTTIME) as STARTDATETIME,
                    dbo.UFN_DATE_ADDHOURMINUTE(dbo.UFN_DATE_GETEARLIESTTIME(ENDDATE), ENDTIME) as ENDDATETIME
                from
                (
                    select
                        T.c.value('(EVENTID)[1]','uniqueidentifier') as 'EVENTID',
                        T.c.value('(STARTDATE)[1]','date') as 'STARTDATE',
                        T.c.value('(ENDDATE)[1]','date') as 'ENDDATE',
                        T.c.value('(STARTTIME)[1]','udt_HOURMINUTE') as 'STARTTIME',
                        T.c.value('(ENDTIME)[1]','udt_HOURMINUTE') as 'ENDTIME'
                    from @EVENTS.nodes('/EVENTS/ITEM') T(c)
                ) as T
                for xml raw('ITEM'),type,elements,root('EVENTDATES'),binary base64
            )

            insert into @CONFLICTS
            (
                EVENTID
            )
            select 
                EVENTID
            from
            (
                select
                    EVENTID
                from dbo.UFN_CONFLICTCHECK_GETLOCATIONCONFLICTSFORMULTIPLETIMES(@EVENTDATES,@LOCATIONS)

                union 

                select
                    EVENTID
                from dbo.UFN_CONFLICTCHECK_GETRESOURCECONFLICTSFORMULTIPLETIMES(@EVENTDATES,@RESOURCES)
                where
                    @RESOURCES is not null

                union 

                select
                    EVENTID
                from dbo.UFN_CONFLICTCHECK_GETSTAFFRESOURCECONFLICTSFORMULTIPLETIMES(@EVENTDATES,@STAFFRESOURCES)
                where
                    @STAFFRESOURCES is not null
            ) as T

            return;

        end