UFN_CONFLICTCHECK_GETLOCATIONCONFLICTSFORMULTIPLETIMES

        CREATE function dbo.UFN_CONFLICTCHECK_GETLOCATIONCONFLICTSFORMULTIPLETIMES
        (
            @EVENTDATES xml,
            @LOCATIONS xml
        )
        returns @CONFLICTS TABLE
        (
            EVENTID uniqueidentifier
        )
        as
        begin 

            declare @EVENTDATESTABLE table
            (
                EVENTID uniqueidentifier,
                STARTDATETIME datetime,
                ENDDATETIME datetime
            )


            insert into @EVENTDATESTABLE
            select
                T.c.value('(EVENTID)[1]','uniqueidentifier') as 'EVENTID',
                T.c.value('(STARTDATETIME)[1]','datetime') as 'STARTDATETIME',
                T.c.value('(ENDDATETIME)[1]','datetime') as 'ENDDATETIME'
            from @EVENTDATES.nodes('/EVENTDATES/ITEM') T(c)


            --Build a table of the locations based on the xml passed in

            declare @LOCATIONSTABLE table
            (
                LOCATIONID uniqueidentifier
            )


            insert into @LOCATIONSTABLE (LOCATIONID)
            select 
                T.c.value('(EVENTLOCATIONID)[1]','uniqueidentifier') 
            from @LOCATIONS.nodes('/LOCATIONS/ITEM') T(c)

            -- Check the events to see if we have any conflicts

            insert into @CONFLICTS(EVENTID)
            select distinct
                EVENTDATES.EVENTID 
            from
            (
                select 
                    EVENT.ID as ID,
                    EVENT.STARTDATETIME as STARTDATETIME,
                    EVENT.ENDDATETIME as ENDDATETIME
                from dbo.EVENT
                left outer join dbo.PROGRAMEVENTLOCATION on 
                    EVENT.ID = PROGRAMEVENTLOCATION.EVENTID
                left outer join dbo.PROGRAM    on 
                    EVENT.PROGRAMID = PROGRAM.ID
                inner join @LOCATIONSTABLE as CONFLICTPROGRAMEVENTLOCATION on 
                    CONFLICTPROGRAMEVENTLOCATION.LOCATIONID = PROGRAMEVENTLOCATION.EVENTLOCATIONID or 
                    EVENT.EVENTLOCATIONID = CONFLICTPROGRAMEVENTLOCATION.LOCATIONID
                where 
                    (PROGRAM.ISACTIVE = 1 or EVENT.PROGRAMID is null) 

                union all

                select
                    ITINERARYITEM.ID as ID, 
                    ITINERARYITEM.STARTDATETIME as STARTDATETIME,
                    ITINERARYITEM.ENDDATETIME as ENDDATETIME
                from dbo.ITINERARYITEM
                inner join dbo.ITINERARY on 
                    ITINERARYITEM.ITINERARYID = ITINERARY.ID
                inner join dbo.SALESORDER on 
                    SALESORDER.ID = ITINERARY.RESERVATIONID
                inner join @LOCATIONSTABLE as CONFLICTLOCATIONS on 
                    ITINERARYITEM.EVENTLOCATIONID = CONFLICTLOCATIONS.LOCATIONID
                where 
                    SALESORDER.STATUSCODE <> 5 and  -- No cancelled reservation locations

                    ITINERARYITEM.INVALIDREASONCODE = 0 and -- Make sure that the itinerary item does not have an invalid reason

                    ITINERARYITEM.ITEMTYPECODE <> 3 and -- Make sure the itinerary item is showing as scheduled

                    (
                        ITINERARYITEM.ITEMTYPECODE <> 2 or ITINERARYITEM.BLOCKEVENT = 1 --Make sure the item is either not a custom item, if it is a custom item only count it if the location is marked busy

                    )
            ) as LOCATIONS
            inner join @EVENTDATESTABLE as EVENTDATES on
            (
                (EVENTDATES.STARTDATETIME >= LOCATIONS.STARTDATETIME and EVENTDATES.STARTDATETIME < LOCATIONS.ENDDATETIME) or
                (EVENTDATES.ENDDATETIME > LOCATIONS.STARTDATETIME and EVENTDATES.ENDDATETIME <= LOCATIONS.ENDDATETIME) or
                (EVENTDATES.STARTDATETIME < LOCATIONS.STARTDATETIME and EVENTDATES.ENDDATETIME > LOCATIONS.ENDDATETIME)
            )


            return;
        end