V_QUERY_HOUSEHOLD_MODELSCORESANDRATINGS_DETAIL

/*
Generated by Blackbaud AppFx Platform
Date:  3/19/2013 1:38:48 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_HOUSEHOLD_MODELSCORESANDRATINGS_DETAIL AS



with CONSTITUENT_CTE as
(

    select
        CONSTITUENT.ID as CONSTITUENTID,
        case
            when GM.MEMBERID is not null then GM.GROUPID
            else CONSTITUENT.ID
        end as HOUSEHOLDID
    from
        dbo.CONSTITUENT
        left join 
        (
            select
                GROUPMEMBER.MEMBERID,
                GROUPMEMBER.GROUPID
            from
                dbo.GROUPMEMBER
                inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
                left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
            where
                GROUPDATA.GROUPTYPECODE = 0 --household

                and --Is current member

                (
                    (
                        GROUPMEMBERDATERANGE.DATEFROM is null
                        and
                        (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO >= getdate())
                    )
                    or
                    (
                        GROUPMEMBERDATERANGE.DATETO is null
                        and
                        (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= getdate())
                    )
                    or
                    (
                        GROUPMEMBERDATERANGE.DATEFROM <= getdate()
                        and
                        GROUPMEMBERDATERANGE.DATETO >= getdate()
                    )
                )
        ) GM on GM.MEMBERID = CONSTITUENT.ID
)
select
    MODELINGANDPROPENSITYDELIVERY.ID,

    MODELINGANDPROPENSITYDELIVERY.MODELINGANDPROPENSITYID as CONSTITUENTID,
    CONSTITUENT_CTE.HOUSEHOLDID,
    ANALYTICSMODEL.ID as ANALYTICSMODELID,
    ANALYTICSMODEL.NAME as ANALYTICSMODELNAME,

    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,
    'Blackbaud' as ORIGIN,
    ANALYTICSPROJECT.ID as ANALYTICSPROJECTID

    /*#EXTENSION*/

from
    CONSTITUENT_CTE 
    left join dbo.MODELINGANDPROPENSITYDELIVERY on MODELINGANDPROPENSITYDELIVERY.MODELINGANDPROPENSITYID = CONSTITUENT_CTE.CONSTITUENTID
    left join dbo.MODELINGANDPROPENSITY on MODELINGANDPROPENSITYDELIVERY.MODELINGANDPROPENSITYID = MODELINGANDPROPENSITY.ID 
    left join dbo.ANALYTICSMODEL on ANALYTICSMODEL.ID = MODELINGANDPROPENSITYDELIVERY.ANALYTICSMODELID
    left join dbo.ANALYTICSPROJECT on ANALYTICSPROJECT.ID = MODELINGANDPROPENSITYDELIVERY.ANALYTICSPROJECTID