UFN_EVENTCONFLICT_STAFFRESOURCECONFLICTSEXIST

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

            declare @STAFFRESOURCESTABLE table
            (
                VOLUNTEERTYPEID uniqueidentifier,
                QUANTITYNEEDED int
            )

            insert into @STAFFRESOURCESTABLE
            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 


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


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

            -- Build a table to store resource counts

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

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

            insert into @STAFFRESOURCESUSED 
            (
                EVENTID, 
                VOLUNTEERTYPEID, 
                QUANTITYNEEDED,
                STARTDATETIME,
                ENDDATETIME
            )
            select 
                EVENT.ID,
                EVENTSTAFFRESOURCE.VOLUNTEERTYPEID,
                EVENTSTAFFRESOURCE.QUANTITYNEEDED,
                EVENT.STARTDATETIME as STARTDATETIME,
                EVENT.ENDDATETIME as ENDDATETIME
            from dbo.EVENT
            inner join dbo.EVENTSTAFFRESOURCE on 
                EVENT.ID = EVENTSTAFFRESOURCE.EVENTID and 
                EVENTSTAFFRESOURCE.FILLEDBYCODE = 0 -- Volunteer

            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) 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
                    )
                )

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

            insert into @STAFFRESOURCESUSED 
            (
                ITINERARYITEMID, 
                VOLUNTEERTYPEID, 
                QUANTITYNEEDED,
                STARTDATETIME,
                ENDDATETIME
            )
            select 
                ITINERARYITEM.ID,
                ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
                ITINERARYITEMSTAFFRESOURCE.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.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 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 @STAFFRESOURCESUSED 
            (
                ITINERARYID, 
                VOLUNTEERTYPEID, 
                QUANTITYNEEDED,
                STARTDATETIME,
                ENDDATETIME
            )
            select 
                ITINERARY.ID,
                ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID,
                ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
                ITINERARY.STARTDATETIME as STARTDATETIME,
                ITINERARY.ENDDATETIME as ENDDATETIME
            from dbo.ITINERARY
            inner join dbo.SALESORDER on
                ITINERARY.RESERVATIONID = SALESORDER.ID
            inner join dbo.ITINERARYSTAFFRESOURCE on ITINERARY.ID = ITINERARYSTAFFRESOURCE.ITINERARYID
            inner join @STAFFRESOURCESTABLE as EVENTSTAFFRESOURCECONFLICT on 
                EVENTSTAFFRESOURCECONFLICT.VOLUNTEERTYPEID = ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID
            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 ITINERARY.ID is not 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 @CURRENTUSEDSTAFFRESOURCES table
            (
                EVENTID uniqueidentifier,
                ITINERARYITEMID uniqueidentifier,
                ITINERARYID uniqueidentifier,
                VOLUNTEERTYPEID uniqueidentifier,
                QUANTITYNEEDED int
            )                

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

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

                union all

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

            open CONFLICTS_CURSOR;

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

            while @@FETCH_STATUS = 0 and @CONFLICTSEXIST = 0
            begin
                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


                    if exists (
                        select top 1 1 from @CURRENTUSEDSTAFFRESOURCES as STAFFRESOURCESUSED
                        where 
                            STAFFRESOURCESUSED.VOLUNTEERTYPEID in
                            (
                                select OVERALLOCATEDSTAFFRESOURCES.VOLUNTEERTYPEID 
                                from @CURRENTUSEDSTAFFRESOURCES as OVERALLOCATEDSTAFFRESOURCES
                                group by OVERALLOCATEDSTAFFRESOURCES.VOLUNTEERTYPEID
                                having 
                                (
                                    sum(OVERALLOCATEDSTAFFRESOURCES.QUANTITYNEEDED) + 
                                    (
                                        select top 1 STAFFRESOURCES.QUANTITYNEEDED 
                                        from @STAFFRESOURCESTABLE as STAFFRESOURCES 
                                        where STAFFRESOURCES.VOLUNTEERTYPEID = OVERALLOCATEDSTAFFRESOURCES.VOLUNTEERTYPEID
                                    ) > 
                                    (
                                        select VOLUNTEERTYPE.QUANTITY from dbo.VOLUNTEERTYPE 
                                        where VOLUNTEERTYPE.ID = OVERALLOCATEDSTAFFRESOURCES.VOLUNTEERTYPEID
                                    )
                                )
                            )
                    )
                        set @CONFLICTSEXIST = 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

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

            close CONFLICTS_CURSOR;
            deallocate CONFLICTS_CURSOR;

            return @CONFLICTSEXIST;
        end