UFN_CONFLICTCHECK_GETSTAFFRESOURCECONFLICTSFORMULTIPLETIMES

        CREATE function dbo.UFN_CONFLICTCHECK_GETSTAFFRESOURCECONFLICTSFORMULTIPLETIMES
        (
            @EVENTDATES xml,
            @STAFFRESOURCES 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 @STAFFRESOURCESTABLE table
            (
                VOLUNTEERTYPEID uniqueidentifier,
                QUANTITYNEEDED int
            )

            insert into @STAFFRESOURCESTABLE
            (VOLUNTEERTYPEID, QUANTITYNEEDED)
            select 
                T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier'),
                T.c.value('(QUANTITYNEEDED)[1]','int')
            from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
            where T.c.value('(FILLEDBYCODE)[1]','tinyint') = 0 /* Volunteer */

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

            -- Build a table to store resource counts

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

            insert into @STAFFRESOURCESUSED
            (
                SUPEREVENTID, 
                EVENTID,
                ITINERARYID,
                ITINERARYITEMID,
                VOLUNTEERTYPEID, 
                QUANTITYNEEDED,
                STARTDATETIME,
                ENDDATETIME
            )
            select 
                EVENTDATES.EVENTID as SUPEREVENTID,
                STAFFRESOURCES.EVENTID,
                STAFFRESOURCES.ITINERARYID,
                STAFFRESOURCES.ITINERARYITEMID,
                STAFFRESOURCES.VOLUNTEERTYPEID,
                STAFFRESOURCES.QUANTITYNEEDED,
                STAFFRESOURCES.STARTDATETIME,
                STAFFRESOURCES.ENDDATETIME
            from
            (
                select
                    EVENT.ID as EVENTID,
                    null as ITINERARYID,
                    null as ITINERARYITEMID,
                    EVENTSTAFFRESOURCE.VOLUNTEERTYPEID as VOLUNTEERTYPEID,
                    EVENTSTAFFRESOURCE.QUANTITYNEEDED as QUANTITYNEEDED,
                    EVENT.STARTDATETIME as STARTDATETIME,
                    EVENT.ENDDATETIME as ENDDATETIME
                from dbo.EVENT
                inner join dbo.EVENTSTAFFRESOURCE on EVENT.ID = EVENTSTAFFRESOURCE.EVENTID
                left outer join dbo.PROGRAM on 
                    EVENT.PROGRAMID = PROGRAM.ID
                inner join @STAFFRESOURCESTABLE as EVENTSTAFFRESOURCECONFLICT on 
                    EVENTSTAFFRESOURCECONFLICT.VOLUNTEERTYPEID = EVENTSTAFFRESOURCE.VOLUNTEERTYPEID
                where 
                    (PROGRAM.ISACTIVE = 1 or EVENT.PROGRAMID is null)

                union all

                --  itinerary items


                select 
                    null as EVENTID,
                    null as ITINERARYID,
                    ITINERARYITEM.ID as ITINERARYITEMID,
                    ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID as VOLUNTEERTYPEID,
                    ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED 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.ITINERARYITEMSTAFFRESOURCE on 
                    ITINERARYITEM.ID = ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID
                inner join @STAFFRESOURCESTABLE as EVENTSTAFFRESOURCECONFLICT on 
                    EVENTSTAFFRESOURCECONFLICT.VOLUNTEERTYPEID = ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID
                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,
                    ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID as VOLUNTEERTYPEID,
                    ITINERARYSTAFFRESOURCE.QUANTITYNEEDED as QUANTITYNEEDED,
                    ITINERARY.STARTDATETIME as STARTDATETIME,
                    ITINERARY.ENDDATETIME as ENDDATETIME
                from dbo.ITINERARY
                inner join dbo.SALESORDER on
                    ITINERARY.RESERVATIONID = SALESORDER.ID
                inner join ITINERARYSTAFFRESOURCE on 
                    ITINERARY.ID = ITINERARYSTAFFRESOURCE.ITINERARYID
                inner join @STAFFRESOURCESTABLE as EVENTSTAFFRESOURCECONFLICT on 
                    EVENTSTAFFRESOURCECONFLICT.VOLUNTEERTYPEID = ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID
                where
                    SALESORDER.STATUSCODE <> 5 -- Make sure the reservation is not cancelled, cancelled resources do not count against total

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

            declare @CURRENTUSEDSTAFFRESOURCES table
            (
                SUPEREVENTID uniqueidentifier,
                EVENTID uniqueidentifier,
                ITINERARYITEMID uniqueidentifier,
                ITINERARYID uniqueidentifier,
                VOLUNTEERTYPEID uniqueidentifier,
                QUANTITYNEEDED int
            )

            declare @THISSUPEREVENTID uniqueidentifier
            declare @THISITINERARYID uniqueidentifier
            declare @THISITINERARYITEMID uniqueidentifier
            declare @THISEVENTID uniqueidentifier
            declare @THISVOLUNTEERTYPEID 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,
                VOLUNTEERTYPEID,
                QUANTITYNEEDED,
                TIME,
                ISSTARTTIME
            from
            (
                select
                    SUPEREVENTID,
                    ITINERARYID,
                    ITINERARYITEMID,
                    EVENTID,
                    VOLUNTEERTYPEID,
                    QUANTITYNEEDED,
                    STARTDATETIME as TIME,
                    1 as ISSTARTTIME
                from @STAFFRESOURCESUSED

                union all

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

            open CONFLICTS_CURSOR;

            fetch next from CONFLICTS_CURSOR
            into 
                @THISSUPEREVENTID,
                @THISITINERARYID, 
                @THISITINERARYITEMID, 
                @THISEVENTID, 
                @THISVOLUNTEERTYPEID,
                @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 @CURRENTUSEDSTAFFRESOURCES
                    set @CURRENTEVENTID = @THISEVENTID
                end

                if @THISISSTARTTIME = 1
                -- PUSH

                begin
                    insert into @CURRENTUSEDSTAFFRESOURCES
                    (
                        EVENTID,
                        ITINERARYITEMID,
                        ITINERARYID,
                        VOLUNTEERTYPEID,
                        QUANTITYNEEDED
                    )
                    values
                    (
                        @THISEVENTID,
                        @THISITINERARYITEMID,
                        @THISITINERARYID,
                        @THISVOLUNTEERTYPEID,
                        @THISQUANTITYNEEDED
                    );

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

                    -- Not inserting duplicates


                    with STAFFRESOURCETOTAL_CTE as
                    (
                        select
                            VOLUNTEERTYPEID,
                            sum(QUANTITYNEEDED) as QUANTITYNEEDED,
                            count(VOLUNTEERTYPEID) as STAFFRESOURCECOUNT
                        from
                        (
                            select
                                VOLUNTEERTYPEID,
                                QUANTITYNEEDED
                            from @CURRENTUSEDSTAFFRESOURCES

                            union all

                            select
                                VOLUNTEERTYPEID,
                                QUANTITYNEEDED
                            from @STAFFRESOURCESTABLE
                        ) as T
                        group by T.VOLUNTEERTYPEID
                    )                    
                    insert into @CONFLICTS
                    (
                        EVENTID
                    )
                    select 
                        @THISSUPEREVENTID
                    from @CURRENTUSEDSTAFFRESOURCES as STAFFRESOURCESUSED
                    inner join STAFFRESOURCETOTAL_CTE STAFFRESOURCETOTAL on
                        STAFFRESOURCETOTAL.VOLUNTEERTYPEID = STAFFRESOURCESUSED.VOLUNTEERTYPEID
                    inner join dbo.VOLUNTEERTYPE on
                        STAFFRESOURCESUSED.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID 
                    where 
                        not exists
                        (
                            select 1 from @CONFLICTS
                            where
                                [@CONFLICTS].EVENTID = @THISSUPEREVENTID
                        ) and
                        STAFFRESOURCETOTAL.QUANTITYNEEDED > VOLUNTEERTYPE.QUANTITY and
                        STAFFRESOURCETOTAL.STAFFRESOURCECOUNT > 1

                end
                else
                -- POP

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

                begin
                    delete from @CURRENTUSEDSTAFFRESOURCES
                    where
                    (
                        @THISEVENTID = [@CURRENTUSEDSTAFFRESOURCES].EVENTID or
                        @THISITINERARYITEMID = [@CURRENTUSEDSTAFFRESOURCES].ITINERARYITEMID or
                        @THISITINERARYID = [@CURRENTUSEDSTAFFRESOURCES].ITINERARYID
                    ) and
                    @THISVOLUNTEERTYPEID = [@CURRENTUSEDSTAFFRESOURCES].VOLUNTEERTYPEID
                end

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

            close CONFLICTS_CURSOR;
            deallocate CONFLICTS_CURSOR;

            return;
        end