USP_DATALIST_VOLUNTEERMATCHES

            CREATE procedure dbo.USP_DATALIST_VOLUNTEERMATCHES (@VOLUNTEERID uniqueidentifier = null, @DAYLIST nvarchar(max) = '', @CURRENTAPPUSERID uniqueidentifier = null)
            as
                set nocount on;

                declare @TODAY datetime;

                /*CONSTANTS FOR MATCHING*/
                set @TODAY = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                /*TABLE CLEANUP */
                if not object_id('tempdb..#AVAILABLE') is null  
                    drop table #AVAILABLE
                if not object_id('tempdb..#TRAITS') is null  
                    drop table #TRAITS


                /*FIND AVAILABLE*/
                select VACANCIES.OCCURRENCEID, JOBOCCURRENCE.JOBID, replace(dbo.UDA_BUILDLIST(distinct convert(nvarchar(8), AVAILABLE.AVAILABLEDATE, 112)), '; ', '') DAYS
                into #AVAILABLE
                from [UFN_JOBOCCURRENCE_VACANTSPECIFICDAYS](@DAYLIST,null,null) VACANCIES 
                inner join dbo.JOBOCCURRENCE 
                    on JOBOCCURRENCE.ID = VACANCIES.OCCURRENCEID
                inner join dbo.JOB
                    on JOBOCCURRENCE.JOBID = JOB.ID
                inner join dbo.[UFN_VOLUNTEER_AVAILABLESPECIFICDAYS](@DAYLIST, @VOLUNTEERID, 0) AVAILABLE
                    on AVAILABLE.AVAILABLEDATE = VACANCIES.ASSIGNMENTDATE
                left outer join dbo.VOLUNTEERVOLUNTEERTYPE VOLTYPE
                    on VOLTYPE.VOLUNTEERID = @VOLUNTEERID AND VOLTYPE.STATUSCODE = 1
                left outer join dbo.VOLUNTEERASSIGNMENT VAS
                    on AVAILABLE.VOLUNTEERID = VAS.VOLUNTEERID
                        and VAS.DATE = VACANCIES.ASSIGNMENTDATE
                left outer join dbo.JOBOCCURRENCE ASSIGNEDJO
                    on ASSIGNEDJO.ID = VAS.JOBOCCURRENCEID and
                       (ASSIGNEDJO.STARTTIME between JOBOCCURRENCE.STARTTIME and JOBOCCURRENCE.ENDTIME
                        or
                       ASSIGNEDJO.ENDTIME between JOBOCCURRENCE.STARTTIME and JOBOCCURRENCE.ENDTIME
                       or ASSIGNEDJO.STARTTIME = '' or ASSIGNEDJO.ENDTIME = ''
                       or JOBOCCURRENCE.STARTTIME = '' or JOBOCCURRENCE.ENDTIME  = '')
                left outer join    dbo.VOLUNTEERVOLUNTEERLOCATION
                    on AVAILABLE.VOLUNTEERID = VOLUNTEERVOLUNTEERLOCATION.VOLUNTEERID
                where (JOB.VOLUNTEERTYPEID is null or VOLTYPE.VOLUNTEERTYPEID = JOB.VOLUNTEERTYPEID) and
                      (AVAILABLE.STARTTIME <= JOBOCCURRENCE.STARTTIME or JOBOCCURRENCE.STARTTIME = '')
                        and
                      (AVAILABLE.ENDTIME >= JOBOCCURRENCE.ENDTIME or AVAILABLE.ENDTIME = '')
                      and 
                      (JOBOCCURRENCE.LOCATIONCODEID is null -- no location

                            or VOLUNTEERVOLUNTEERLOCATION.ID is null --none

                            or VOLUNTEERVOLUNTEERLOCATION.VOLUNTEERLOCATIONCODEID = JOBOCCURRENCE.LOCATIONCODEID)
                      and AVAILABLE.AVAILABLEDATE >= @TODAY 
                      and (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOBOCCURRENCE.SITEID) = 1)
                      and (dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,JOB.SITEID) = 1)
                group by VACANCIES.OCCURRENCEID, JOBOCCURRENCE.JOBID
                having max(case when ASSIGNEDJO.ID is null then 0 else 1 end) = 0;                
                create index AVAILABLE_OCCURRENCEIDID on #AVAILABLE(OCCURRENCEID);

                /*Remove jobs that do not have all of the required special needs*/
                delete from #AVAILABLE
                where JOBID in 
                    (select AVAIL.JOBID
                     from #AVAILABLE AVAIL
                     inner join VOLUNTEERSPECIALNEED VSN
                        on VSN.VOLUNTEERID = @VOLUNTEERID
                     left outer join dbo.JOBSPECIALNEED JSN
                        on JSN.JOBID = AVAIL.JOBID and JSN.SPECIALNEEDCODEID = VSN.SPECIALNEEDCODEID
                     where JSN.ID is null)


                /*FIND TRAITS*/
                select    AVAIL.OCCURRENCEID,
                        AVAIL.JOBID,
                        AVAIL.DAYS,
                        cast((select count(*) from dbo.[UFN_JOB_TRAITS](AVAIL.JOBID) where TRAITTYPEID <> 7) as numeric(13,2)) AS TRAITCOUNT,
                        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(VOLUNTEERSKILLLEVEL.ID)) TRAITHIT,
                        sum(
                                case 
                                    when JOBADMINISTRATIVE.ISREQUIRED = 1 and VOLUNTEERADMINISTRATIVE.ID is null then 1
                                    when JOBCERTIFICATION.ISREQUIRED = 1 and VOLUNTEERCERTIFICATION.ID is null then 1
                                    when JOBCOURSE.ISREQUIRED = 1 and VOLUNTEERCOURSE.ID is null then 1
                                    when JOBINTEREST.ISREQUIRED = 1 and VOLUNTEERINTEREST.ID is null then 1
                                    when JOBLICENSE.ISREQUIRED = 1 and VOLUNTEERLICENSE.ID is null then 1
                                    when JOBMEDICAL.ISREQUIRED = 1 and VOLUNTEERMEDICAL.ID is null then 1
                                    when JOBSKILL.ISREQUIRED = 1 and VOLUNTEERSKILLLEVEL.ID is null then 1
                                    else 0
                                end
                            ) as MISSINGREQUIREDTRAITCOUNT
                into #TRAITS
                from #AVAILABLE AVAIL
                left outer join dbo.JOBADMINISTRATIVE
                    on JOBADMINISTRATIVE.JOBID = AVAIL.JOBID 
                left outer join dbo.VOLUNTEERADMINISTRATIVE
                    on VOLUNTEERADMINISTRATIVE.VOLUNTEERID = @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 = AVAIL.JOBID 
                left outer join dbo.VOLUNTEERCERTIFICATION
                    on VOLUNTEERCERTIFICATION.VOLUNTEERID = @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 = AVAIL.JOBID 
                left outer join dbo.VOLUNTEERCOURSE
                    on VOLUNTEERCOURSE.VOLUNTEERID = @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 = AVAIL.JOBID 
                left outer join dbo.VOLUNTEERINTEREST
                    on VOLUNTEERINTEREST.VOLUNTEERID = @VOLUNTEERID 
                            and VOLUNTEERINTEREST.VOLUNTEERINTERESTCODEID = JOBINTEREST.VOLUNTEERINTERESTCODEID 
                left outer join dbo.JOBLICENSE
                    on JOBLICENSE.JOBID = AVAIL.JOBID 
                left outer join dbo.VOLUNTEERLICENSE
                    on VOLUNTEERLICENSE.VOLUNTEERID = @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 = AVAIL.JOBID 
                left outer join dbo.VOLUNTEERMEDICAL
                    on VOLUNTEERMEDICAL.VOLUNTEERID = @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 = AVAIL.JOBID 
                left outer join dbo.VOLUNTEERSKILLLEVEL as [JOBSKILLLEVEL]
                    on JOBSKILLLEVEL.ID = JOBSKILL.SKILLLEVELID
                left outer join dbo.VOLUNTEERSKILL
                    on VOLUNTEERSKILL.VOLUNTEERID = @VOLUNTEERID 
                            and VOLUNTEERSKILL.SKILLCODEID = JOBSKILL.SKILLCODEID
                left outer join dbo.VOLUNTEERSKILLLEVEL
                    on VOLUNTEERSKILLLEVEL.ID = VOLUNTEERSKILL.SKILLLEVELID
                            and VOLUNTEERSKILLLEVEL.SEQUENCE >= JOBSKILLLEVEL.SEQUENCE
                group by AVAIL.OCCURRENCEID,
                        AVAIL.JOBID,
                        AVAIL.DAYS

                create index TRAITS_JOBID on #TRAITS(JOBID);

                /*PUT IT ALL TOGETHER */
                select distinct 
                        TRAITS.OCCURRENCEID,
                        cast(round(case when TRAITCOUNT = 0 then 1
                                else TRAITHIT/TRAITCOUNT end * 100, 0) as int) as MATCH,
                        JOB.NAME + char(13) + JOBOCCURRENCE.DESCRIPTION as NAME,
                        JOB.ID,
                        TRAITS.DAYS
                from #TRAITS TRAITS
                inner join dbo.JOBOCCURRENCE
                    on TRAITS.OCCURRENCEID = JOBOCCURRENCE.ID
                inner join dbo.JOB 
                    on TRAITS.JOBID = JOB.ID
                where
                    coalesce(TRAITS.MISSINGREQUIREDTRAITCOUNT, 0) = 0
                order by MATCH desc, NAME asc;

                /*TABLE CLEANUP */
                if not object_id('tempdb..#AVAILABLE') is null
                    drop table #AVAILABLE
                if not object_id('tempdb..#TRAITS') is null
                    drop table #TRAITS