UFN_EVENTCONFLICT_GETSTAFFRESOURCESINCONFLICT

Get all of the staff resources that are in conflict within the supplied information.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@START	datetime	IN
@END	datetime	IN
@STAFFRESOURCES	xml	IN
@IGNORERECORDID	uniqueidentifier	IN
@IGNORESUBRECORDID	uniqueidentifier	IN
Definition

Copy


              CREATE function dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCESINCONFLICT
                (
                  @START datetime,
                  @END datetime,
                  @STAFFRESOURCES xml,
                  @IGNORERECORDID uniqueidentifier = null,
                  @IGNORESUBRECORDID uniqueidentifier = null
                )
              returns @CONFLICTS TABLE
                    (
                        VOLUNTEERTYPEID uniqueidentifier,
                        QUANTITYNEEDED int
                    )
              as
              begin 

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

                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 */

                insert into @STAFFRESOURCESTABLE
                    (VOLUNTEERTYPEID, QUANTITYNEEDED)
                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 */

                -- Use the detail version with distinct select to get only the events, itineraries, and itinerary items.

                insert into @CONFLICTS
                    (VOLUNTEERTYPEID, QUANTITYNEEDED)
                select distinct STAFFRESOURCECONFLICTDETAILS.VOLUNTEERTYPEID, STAFFRESOURCES.QUANTITYNEEDED
                from dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCECONFLICTDETAILS(@START, @END, @STAFFRESOURCES, @IGNORERECORDID, @IGNORESUBRECORDID, null, 1, 1, 1, null) as STAFFRESOURCECONFLICTDETAILS
                    inner join @STAFFRESOURCESTABLE as STAFFRESOURCES
                        on STAFFRESOURCECONFLICTDETAILS.VOLUNTEERTYPEID = STAFFRESOURCES.VOLUNTEERTYPEID

                    return;
                end