UFN_STAFFRESOURCE_CONSTITUENTISVALID

Checks to see if a constituent is valid for a staff resource given the resource needed times and filled by code.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@FILLEDBYCODE	tinyint	IN
@START	datetime	IN
@END	datetime	IN

Definition

Copy



            CREATE function dbo.UFN_STAFFRESOURCE_CONSTITUENTISVALID
            (
                @CONSTITUENTID uniqueidentifier,
                @FILLEDBYCODE tinyint,
                @START datetime,
                @END datetime
            )
            returns bit
            as begin
                declare @RETVAL bit = 0;

                if (@FILLEDBYCODE = 0) /* Volunteer */
                begin
                    if (exists (select VOLUNTEER.ID
                                from dbo.VOLUNTEER
                                where (VOLUNTEER.ID = @CONSTITUENTID)))
                        set @RETVAL = 1;
                end
                else if (@FILLEDBYCODE = 1) /* Staff */
                begin
                    if (exists (select STAFFDATERANGE.ID
                                from dbo.STAFFDATERANGE
                                where (STAFFDATERANGE.CONSTITUENTID = @CONSTITUENTID) and
                                        ((STAFFDATERANGE.DATEFROM <= @END) or (STAFFDATERANGE.DATEFROM is null) or (@END is null)) and
                                        ((STAFFDATERANGE.DATETO >= @START) or (STAFFDATERANGE.DATETO is null) or (@START is null))))
                        set @RETVAL = 1;
                end    
                else if (@FILLEDBYCODE = 2) /* Board */
                begin
                    if (exists (select BOARDMEMBERDATERANGE.ID
                                from dbo.BOARDMEMBERDATERANGE
                                where (BOARDMEMBERDATERANGE.CONSTITUENTID = @CONSTITUENTID) and
                                        ((BOARDMEMBERDATERANGE.DATEFROM <= @END) or (BOARDMEMBERDATERANGE.DATEFROM is null) or (@END is null)) and
                                        ((BOARDMEMBERDATERANGE.DATETO >= @START) or (BOARDMEMBERDATERANGE.DATETO is null) or (@START is null))))
                        set @RETVAL = 1;
                end

                return @RETVAL;
            end