UFN_CONFLICTCHECK_GETRESOURCECONFLICTSFORMULTIPLETIMES

        CREATE function dbo.UFN_CONFLICTCHECK_GETRESOURCECONFLICTSFORMULTIPLETIMES
        (
            @EVENTDATES xml,
            @RESOURCES 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)

            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)

            -- Don't worry about per ticket resources on events

            -- Or consumable resources

            delete from @RESOURCESTABLE 
            where 
                QUANTITYNEEDED = 0 or
                RESOURCEID in
                (
                    select ID 
                    from dbo.RESOURCE
                    where RESOURCE.TYPECODE <> 0
                )

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

            -- Build a table to store resource counts

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

            insert into @RESOURCESUSED
            (
                SUPEREVENTID, 
                EVENTID,
                ITINERARYID,
                ITINERARYITEMID,
                RESOURCEID, 
                QUANTITYNEEDED,
                STARTDATETIME,
                ENDDATETIME
            )
            select 
                EVENTDATES.EVENTID as SUPEREVENTID,
                RESOURCES.EVENTID,
                RESOURCES.ITINERARYID,
                RESOURCES.ITINERARYITEMID,
                RESOURCES.RESOURCEID,
                RESOURCES.QUANTITYNEEDED,
                RESOURCES.STARTDATETIME,
                RESOURCES.ENDDATETIME
            from
            (
                select
                    EVENT.ID as EVENTID,
                    null as ITINERARYID,
                    null as ITINERARYITEMID,
                    EVENTRESOURCE.RESOURCEID as RESOURCEID,
                    EVENTRESOURCE.QUANTITYNEEDED as QUANTITYNEEDED,
                    EVENT.STARTDATETIME as STARTDATETIME,
                    EVENT.ENDDATETIME as ENDDATETIME
                from dbo.EVENT
                inner join dbo.EVENTRESOURCE on EVENT.ID = EVENTRESOURCE.EVENTID
                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)

                union all

                --  itinerary items


                select 
                    null as EVENTID,
                    null as ITINERARYID,
                    ITINERARYITEM.ID as ITINERARYITEMID,
                    ITINERARYITEMRESOURCE.RESOURCEID as 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 
                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 -- Make sure the itinerary item is showing as scheduled


                union all

                -- itineraries 

                select 
                    null as EVENTID,
                    ITINERARY.ID as ITINERARYID,
                    null as ITINERARYITEMID,
                    ITINERARYRESOURCE.RESOURCEID as 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
                inner join @RESOURCESTABLE as EVENTRESOURCECONFLICT on 
                    EVENTRESOURCECONFLICT.RESOURCEID = ITINERARYRESOURCE.RESOURCEID
                where
                    SALESORDER.STATUSCODE <> 5 -- Make sure the reservation is not cancelled, cancelled resources do not count against total

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

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

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

            declare @CURRENTEVENTID uniqueidentifier

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

                union all

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

            open CONFLICTS_CURSOR;

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

            while @@FETCH_STATUS = 0
            begin

                if @CURRENTEVENTID is null
                    set @CURRENTEVENTID = @THISSUPEREVENTID

                -- Reset current event if we are switching events, this is because we are going through 

                -- multiple events on conflict checking, ordered by eventid

                if @CURRENTEVENTID <> @THISSUPEREVENTID
                begin
                    delete from @CURRENTUSEDRESOURCES
                    set @CURRENTEVENTID = @THISEVENTID
                end

                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


                    with RESOURCETOTAL_CTE as
                    (
                        select
                            RESOURCEID,
                            sum(QUANTITYNEEDED) as QUANTITYNEEDED,
                            count(RESOURCEID) as RESOURCECOUNT
                        from
                        (
                            select
                                RESOURCEID,
                                QUANTITYNEEDED
                            from @CURRENTUSEDRESOURCES

                            union all

                            select
                                RESOURCEID,
                                QUANTITYNEEDED
                            from @RESOURCESTABLE                        
                        ) as T
                        group by T.RESOURCEID
                    )                    
                    insert into @CONFLICTS
                    (
                        EVENTID
                    )
                    select 
                        @THISSUPEREVENTID
                    from @CURRENTUSEDRESOURCES as RESOURCESUSED
                    inner join RESOURCETOTAL_CTE RESOURCETOTAL on
                        RESOURCETOTAL.RESOURCEID = RESOURCESUSED.RESOURCEID
                    inner join dbo.RESOURCE on
                        RESOURCESUSED.RESOURCEID = RESOURCE.ID 
                    where 
                        not exists
                        (
                            select 1 from @CONFLICTS
                            where
                                [@CONFLICTS].EVENTID = @THISSUPEREVENTID
                        ) and
                        RESOURCETOTAL.QUANTITYNEEDED > RESOURCE.QUANTITY and
                        RESOURCETOTAL.RESOURCECOUNT > 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

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

            close CONFLICTS_CURSOR;
            deallocate CONFLICTS_CURSOR;

            return;
        end