UFN_MODELSCORESFORCONSTITUENT

            CREATE function dbo.UFN_MODELSCORESFORCONSTITUENT(@CONSTITUENTID uniqueidentifier)
            returns table
            as return (

                select top 500 ID, NAME, SCORE, MAXIMUMSCORE, UPDATEDON, ANALYTICSMODELID from (

                    select
                        row_number() over (partition by ANALYTICSMODEL.ID order by ANALYTICSPROJECT.DATE desc) as ANALYTICSMODELROWNUMBER,

                        MODELINGANDPROPENSITYDELIVERY.ID as ID,
                        ANALYTICSMODEL.NAME as NAME,

                        case ANALYTICSMODEL.ID
                        when '3CF4EC5A-63FE-42EA-B70B-7F6CAAE286F1' then cast(nullif(MODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD, -1) as nvarchar(50))
                        when '3788BA7E-E7DC-4A6E-93C9-F2245B26A13B' then cast(nullif(MODELINGANDPROPENSITY.ANNUITYLIKELIHOOD, -1) as nvarchar(50))
                        when 'DEF85E09-E51A-4EED-B8AF-0763272A5593' then cast(nullif(MODELINGANDPROPENSITY.BEQUESTLIKELIHOOD, -1) as nvarchar(50))
                        when 'FEB5BE0E-3B4F-45E1-A3E2-26F4372DEE96' then cast(nullif(MODELINGANDPROPENSITY.CRTLIKELIHOOD, -1) as nvarchar(50))
                        when 'CE8C3F19-6B9A-4852-AB3C-3765F48578F7' then cast(nullif(MODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD, -1) as nvarchar(50))
                        when 'B2C12F11-3C63-4077-ADD0-C976A905B07C' then cast(nullif(MODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD, -1) as nvarchar(50))
                        when '06776DBB-E33A-45A2-B1DE-972B626169DC' then cast(nullif(MODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD, -1) as nvarchar(50))
                        when '64111AFB-850D-4407-B403-948F0CD8BE25' then cast(nullif(MODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD, -1) as nvarchar(50))
                        when '2018E9DB-F837-4CC0-BFC9-E72E5E63ACB5' then cast(nullif(MODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD, -1) as nvarchar(50))
                        when 'A4BACAA3-8EE4-4476-B359-428E582C42AE' then 
                            (
                                select SUGGESTEDMEMBERSHIPLEVEL.RANGE from dbo.SUGGESTEDMEMBERSHIPLEVEL 
                                where MODELINGANDPROPENSITY.SUGGESTEDMEMBERSHIPLEVELID = SUGGESTEDMEMBERSHIPLEVEL.ID
                            )
                        when 'CA0A6B61-2201-482F-AE8D-C6AE6852C318' then 
                            (
                                select TARGETGIFTRANGE.RANGE from dbo.TARGETGIFTRANGE 
                                where MODELINGANDPROPENSITY.TARGETGIFTRANGEID = TARGETGIFTRANGE.ID
                            )
                        when '4D03C579-7461-42AE-8445-442D510B6151' then
                            (
                                select WEALTHESTIMATORRATING.RANGE from dbo.WEALTHESTIMATORRATING
                                where MODELINGANDPROPENSITY.WEALTHESTIMATORRATINGID = WEALTHESTIMATORRATING.ID
                            )
                        when 'CA48F28A-DBE6-40E5-907A-D29D40F9B588' then
                            (
                                case MODELINGANDPROPENSITY.NCOAMOVEDINDICATOR
                                when 1 then 'Yes'
                                else null
                                end
                            )
                        end as SCORE,

                        ANALYTICSMODEL.MAXIMUMVALUE as MAXIMUMSCORE,
                        coalesce(ANALYTICSPROJECT.DATE, cast(MODELINGANDPROPENSITY.DATECHANGED as date)) as UPDATEDON,
                        ANALYTICSMODEL.ID as ANALYTICSMODELID
                    from dbo.ANALYTICSMODEL
                    inner join dbo.MODELINGANDPROPENSITY on MODELINGANDPROPENSITY.ID = @CONSTITUENTID
                    left join dbo.MODELINGANDPROPENSITYDELIVERY on MODELINGANDPROPENSITYDELIVERY.MODELINGANDPROPENSITYID = MODELINGANDPROPENSITY.ID 
                        and MODELINGANDPROPENSITYDELIVERY.ANALYTICSMODELID = ANALYTICSMODEL.ID
                    left join dbo.ANALYTICSPROJECT on ANALYTICSPROJECT.ID = MODELINGANDPROPENSITYDELIVERY.ANALYTICSPROJECTID
                    where MODELINGANDPROPENSITY.ID = @CONSTITUENTID

                ) as SUBSELECT
                where SUBSELECT.SCORE is not null
                and ANALYTICSMODELROWNUMBER = 1
                order by UPDATEDON desc, NAME asc

            )