V_QUERY_HOUSEHOLDMODELINGANDPROPENSITY

Household Modeling and Propensity.

Fields

Field	Field Type	Null	Description
HOUSEHOLDID	uniqueidentifier	yes	Household
ANNUALGIFTLIKELIHOOD	int	yes	Annual giving likelihood
ANNUITYLIKELIHOOD	int	yes	Annuity likelihood
BEQUESTLIKELIHOOD	int	yes	Bequest likelihood
CRTLIKELIHOOD	int	yes	CRT likelihood
MAJORGIVINGLIKELIHOOD	int	yes	Major giving likelihood
PLANNEDGIFTLIKELIHOOD	int	yes	Planned giving likelihood
MEMBERSHIPLIKELIHOOD	int	yes	Membership likelihood
ONLINEGIVINGLIKELIHOOD	int	yes	Online giving likelihood
PATIENTRESPONSELIKELIHOOD	int	yes	Patient response likelihood
SUGGESTEDMEMBERSHIPLEVEL	nvarchar(50)	yes	Suggested membership level range
SUGGESTEDMEMBERSHIPLEVELSCORE	int	yes	Suggested membership level score
TARGETGIFTRANGE	nvarchar(50)	yes	Target gift range
TARGETGIFTRANGESCORE	int	yes	Target gift range score
WEALTHESTIMATORRATING	nvarchar(50)	yes	Wealth estimator rating range
WEALTHESTIMATORRATINGSCORE	int	yes	Wealth estimator rating score

Definition

Copy

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



with CONSTITUENT_CTE as(
    select
        CONSTITUENT.ID as CONSTITUENTID,
        case when subq.MEMBERID is not null then subq.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
        where dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1 and GROUPDATA.GROUPTYPECODE = 0
    ) subq
    on subq.MEMBERID = CONSTITUENT.ID
),
MODELINGANDPROPENSITY_CTE as(
    select
        MP.ID,
        case MP.ANNUALGIFTLIKELIHOOD when -1 then NULL else MP.ANNUALGIFTLIKELIHOOD end ANNUALGIFTLIKELIHOOD,
        case MP.ANNUITYLIKELIHOOD when -1 then NULL else MP.ANNUITYLIKELIHOOD end ANNUITYLIKELIHOOD,
        case MP.BEQUESTLIKELIHOOD when -1 then NULL else MP.BEQUESTLIKELIHOOD end BEQUESTLIKELIHOOD,
        case MP.CRTLIKELIHOOD when -1 then NULL else MP.CRTLIKELIHOOD end CRTLIKELIHOOD,
        case MP.MAJORGIVINGLIKELIHOOD when -1 then NULL else MP.MAJORGIVINGLIKELIHOOD end MAJORGIVINGLIKELIHOOD,
        case MP.PLANNEDGIFTLIKELIHOOD when -1 then NULL else MP.PLANNEDGIFTLIKELIHOOD end PLANNEDGIFTLIKELIHOOD,
        case MP.MEMBERSHIPLIKELIHOOD when -1 then NULL else MP.MEMBERSHIPLIKELIHOOD end MEMBERSHIPLIKELIHOOD,
        case MP.ONLINEGIVINGLIKELIHOOD when -1 then NULL else MP.ONLINEGIVINGLIKELIHOOD end ONLINEGIVINGLIKELIHOOD,
        case MP.PATIENTRESPONSELIKELIHOOD when -1 then NULL else MP.PATIENTRESPONSELIKELIHOOD end PATIENTRESPONSELIKELIHOOD,
        SUGGESTEDMEMBERSHIPLEVEL.SCORE MEMBERSHIPSCORE,
        TARGETGIFTRANGE.SCORE TARGETGIFTSCORE,
        WEALTHESTIMATORRATING.SCORE WEALTHESTIMATORSCORE
    from dbo.MODELINGANDPROPENSITY MP
    left join dbo.SUGGESTEDMEMBERSHIPLEVEL on SUGGESTEDMEMBERSHIPLEVEL.ID = MP.SUGGESTEDMEMBERSHIPLEVELID
    left join dbo.TARGETGIFTRANGE on TARGETGIFTRANGE.ID = MP.TARGETGIFTRANGEID
    left join dbo.WEALTHESTIMATORRATING on WEALTHESTIMATORRATING.ID = MP.WEALTHESTIMATORRATINGID
)
select
    CONSTITUENT_CTE.HOUSEHOLDID,
    max(MP.ANNUALGIFTLIKELIHOOD) ANNUALGIFTLIKELIHOOD,
    max(MP.ANNUITYLIKELIHOOD) ANNUITYLIKELIHOOD,
    max(MP.BEQUESTLIKELIHOOD) BEQUESTLIKELIHOOD,
    max(MP.CRTLIKELIHOOD) CRTLIKELIHOOD,
    max(MP.MAJORGIVINGLIKELIHOOD) MAJORGIVINGLIKELIHOOD,
    max(MP.PLANNEDGIFTLIKELIHOOD) PLANNEDGIFTLIKELIHOOD,
    max(MP.MEMBERSHIPLIKELIHOOD) MEMBERSHIPLIKELIHOOD,
    max(MP.ONLINEGIVINGLIKELIHOOD) ONLINEGIVINGLIKELIHOOD,
    max(MP.PATIENTRESPONSELIKELIHOOD) PATIENTRESPONSELIKELIHOOD,
    (select [RANGE] from dbo.SUGGESTEDMEMBERSHIPLEVEL where SCORE = max(MEMBERSHIPSCORE)) SUGGESTEDMEMBERSHIPLEVEL,
    max(MEMBERSHIPSCORE) SUGGESTEDMEMBERSHIPLEVELSCORE,
    (select [RANGE] from dbo.TARGETGIFTRANGE where SCORE = max(TARGETGIFTSCORE)) TARGETGIFTRANGE,
    max(TARGETGIFTSCORE) TARGETGIFTRANGESCORE,
    (select [RANGE] from dbo.WEALTHESTIMATORRATING where SCORE = max(WEALTHESTIMATORSCORE)) WEALTHESTIMATORRATING,
    max(WEALTHESTIMATORSCORE) WEALTHESTIMATORRATINGSCORE
from CONSTITUENT_CTE 
left join MODELINGANDPROPENSITY_CTE MP on MP.ID = CONSTITUENT_CTE.CONSTITUENTID
group by HOUSEHOLDID