UFN_EVENTCONFLICT_RESOURCECONFLICTSEXIST

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

            -- Take the resource xml passed in and generate a table of resources

            declare @RESOURCESTABLE table
            (
                RESOURCEID uniqueidentifier,
                QUANTITYNEEDED int
            )

            insert into @RESOURCESTABLE (RESOURCEID, QUANTITYNEEDED)
            select 
                T.c.value('(RESOURCEID)[1]','uniqueidentifier'),
                T.c.value('(QUANTITYNEEDED)[1]','int')
            from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)

            if @IGNORERECORDID is not null
            begin
                declare @VISITORCOUNT int
                set @VISITORCOUNT = isnull((
                                        select sum(ITINERARYATTENDEE.QUANTITY)
                                        from dbo.ITINERARYATTENDEE
                                        where ITINERARYID = @IGNORERECORDID
                                    ),0);
                update @RESOURCESTABLE set
                    QUANTITYNEEDED = dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(@VISITORCOUNT * RESOURCE.PERTICKETQUANTITY, RESOURCE.PERTICKETDIVISOR)
                from dbo.RESOURCE
                where
                    RESOURCE.ID = [@RESOURCESTABLE].RESOURCEID and
                    -- Special checks for Itinerary Edit, Reservation Add, Group Checkin

                    -- already set this up because of attendee quantity change

                    [@RESOURCESTABLE].QUANTITYNEEDED = 0 and 
                    RESOURCE.ISPERTICKETITEM = 1
            end

            insert into @RESOURCESTABLE (RESOURCEID, QUANTITYNEEDED)
            select 
                T.c.value('(RESOURCEID)[1]','uniqueidentifier'),
                T.c.value('(QUANTITYNEEDED)[1]','int')
            from @RESOURCES.nodes('/EVENTRESOURCES/ITEM') T(c)

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

            -- Build a table to store resource counts

            declare @RESOURCESUSED table
            (
                EVENTID uniqueidentifier,
                ITINERARYITEMID uniqueidentifier,
                ITINERARYID uniqueidentifier,
                RESOURCEID uniqueidentifier,
                QUANTITYNEEDED int,
                STARTDATETIME datetime,
                ENDDATETIME datetime
            )

            -- fill the table based on those events occurring at the same time        

            insert into @RESOURCESUSED
            (
                EVENTID, 
                RESOURCEID, 
                QUANTITYNEEDED,
                STARTDATETIME,
                ENDDATETIME
            )
            select 
                EVENT.ID as EVENTID,
                EVENTRESOURCE.RESOURCEID,
                EVENTRESOURCE.QUANTITYNEEDED,
                EVENT.STARTDATETIME as STARTDATETIME,
                EVENT.ENDDATETIME as ENDDATETIME
            from dbo.EVENT
            inner join dbo.EVENTRESOURCE on EVENT.ID = EVENTRESOURCE.EVENTID
            inner join dbo.RESOURCE on 
                EVENTRESOURCE.RESOURCEID = RESOURCE.ID and 
                RESOURCE.TYPECODE = 0 --A non-consumable rsource

            left outer join dbo.PROGRAM on 
                EVENT.PROGRAMID = PROGRAM.ID
            inner join @RESOURCESTABLE as EVENTRESOURCECONFLICT on 
                EVENTRESOURCECONFLICT.RESOURCEID = EVENTRESOURCE.RESOURCEID
            where 
                (PROGRAM.ISACTIVE = 1 or EVENT.PROGRAMID is null) and
                (EVENT.ID <> @IGNORERECORDID or @IGNORERECORDID is 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
                    )
                )

            -- fill the table based on those itinerary items occurring at the same time

            insert into @RESOURCESUSED
            (
                ITINERARYITEMID, 
                RESOURCEID, 
                QUANTITYNEEDED,
                STARTDATETIME,
                ENDDATETIME
            )
            select 
                ITINERARYITEM.ID as ITINERARYITEMID,
                ITINERARYITEMRESOURCE.RESOURCEID,
                case RESOURCE.ISPERTICKETITEM
                    when 0 then
                        ITINERARYITEMRESOURCE.QUANTITYNEEDED
                    else
                        dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(COALESCE((select sum(ITINERARYATTENDEE.QUANTITY) from dbo.ITINERARYATTENDEE where ITINERARYATTENDEE.ITINERARYID = ITINERARY.ID),0) * ITINERARYITEMRESOURCE.PERTICKETQUANTITY, ITINERARYITEMRESOURCE.PERTICKETDIVISOR)
                end as QUANTITYNEEDED,
                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
                ITINERARY.RESERVATIONID = SALESORDER.ID
            inner join dbo.ITINERARYITEMRESOURCE on 
                ITINERARYITEM.ID = ITINERARYITEMRESOURCE.ITINERARYITEMID
            inner join dbo.RESOURCE on 
                ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID and 
                RESOURCE.TYPECODE = 0 --A non-consumable resource

            inner join @RESOURCESTABLE as EVENTRESOURCECONFLICT on 
                EVENTRESOURCECONFLICT.RESOURCEID = ITINERARYITEMRESOURCE.RESOURCEID
            where  
                SALESORDER.STATUSCODE <> 5 and -- Make sure the reservation is not cancelled, cancelled resources do not count against total

                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.ID <> @IGNORESUBRECORDID or @IGNORESUBRECORDID is null) or 
                    (ITINERARYITEM.ITINERARYID <> @IGNORERECORDID or @IGNORERECORDID is 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
                    )
                )

            -- fill the table based on those itineraries occurring at the same time

            insert into @RESOURCESUSED
            (
                ITINERARYID, 
                RESOURCEID, 
                QUANTITYNEEDED,
                STARTDATETIME,
                ENDDATETIME
            )
            select 
                ITINERARY.ID as ITINERARYID,
                ITINERARYRESOURCE.RESOURCEID,
                case RESOURCE.ISPERTICKETITEM
                    when 0 then
                        ITINERARYRESOURCE.QUANTITYNEEDED
                    else
                        dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(COALESCE((select sum(ITINERARYATTENDEE.QUANTITY) from dbo.ITINERARYATTENDEE where ITINERARYATTENDEE.ITINERARYID = ITINERARYRESOURCE.ITINERARYID),0) * ITINERARYRESOURCE.PERTICKETQUANTITY, ITINERARYRESOURCE.PERTICKETDIVISOR)
                end as QUANTITYNEEDED,
                ITINERARY.STARTDATETIME as STARTDATETIME,
                ITINERARY.ENDDATETIME as ENDDATETIME
            from dbo.ITINERARY
            inner join dbo.SALESORDER on
                ITINERARY.RESERVATIONID = SALESORDER.ID
            inner join dbo.ITINERARYRESOURCE on ITINERARY.ID = ITINERARYRESOURCE.ITINERARYID
            inner join dbo.RESOURCE on 
                ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID and 
                RESOURCE.TYPECODE = 0 --A non-consumable resource

            inner join @RESOURCESTABLE as EVENTRESOURCECONFLICT on 
                EVENTRESOURCECONFLICT.RESOURCEID = ITINERARYRESOURCE.RESOURCEID
            where   
                SALESORDER.STATUSCODE <> 5 and -- Make sure the reservation is not cancelled, cancelled resources do not count against total

                (
                    @IGNORESUBRECORDID is not null or 
                    (ITINERARY.ID <> @IGNORERECORDID or @IGNORERECORDID is null)
                ) and
                (
                    (@START >= ITINERARY.STARTDATETIME and @START < ITINERARY.ENDDATETIME) or
                    (@END > ITINERARY.STARTDATETIME and @END <= ITINERARY.ENDDATETIME) or
                    (@START < ITINERARY.STARTDATETIME and @END > ITINERARY.ENDDATETIME)
                )

            declare @CONFLICTSEXIST bit = 0;

            declare @CURRENTUSEDRESOURCES table
            (
                EVENTID uniqueidentifier,
                ITINERARYITEMID uniqueidentifier,
                ITINERARYID uniqueidentifier,
                RESOURCEID uniqueidentifier,
                QUANTITYNEEDED int
            )                

            declare @THISITINERARYID uniqueidentifier
            declare @THISITINERARYITEMID uniqueidentifier
            declare @THISEVENTID uniqueidentifier
            declare @THISRESOURCEID uniqueidentifier
            declare @THISQUANTITYNEEDED int
            declare @THISTIME datetime
            declare @THISISSTARTTIME bit            

            declare CONFLICTS_CURSOR cursor local fast_forward for
            select
                ITINERARYID,
                ITINERARYITEMID,
                EVENTID,
                RESOURCEID,
                QUANTITYNEEDED,
                TIME,
                ISSTARTTIME
            from
            (
                select
                    ITINERARYID,
                    ITINERARYITEMID,
                    EVENTID,
                    RESOURCEID,
                    QUANTITYNEEDED,
                    STARTDATETIME as TIME,
                    1 as ISSTARTTIME
                from @RESOURCESUSED

                union all

                select
                    ITINERARYID,
                    ITINERARYITEMID,
                    EVENTID,
                    RESOURCEID,
                    QUANTITYNEEDED,
                    ENDDATETIME as TIME,
                    0 as ISSTARTTIME
                from @RESOURCESUSED
            ) as T
            order by T.TIME, T.ISSTARTTIME

            open CONFLICTS_CURSOR;

            fetch next from CONFLICTS_CURSOR
            into 
                @THISITINERARYID, 
                @THISITINERARYITEMID, 
                @THISEVENTID, 
                @THISRESOURCEID,
                @THISQUANTITYNEEDED,
                @THISTIME,
                @THISISSTARTTIME;

            while @@FETCH_STATUS = 0 and @CONFLICTSEXIST = 0
            begin
                if @THISISSTARTTIME = 1
                -- PUSH

                begin
                    insert into @CURRENTUSEDRESOURCES
                    (
                        EVENTID,
                        ITINERARYITEMID,
                        ITINERARYID,
                        RESOURCEID,
                        QUANTITYNEEDED
                    )
                    values
                    (
                        @THISEVENTID,
                        @THISITINERARYITEMID,
                        @THISITINERARYID,
                        @THISRESOURCEID,
                        @THISQUANTITYNEEDED
                    )


                    -- If in conflict, dump @CURRENTUSEDRESOURCES into @CONFLICTS

                    -- Not inserting duplicates


                    if exists (
                        select top 1 1 from @CURRENTUSEDRESOURCES as RESOURCESUSED
                        where 
                            RESOURCESUSED.RESOURCEID in
                            (
                                select OVERALLOCATEDRESOURCES.RESOURCEID
                                from @CURRENTUSEDRESOURCES as OVERALLOCATEDRESOURCES
                                group by OVERALLOCATEDRESOURCES.RESOURCEID
                                having 
                                (
                                    sum(OVERALLOCATEDRESOURCES.QUANTITYNEEDED) + 
                                    (
                                        select top 1 RESOURCES.QUANTITYNEEDED 
                                        from @RESOURCESTABLE as RESOURCES 
                                        where RESOURCES.RESOURCEID = OVERALLOCATEDRESOURCES.RESOURCEID
                                    ) > 
                                    (
                                        select RESOURCE.QUANTITY 
                                        from RESOURCE 
                                        where RESOURCE.ID = OVERALLOCATEDRESOURCES.RESOURCEID
                                    )
                                )
                            )
                    )
                        set @CONFLICTSEXIST = 1;

                end
                else
                -- POP

                -- Do not need to check for conflicts as we are actually decrementing the count

                begin
                    delete from @CURRENTUSEDRESOURCES
                    where
                    (
                        @THISEVENTID = [@CURRENTUSEDRESOURCES].EVENTID or
                        @THISITINERARYITEMID = [@CURRENTUSEDRESOURCES].ITINERARYITEMID or
                        @THISITINERARYID = [@CURRENTUSEDRESOURCES].ITINERARYID
                    ) and
                    @THISRESOURCEID = [@CURRENTUSEDRESOURCES].RESOURCEID
                end

                if @CONFLICTSEXIST = 0    
                    fetch next from CONFLICTS_CURSOR
                    into 
                        @THISITINERARYID, 
                        @THISITINERARYITEMID, 
                        @THISEVENTID, 
                        @THISRESOURCEID,
                        @THISQUANTITYNEEDED,
                        @THISTIME,
                        @THISISSTARTTIME;
            end

            close CONFLICTS_CURSOR;
            deallocate CONFLICTS_CURSOR;

            return @CONFLICTSEXIST;
        end