UFN_EVENTCONFLICT_LOCATIONCONFLICTSEXIST

        CREATE function dbo.UFN_EVENTCONFLICT_LOCATIONCONFLICTSEXIST
        (
            @START datetime,
            @END datetime,
            @LOCATIONS xml,
            @IGNORERECORDID uniqueidentifier = null,
            @IGNORESUBRECORDID uniqueidentifier = null
        )
        returns bit
        as
        begin 

            declare @LOCATIONSTABLE table (LOCATIONID uniqueidentifier)

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

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

            union all

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

            if (select count(*) from @LOCATIONSTABLE) = 0
                return 0;

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

            if exists 
            (
                select top(1) 1 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 
                    CONFLICTPROGRAMEVENTLOCATION.LOCATIONID = EVENT.EVENTLOCATIONID
                where 
                    (PROGRAM.ISACTIVE = 1 or EVENT.PROGRAMID is null) and
                    (EVENT.ID <> @IGNORERECORDID or (@IGNORERECORDID is null and EVENT.ID is not null)) and
                    (
                        (
                            @START >= EVENT.STARTDATETIME and 
                            @START < EVENT.ENDDATETIME
                        ) or
                        (
                            @END > EVENT.STARTDATETIME and 
                            @END <= EVENT.ENDDATETIME
                        ) or
                        (
                            @START < EVENT.STARTDATETIME and 
                            @END > EVENT.ENDDATETIME
                        )
                    )
            )
                return 1;

            -- Check the itinerary items to see if we have any conflicts

            if exists 
            (
                select top(1) 1 from dbo.ITINERARYITEM
                inner join @LOCATIONSTABLE as CONFLICTLOCATIONS on 
                    ITINERARYITEM.EVENTLOCATIONID = CONFLICTLOCATIONS.LOCATIONID
                inner join dbo.ITINERARY on
                    ITINERARY.ID = ITINERARYITEM.ITINERARYID
                inner join dbo.SALESORDER on
                    SALESORDER.ID = ITINERARY.RESERVATIONID
                where 
                    SALESORDER.STATUSCODE <> 5 and 
                    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) and --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

                    (
                        ITINERARYITEM.ITINERARYID <> @IGNORERECORDID or 
                        (@IGNORERECORDID is null and ITINERARYITEM.ITINERARYID is not null) or 
                        ITINERARYITEM.ID <> @IGNORESUBRECORDID or 
                        (@IGNORESUBRECORDID is null and ITINERARYITEM.ID is not null)
                    ) and 
                    (
                        (
                            @START >= ITINERARYITEM.STARTDATETIME and 
                            @START < ITINERARYITEM.ENDDATETIME
                        ) or
                        (
                            @END > ITINERARYITEM.STARTDATETIME and 
                            @END <= ITINERARYITEM.ENDDATETIME
                        ) or
                        (
                            @START < ITINERARYITEM.STARTDATETIME and 
                            @END > ITINERARYITEM.ENDDATETIME
                        )
                    )
            )
                return 1;

            return 0;
        end