USP_DATALIST_ITINERARYITINERARYITEMSTAFFRESOURCEJOBOCCURRENCEMATCH

            CREATE procedure dbo.USP_DATALIST_ITINERARYITINERARYITEMSTAFFRESOURCEJOBOCCURRENCEMATCH
            (
                @ID uniqueidentifier,
                @ISITINERARYRESOURCE bit = 1
            )
            as
                set nocount on;

                declare @MATCHSTARTDATETIME datetime
                declare @MATCHENDDATETIME datetime
                declare @JOBOCCURRENCEID uniqueidentifier
                declare @JOBID uniqueidentifier
                declare @VOLUNTEERTYPEID uniqueidentifier
                declare @TRAITCOUNT numeric(13,2);
                declare @REQUIREDTRAITCOUNT int = 0;
                declare @LOCATIONID uniqueidentifier

                if @ISITINERARYRESOURCE = 1
                begin
                    select
                        @MATCHSTARTDATETIME = ITINERARY.STARTDATETIME, 
                        @MATCHENDDATETIME = ITINERARY.ENDDATETIME,
                        @JOBOCCURRENCEID = ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID,
                        @JOBID = ITINERARYSTAFFRESOURCE.JOBID
                    from dbo.ITINERARY
                    inner join dbo.ITINERARYSTAFFRESOURCE on
                        ITINERARY.ID = ITINERARYSTAFFRESOURCE.ITINERARYID
                    where
                        ITINERARYSTAFFRESOURCE.ID = @ID
                end
                else
                begin
                    select
                        @MATCHSTARTDATETIME = ITINERARYITEM.STARTDATETIME, 
                        @MATCHENDDATETIME = ITINERARYITEM.ENDDATETIME,
                        @JOBOCCURRENCEID = ITINERARYITEMSTAFFRESOURCE.JOBOCCURRENCEID,
                        @JOBID = ITINERARYITEMSTAFFRESOURCE.JOBID
                    from dbo.ITINERARYITEM
                    inner join dbo.ITINERARYITEMSTAFFRESOURCE on
                        ITINERARYITEM.ID = ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID
                    where
                        ITINERARYITEMSTAFFRESOURCE.ID = @ID
                end

                select  
                    @LOCATIONID = LOCATIONCODEID,
                    @VOLUNTEERTYPEID = VOLUNTEERTYPEID
                from dbo.JOBOCCURRENCE 
                inner join dbo.JOB on
                    dbo.JOBOCCURRENCE.JOBID = dbo.JOB.ID
                where JOBOCCURRENCE.ID = @JOBOCCURRENCEID

                -- Constants for matching

                declare @TODAY datetime;
                set @TODAY = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
                select 
                    @TRAITCOUNT = count(*),
                    @REQUIREDTRAITCOUNT = coalesce(sum(case when ISREQUIRED = 1 then 1 else 0 end), 0)
                from 
                    dbo.[UFN_JOBOCCURRENCE_TRAITIDS](@JOBOCCURRENCEID) 
                where 
                    TRAITTYPEID <> 7 --exclude special needs


                declare @VOLUNTEERMATCH table
                (
                    ID uniqueidentifier,
                    VALIDINTIME bit,
                    TRAITHIT tinyint,
                    REQUIREDTRAITCOUNT tinyint
                )

                insert into @VOLUNTEERMATCH
                (
                    ID,
                    VALIDINTIME,
                    TRAITHIT,
                    REQUIREDTRAITCOUNT
                )
                select    
                    VA.VOLUNTEERID,
                    VA.VALIDINTIME,
                    count(distinct(VOLUNTEERADMINISTRATIVE.ID)) + 
                    count(distinct(VOLUNTEERCERTIFICATION.ID)) + 
                    count(distinct(VOLUNTEERCOURSE.ID)) + 
                    count(distinct(VOLUNTEERINTEREST.ID)) + 
                    count(distinct(VOLUNTEERLICENSE.ID)) + 
                    count(distinct(VOLUNTEERMEDICAL.ID)) + 
                    count(distinct(VOLUNTEERSKILL.ID)) as TRAITHIT,
                    sum(
                            case 
                                when JOBADMINISTRATIVE.ISREQUIRED = 1 and VOLUNTEERADMINISTRATIVE.ID is not null then 1
                                when JOBCERTIFICATION.ISREQUIRED = 1 and VOLUNTEERCERTIFICATION.ID is not null then 1
                                when JOBCOURSE.ISREQUIRED = 1 and VOLUNTEERCOURSE.ID is not null then 1
                                when JOBINTEREST.ISREQUIRED = 1 and VOLUNTEERINTEREST.ID is not null then 1
                                when JOBLICENSE.ISREQUIRED = 1 and VOLUNTEERLICENSE.ID is not null then 1
                                when JOBMEDICAL.ISREQUIRED = 1 and VOLUNTEERMEDICAL.ID is not null then 1
                                when JOBSKILL.ISREQUIRED = 1 and VOLUNTEERSKILL.ID is not null then 1
                                else 0
                            end
                        ) as REQUIREDTRAITCOUNT
                from dbo.JOB
                cross join [dbo].[UFN_VOLUNTEER_GETVOLUNTEERSAVAILABLEFORDATERANGE](@MATCHSTARTDATETIME, @MATCHENDDATETIME, @VOLUNTEERTYPEID) as VA
                left outer join dbo.JOBADMINISTRATIVE on 
                    JOBADMINISTRATIVE.JOBID = JOB.ID 
                left outer join dbo.VOLUNTEERADMINISTRATIVE on 
                    VOLUNTEERADMINISTRATIVE.VOLUNTEERID = VA.VOLUNTEERID and 
                    VOLUNTEERADMINISTRATIVE.ADMINISTRATIVECODEID = JOBADMINISTRATIVE.ADMINISTRATIVECODEID and 
                    VOLUNTEERADMINISTRATIVE.VERIFIED = 1 and 
                    (
                        VOLUNTEERADMINISTRATIVE.EXPIRESON is null or
                        VOLUNTEERADMINISTRATIVE.EXPIRESON >= @TODAY
                    )
                left outer join dbo.JOBCERTIFICATION on 
                    JOBCERTIFICATION.JOBID = JOB.ID 
                left outer join dbo.VOLUNTEERCERTIFICATION on 
                    VOLUNTEERCERTIFICATION.VOLUNTEERID = VA.VOLUNTEERID and 
                    VOLUNTEERCERTIFICATION.CERTIFICATIONCODEID = JOBCERTIFICATION.CERTIFICATIONCODEID and 
                    VOLUNTEERCERTIFICATION.VERIFIED = 1 and 
                    (
                        VOLUNTEERCERTIFICATION.EXPIRESON is null or 
                        VOLUNTEERCERTIFICATION.EXPIRESON >= @TODAY
                    )
                left outer join dbo.JOBCOURSE on 
                    JOBCOURSE.JOBID = JOB.ID
                left outer join dbo.VOLUNTEERCOURSE    on 
                    VOLUNTEERCOURSE.VOLUNTEERID = VA.VOLUNTEERID and 
                    VOLUNTEERCOURSE.COURSECODEID = JOBCOURSE.COURSECODEID and 
                    VOLUNTEERCOURSE.VERIFIED = 1 and 
                    (
                        VOLUNTEERCOURSE.EXPIRESON is null or 
                        VOLUNTEERCOURSE.EXPIRESON >= @TODAY
                    )
                left outer join dbo.JOBINTEREST    on 
                    JOBINTEREST.JOBID = JOB.ID
                left outer join dbo.VOLUNTEERINTEREST on 
                    VOLUNTEERINTEREST.VOLUNTEERID = VA.VOLUNTEERID and 
                    VOLUNTEERINTEREST.VOLUNTEERINTERESTCODEID = JOBINTEREST.VOLUNTEERINTERESTCODEID 
                left outer join dbo.JOBLICENSE on 
                    JOBLICENSE.JOBID = JOB.ID 
                left outer join dbo.VOLUNTEERLICENSE on 
                    VOLUNTEERLICENSE.VOLUNTEERID = VA.VOLUNTEERID and 
                    VOLUNTEERLICENSE.LICENSECODEID = JOBLICENSE.LICENSECODEID and 
                    VOLUNTEERLICENSE.VERIFIED = 1 and 
                    (
                        VOLUNTEERLICENSE.EXPIRESON is null or 
                        VOLUNTEERLICENSE.EXPIRESON >= @TODAY
                    )
                left outer join dbo.JOBMEDICAL on 
                    JOBMEDICAL.JOBID = JOB.ID
                left outer join dbo.VOLUNTEERMEDICAL on 
                    VOLUNTEERMEDICAL.VOLUNTEERID = VA.VOLUNTEERID and 
                    VOLUNTEERMEDICAL.MEDICALCODEID = JOBMEDICAL.MEDICALCODEID and 
                    VOLUNTEERMEDICAL.VERIFIED = 1 and 
                    (
                        VOLUNTEERMEDICAL.EXPIRESON is null or 
                        VOLUNTEERMEDICAL.EXPIRESON >= @TODAY
                    )
                left outer join dbo.JOBSKILL on 
                    JOBSKILL.JOBID = JOB.ID 
                left outer join dbo.VOLUNTEERSKILLLEVEL as [JOBSKILLLEVEL] on 
                    JOBSKILLLEVEL.ID = JOBSKILL.SKILLLEVELID
                    left outer join (
                        select
                            VOLUNTEERSKILL.ID,
                            VOLUNTEERSKILL.VOLUNTEERID,
                            VOLUNTEERSKILL.SKILLCODEID,
                            VOLUNTEERSKILLLEVEL.SEQUENCE
                        from dbo.VOLUNTEERSKILL
                        inner join dbo.VOLUNTEERSKILLLEVEL on VOLUNTEERSKILLLEVEL.ID = VOLUNTEERSKILL.SKILLLEVELID
                    ) as VOLUNTEERSKILL
                        on VOLUNTEERSKILL.VOLUNTEERID = VA.VOLUNTEERID
                            and VOLUNTEERSKILL.SKILLCODEID = JOBSKILL.SKILLCODEID
                            and VOLUNTEERSKILL.SEQUENCE >= JOBSKILLLEVEL.SEQUENCE
                left outer join    dbo.VOLUNTEERVOLUNTEERLOCATION
                    on VA.VOLUNTEERID = VOLUNTEERVOLUNTEERLOCATION.VOLUNTEERID
                where
                    JOB.ID = @JOBID and
                    (
                        @LOCATIONID is null or -- no location

                        VOLUNTEERVOLUNTEERLOCATION.ID is null or --none

                        VOLUNTEERVOLUNTEERLOCATION.VOLUNTEERLOCATIONCODEID = @LOCATIONID
                    )
                group by VA.VOLUNTEERID, JOB.ID, VA.VALIDINTIME;


                /* Remove volunteers that do not have fulfilled special needs */
                with UNFILLEDSPECIAL_CTE as
                (
                    select 
                        VM.ID as ID
                    from @VOLUNTEERMATCH as VM
                    inner join VOLUNTEERSPECIALNEED VSN on 
                        VSN.VOLUNTEERID = VM.ID
                    left outer join dbo.JOBSPECIALNEED as JSN on 
                        JSN.JOBID = @JOBID and 
                        JSN.SPECIALNEEDCODEID = VSN.SPECIALNEEDCODEID
                    where 
                        JSN.ID is null
                )
                delete from @VOLUNTEERMATCH
                from UNFILLEDSPECIAL_CTE
                where 
                    [@VOLUNTEERMATCH].ID = UNFILLEDSPECIAL_CTE.ID 

                /*PUT IT ALL TOGETHER */
                select distinct 
                    VM.ID,
                    VM.VALIDINTIME,
                    cast(round(case when @TRAITCOUNT = 0 then 1
                            else TRAITHIT / @TRAITCOUNT end * 100, 0) as int) as PERCENTMATCH,
                    dbo.UFN_CONSTITUENT_BUILDNAME(VM.ID) as VOLUNTEERNAME
                from @VOLUNTEERMATCH as VM
                where
                    @REQUIREDTRAITCOUNT = VM.REQUIREDTRAITCOUNT
                order by PERCENTMATCH desc, VOLUNTEERNAME asc;