USP_DATALIST_JOBMATCHESVOLUNTEERTRAITS

This datalist returns all Traits matches for a job and a volunteer.

Parameters

Parameter	Parameter Type	Mode	Description
@JOBID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@VOLUNTEERID	uniqueidentifier	IN	Volunteer

Definition

Copy



            CREATE procedure dbo.USP_DATALIST_JOBMATCHESVOLUNTEERTRAITS (@JOBID uniqueidentifier = null, @VOLUNTEERID uniqueidentifier = null)
            as
                set nocount on;

                declare @TODAY datetime;
                set @TODAY = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                select     JT.TRAITTYPE,
                        coalesce(JT.ADMINISTRATIVE, JT.SKILL, JT.INTEREST, JT.SPECIALNEED,JT.CERTIFICATION,JT.COURSE,JT.LICENSE,JT.MEDICAL) JOBDESC,
                        cast(case when coalesce(JT.ADMINISTRATIVE, JT.SKILL, JT.INTEREST, JT.SPECIALNEED,JT.CERTIFICATION,JT.COURSE,JT.LICENSE,JT.MEDICAL)
                              = coalesce(VT.ADMINISTRATIVE, VT.SKILL, VT.INTEREST, VT.SPECIALNEED,VT.CERTIFICATION,VT.COURSE,VT.LICENSE,VT.MEDICAL)
                            and coalesce(VT.VERIFIED, 1) = 1 and (COALESCE(VT.EXPIRESON, @TODAY) >= @TODAY) then 1 else 0 end as bit) ISMATCH,
                        VT.VERIFIED,
                        JT.ISREQUIRED
                from dbo.UFN_JOB_TRAITS(@JOBID) JT
                left outer join dbo.UFN_VOLUNTEER_TRAITS(@VOLUNTEERID) VT
                    on JT.TRAITTYPE = VT.TRAITTYPE
                        and coalesce(JT.ADMINISTRATIVE, JT.SKILL, JT.INTEREST, JT.SPECIALNEED,JT.CERTIFICATION,JT.COURSE,JT.LICENSE,JT.MEDICAL)
                          = coalesce(VT.ADMINISTRATIVE, VT.SKILL, VT.INTEREST, VT.SPECIALNEED,VT.CERTIFICATION,VT.COURSE,VT.LICENSE,VT.MEDICAL)
                        and coalesce(JT.SKILLLEVELSEQUENCE, 0) <= coalesce(VT.SKILLLEVELSEQUENCE, 0)
                where JT.TRAITTYPEID <> 7 --exclude special needs

                order by 
                    ISMATCH desc,
                    coalesce(JT.TRAITTYPE, VT.TRAITTYPE),
                    coalesce(JT.ADMINISTRATIVE, JT.SKILL, JT.INTEREST, JT.SPECIALNEED,JT.CERTIFICATION,JT.COURSE,JT.LICENSE,JT.MEDICAL)