V_QUERY_HOUSEHOLDWEALTH

Household wealth

Fields

Field	Field Type	Null	Description
CONSTITUENTID	uniqueidentifier		Constituent
REALESTATE_IDENTIFIEDASSETS	money	yes	Real estate assets - identified
REALESTATE_CONFIRMEDASSETS	money	yes	Real estate assets - confirmed
REALESTATE_IDENTIFIED	int	yes	Real estate number - identified
REALESTATE_CONFIRMED	int	yes	Real estate number - confirmed
BUSINESSOWNERSHIP_IDENTIFIEDASSETS	numeric(38, 6)	yes	Business ownership assets - identified
BUSINESSOWNERSHIP_CONFIRMEDASSETS	numeric(38, 6)	yes	Business ownership assets - confirmed
BUSINESSOWNERSHIP_IDENTIFIED	int	yes	Business ownership number - identified
BUSINESSOWNERSHIP_CONFIRMED	int	yes	Business ownership number - confirmed
SECURITIES_IDENTIFIEDASSETS	money	yes	Securities assets - identified
SECURITIES_CONFIRMEDASSETS	money	yes	Securities assets - confirmed
SECURITIES_IDENTIFIED	int	yes	Securities number - identified
SECURITIES_CONFIRMED	int	yes	Securities number - confirmed
AFFLUENCEINDICATOR_IDENTIFIEDASSETS	money	yes	Affluence indicator assets - identified
AFFLUENCEINDICATOR_CONFIRMEDASSETS	money	yes	Affluence indicator assets - confirmed
AFFLUENCEINDICATOR_IDENTIFIED	int	yes	Affluence indicator number - identified
AFFLUENCEINDICATOR_CONFIRMED	int	yes	Affluence indicator number - confirmed
INCOME_IDENTIFIEDASSETS	money	yes	Income/compensation assets - identified
INCOME_CONFIRMEDASSETS	money	yes	Income/compensation assets - confirmed
INCOME_IDENTIFIED	int	yes	Income/compensation number - identified
INCOME_CONFIRMED	int	yes	Income/compensation number - confirmed
OTHERASSETS_IDENTIFIEDASSETS	money	yes	Other assets - identified
OTHERASSETS_CONFIRMEDASSETS	money	yes	Other assets - confirmed
OTHERASSETS_IDENTIFIED	int	yes	Other assets number - identified
OTHERASSETS_CONFIRMED	int	yes	Other assets number - confirmed
BIOGRAPHICAL_IDENTIFIED	int	yes	Biographical number - identified
BIOGRAPHICAL_CONFIRMED	int	yes	Biographical number - confirmed
POLITICALDONATION_IDENTIFIED	int	yes	Political donation number - identified
POLITICALDONATION_CONFIRMED	int	yes	Political donation number - confirmed
PHILANTHROPICGIFT_IDENTIFIED	int	yes	Philanthropic gift number - identified
PHILANTHROPICGIFT_CONFIRMED	int	yes	Philanthropic gift number - confirmed
NONPROFITAFFILIATION_IDENTIFIED	int	yes	Nonprofit affiliation number - identified
NONPROFITAFFILIATION_CONFIRMED	int	yes	Nonprofit affiliation number - confirmed
PRIVATEFOUNDATION_IDENTIFIED	int	yes	Private foundation number - identified
PRIVATEFOUNDATION_CONFIRMED	int	yes	Private foundation number - confirmed
TOTALASSETS_IDENTIFIED	numeric(38, 6)	yes	Total assets - identified
TOTALASSETS_CONFIRMED	numeric(38, 6)	yes	Total assets - confirmed

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  11/11/2014 4:17:27 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_HOUSEHOLDWEALTH 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
),
BIOGRAPHICALIDENTIFIED_CTE as(
    select 
        count(*) BIOGRAPHICAL_IDENTIFIED,
        HOUSEHOLDID
    from 
        (select
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        REJECTED,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPBIOGRAPHICAL
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where REJECTED = 0) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
BIOGRAPHICALCONFIRMED_CTE as(
    select 
        count(*) BIOGRAPHICAL_CONFIRMED,
        HOUSEHOLDID
    from 
        (select
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPBIOGRAPHICAL
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where CONFIRMED = 1) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
POLITICALDONATIONIDENTIFIED_CTE as(
    select 
        count(*) POLITICALDONATION_IDENTIFIED,
        HOUSEHOLDID
    from 
        (select
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPPOLITICALDONATION
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where REJECTED = 0) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
POLITICALDONATIONCONFIRMED_CTE as(
    select 
        count(*) POLITICALDONATION_CONFIRMED,
        HOUSEHOLDID
    from 
        (select
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPPOLITICALDONATION
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where CONFIRMED = 1) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
PHILANTRHOPICGIFTIDENTIFIED_CTE as(
    select 
        count(*) PHILANTHROPICGIFT_IDENTIFIED,
        HOUSEHOLDID
    from 
        (select
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPPHILANTHROPICGIFT
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where REJECTED = 0) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
PHILANTRHOPICGIFTCONFIRMED_CTE as(
    select 
        count(*) PHILANTHROPICGIFT_CONFIRMED,
        HOUSEHOLDID
    from 
        (select
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPPHILANTHROPICGIFT
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where CONFIRMED = 1) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
NONPROFITAFFILIATIONIDENTIFIED_CTE as(
    select 
        count(*) NONPROFITAFFILIATION_IDENTIFIED,
        HOUSEHOLDID
    from 
        (select
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPNONPROFITAFFILIATION
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where REJECTED = 0) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
NONPROFITAFFILIATIONCONFIRMED_CTE as(
    select 
        count(*) NONPROFITAFFILIATION_CONFIRMED,
        HOUSEHOLDID
    from 
        (select
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPNONPROFITAFFILIATION
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where CONFIRMED = 1) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
PRIVATEFOUNDATIONIDENTIFIED_CTE as(
    select 
        count(*) PRIVATEFOUNDATION_IDENTIFIED,
        HOUSEHOLDID
    from 
        (select
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPPRIVATEFOUNDATION
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where REJECTED = 0) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
PRIVATEFOUNDATIONCONFIRMED_CTE as(
    select 
        count(*) PRIVATEFOUNDATION_CONFIRMED,
        HOUSEHOLDID
    from 
        (select
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPPRIVATEFOUNDATION
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where CONFIRMED = 1) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
BUSINESSOWNERSHIPIDENTIFIED_CTE as(
    select 
        count(*) BUSINESSOWNERSHIP_IDENTIFIED,
        SUM(OWNERSHIPVALUE) BUSINESSOWNERSHIP_IDENTIFIEDASSETS,
        HOUSEHOLDID
    from 
        (select
        OWNERSHIPVALUE,
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPBUSINESSOWNERSHIP
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where REJECTED = 0) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
BUSINESSOWNERSHIPCONFIRMED_CTE as(
    select 
        count(*) BUSINESSOWNERSHIP_CONFIRMED,
        SUM(OWNERSHIPVALUE) BUSINESSOWNERSHIP_CONFIRMEDASSETS,
        HOUSEHOLDID
    from 
        (select
        OWNERSHIPVALUE,
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPBUSINESSOWNERSHIP
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where CONFIRMED = 1) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
AFFLUENCEINDICATORIDENTIFIED_CTE as(
    select 
        count(*) AFFLUENCEINDICATOR_IDENTIFIED,
        SUM(RPASSETS) AFFLUENCEINDICATOR_IDENTIFIEDASSETS,
        HOUSEHOLDID
    from 
        (select
        RPASSETS,
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPAFFLUENCEINDICATOR
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where REJECTED = 0) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
AFFLUENCEINDICATORCONFIRMED_CTE as(
    select 
        count(*) AFFLUENCEINDICATOR_CONFIRMED,
        SUM(RPASSETS) AFFLUENCEINDICATOR_CONFIRMEDASSETS,
        HOUSEHOLDID
    from 
        (select
        RPASSETS,
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPAFFLUENCEINDICATOR
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where CONFIRMED = 1) as SUBQ
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
OTHERASSETIDENTIFIED_CTE as(
    select 
        count(*) OTHERASSETS_IDENTIFIED,
        SUM(VALUE) OTHERASSETS_IDENTIFIEDASSETS,
        HOUSEHOLDID
    from 
        (select
        VALUE,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPOTHERASSET
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where REJECTED = 0) as SUBQ
    group by HOUSEHOLDID
),
OTHERASSETCONFIRMED_CTE as(
    select 
        count(*) OTHERASSETS_CONFIRMED,
        SUM(VALUE) OTHERASSETS_CONFIRMEDASSETS,
        HOUSEHOLDID
    from 
        (select
        VALUE,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPOTHERASSET
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where CONFIRMED = 1) as SUBQ
    group by HOUSEHOLDID
),
INCOMECOMPENSATIONIDENTIFIED_CTE as(
    select 
        count(*) INCOME_IDENTIFIED,
        SUM(TOTALCOMPENSATION) INCOME_IDENTIFIEDASSETS,
        HOUSEHOLDID
    from 
        (select
        TOTALCOMPENSATION,
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPINCOMECOMPENSATION
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where REJECTED = 0) as INCOME
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
INCOMECOMPENSATIONCONFIRMED_CTE as(
    select 
        count(*) INCOME_CONFIRMED,
        SUM(TOTALCOMPENSATION) INCOME_CONFIRMEDASSETS,
        HOUSEHOLDID
    from 
        (select
        TOTALCOMPENSATION,
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPINCOMECOMPENSATION
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where CONFIRMED = 1) as INCOME
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
REALESTATEIDENTIFIED_CTE as(
    select 
        count(*) REALESTATE_IDENTIFIED,
        SUM(PROPERTYVALUATION) REALESTATE_IDENTIFIEDASSETS,
        HOUSEHOLDID
    from 
        (select
        PROPERTYVALUATION,
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPREALESTATE
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where REJECTED = 0) as INCOME
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
REALESTATECONFIRMED_CTE as(
    select 
        count(*) REALESTATE_CONFIRMED,
        SUM(PROPERTYVALUATION) REALESTATE_CONFIRMEDASSETS,
        HOUSEHOLDID
    from 
        (select
        PROPERTYVALUATION,
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPREALESTATE
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where CONFIRMED = 1) as INCOME
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
SECURITIESIDENTIFIED_CTE as(
    select 
        count(*) SECURITIES_IDENTIFIED,
        SUM(VALUEDIRECT) SECURITIES_IDENTIFIEDASSETS,
        HOUSEHOLDID
    from 
        (select
        VALUEDIRECT,
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPSECURITIES
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where REJECTED = 0) as INCOME
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
),
SECURITIESCONFIRMED_CTE as(
    select 
        count(*) SECURITIES_CONFIRMED,
        SUM(VALUEDIRECT) SECURITIES_CONFIRMEDASSETS,
        HOUSEHOLDID
    from 
        (select
        VALUEDIRECT,
        row_number() over(partition by PARTIALHASH, CONSTITUENT_CTE.HOUSEHOLDID order by DATECHANGED desc) as ROWNUMBER,
        PARTIALHASH,
        CONSTITUENT_CTE.HOUSEHOLDID
        from dbo.WPSECURITIES
        inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = WEALTHID
        where CONFIRMED = 1) as INCOME
    where ROWNUMBER = 1 or (PARTIALHASH = '' or PARTIALHASH is null)
    group by HOUSEHOLDID
)
select 
    CONSTITUENT_CTE.CONSTITUENTID,
    coalesce(REALESTATE_IDENTIFIEDASSETS, 0) REALESTATE_IDENTIFIEDASSETS,
    coalesce(REALESTATE_CONFIRMEDASSETS, 0) REALESTATE_CONFIRMEDASSETS,
    coalesce(REALESTATE_IDENTIFIED, 0) REALESTATE_IDENTIFIED,
    coalesce(REALESTATE_CONFIRMED, 0) REALESTATE_CONFIRMED,
    coalesce(BUSINESSOWNERSHIP_IDENTIFIEDASSETS, 0) BUSINESSOWNERSHIP_IDENTIFIEDASSETS,
    coalesce(BUSINESSOWNERSHIP_CONFIRMEDASSETS, 0) BUSINESSOWNERSHIP_CONFIRMEDASSETS,
    coalesce(BUSINESSOWNERSHIP_IDENTIFIED, 0) BUSINESSOWNERSHIP_IDENTIFIED,
    coalesce(BUSINESSOWNERSHIP_CONFIRMED, 0) BUSINESSOWNERSHIP_CONFIRMED,
    coalesce(SECURITIES_IDENTIFIEDASSETS, 0) SECURITIES_IDENTIFIEDASSETS,
    coalesce(SECURITIES_CONFIRMEDASSETS, 0) SECURITIES_CONFIRMEDASSETS,
    coalesce(SECURITIES_IDENTIFIED, 0) SECURITIES_IDENTIFIED,
    coalesce(SECURITIES_CONFIRMED, 0) SECURITIES_CONFIRMED,    
    coalesce(AFFLUENCEINDICATOR_IDENTIFIEDASSETS, 0) AFFLUENCEINDICATOR_IDENTIFIEDASSETS,
    coalesce(AFFLUENCEINDICATOR_CONFIRMEDASSETS, 0) AFFLUENCEINDICATOR_CONFIRMEDASSETS,
    coalesce(AFFLUENCEINDICATOR_IDENTIFIED, 0) AFFLUENCEINDICATOR_IDENTIFIED,
    coalesce(AFFLUENCEINDICATOR_CONFIRMED, 0) AFFLUENCEINDICATOR_CONFIRMED,    
    coalesce(INCOME_IDENTIFIEDASSETS, 0) INCOME_IDENTIFIEDASSETS,
    coalesce(INCOME_CONFIRMEDASSETS, 0) INCOME_CONFIRMEDASSETS,
    coalesce(INCOME_IDENTIFIED, 0) INCOME_IDENTIFIED,
    coalesce(INCOME_CONFIRMED, 0) INCOME_CONFIRMED,    
    coalesce(OTHERASSETS_IDENTIFIEDASSETS, 0) OTHERASSETS_IDENTIFIEDASSETS,
    coalesce(OTHERASSETS_CONFIRMEDASSETS, 0) OTHERASSETS_CONFIRMEDASSETS,
    coalesce(OTHERASSETS_IDENTIFIED, 0) OTHERASSETS_IDENTIFIED,
    coalesce(OTHERASSETS_CONFIRMED, 0) OTHERASSETS_CONFIRMED,    
    coalesce(BIOGRAPHICAL_IDENTIFIED, 0) BIOGRAPHICAL_IDENTIFIED,
    coalesce(BIOGRAPHICAL_CONFIRMED, 0) BIOGRAPHICAL_CONFIRMED,
    coalesce(POLITICALDONATION_IDENTIFIED, 0) POLITICALDONATION_IDENTIFIED,
    coalesce(POLITICALDONATION_CONFIRMED, 0) POLITICALDONATION_CONFIRMED,
    coalesce(PHILANTHROPICGIFT_IDENTIFIED, 0) PHILANTHROPICGIFT_IDENTIFIED,
    coalesce(PHILANTHROPICGIFT_CONFIRMED, 0) PHILANTHROPICGIFT_CONFIRMED,
    coalesce(NONPROFITAFFILIATION_IDENTIFIED, 0) NONPROFITAFFILIATION_IDENTIFIED,
    coalesce(NONPROFITAFFILIATION_CONFIRMED, 0) NONPROFITAFFILIATION_CONFIRMED,
    coalesce(PRIVATEFOUNDATION_IDENTIFIED, 0) PRIVATEFOUNDATION_IDENTIFIED,
    coalesce(PRIVATEFOUNDATION_CONFIRMED, 0) PRIVATEFOUNDATION_CONFIRMED,
    coalesce(AFFLUENCEINDICATOR_IDENTIFIEDASSETS, 0) +
        coalesce(BUSINESSOWNERSHIP_IDENTIFIEDASSETS, 0) +
        coalesce(OTHERASSETS_IDENTIFIEDASSETS, 0) +
        coalesce(INCOME_IDENTIFIEDASSETS, 0) +
        coalesce(REALESTATE_IDENTIFIEDASSETS, 0) +
        coalesce(SECURITIES_IDENTIFIEDASSETS, 0) as TOTALASSETS_IDENTIFIED,
    coalesce(AFFLUENCEINDICATOR_CONFIRMEDASSETS, 0) +
        coalesce(BUSINESSOWNERSHIP_CONFIRMEDASSETS, 0) +
        coalesce(OTHERASSETS_CONFIRMEDASSETS, 0) +
        coalesce(INCOME_CONFIRMEDASSETS, 0) +
        coalesce(REALESTATE_CONFIRMEDASSETS, 0) +
        coalesce(SECURITIES_CONFIRMEDASSETS, 0) as TOTALASSETS_CONFIRMED
from CONSTITUENT_CTE
left join BIOGRAPHICALIDENTIFIED_CTE on BIOGRAPHICALIDENTIFIED_CTE.HOUSEHOLDID = CONSTITUENTID
left join BIOGRAPHICALCONFIRMED_CTE on BIOGRAPHICALCONFIRMED_CTE.HOUSEHOLDID = CONSTITUENTID
left join POLITICALDONATIONIDENTIFIED_CTE on POLITICALDONATIONIDENTIFIED_CTE.HOUSEHOLDID = CONSTITUENTID
left join POLITICALDONATIONCONFIRMED_CTE on POLITICALDONATIONCONFIRMED_CTE.HOUSEHOLDID = CONSTITUENTID
left join PHILANTRHOPICGIFTIDENTIFIED_CTE on PHILANTRHOPICGIFTIDENTIFIED_CTE.HOUSEHOLDID = CONSTITUENTID
left join PHILANTRHOPICGIFTCONFIRMED_CTE on PHILANTRHOPICGIFTCONFIRMED_CTE.HOUSEHOLDID = CONSTITUENTID
left join NONPROFITAFFILIATIONIDENTIFIED_CTE on NONPROFITAFFILIATIONIDENTIFIED_CTE.HOUSEHOLDID = CONSTITUENTID
left join NONPROFITAFFILIATIONCONFIRMED_CTE on NONPROFITAFFILIATIONCONFIRMED_CTE.HOUSEHOLDID = CONSTITUENTID
left join PRIVATEFOUNDATIONIDENTIFIED_CTE on PRIVATEFOUNDATIONIDENTIFIED_CTE.HOUSEHOLDID = CONSTITUENTID
left join PRIVATEFOUNDATIONCONFIRMED_CTE on PRIVATEFOUNDATIONCONFIRMED_CTE.HOUSEHOLDID = CONSTITUENTID
left join BUSINESSOWNERSHIPIDENTIFIED_CTE on BUSINESSOWNERSHIPIDENTIFIED_CTE.HOUSEHOLDID = CONSTITUENTID
left join BUSINESSOWNERSHIPCONFIRMED_CTE on BUSINESSOWNERSHIPCONFIRMED_CTE.HOUSEHOLDID = CONSTITUENTID
left join AFFLUENCEINDICATORIDENTIFIED_CTE on AFFLUENCEINDICATORIDENTIFIED_CTE.HOUSEHOLDID = CONSTITUENTID
left join AFFLUENCEINDICATORCONFIRMED_CTE on AFFLUENCEINDICATORCONFIRMED_CTE.HOUSEHOLDID = CONSTITUENTID
left join OTHERASSETIDENTIFIED_CTE on OTHERASSETIDENTIFIED_CTE.HOUSEHOLDID = CONSTITUENTID
left join OTHERASSETCONFIRMED_CTE on OTHERASSETCONFIRMED_CTE.HOUSEHOLDID = CONSTITUENTID
left join INCOMECOMPENSATIONIDENTIFIED_CTE on INCOMECOMPENSATIONIDENTIFIED_CTE.HOUSEHOLDID = CONSTITUENTID
left join INCOMECOMPENSATIONCONFIRMED_CTE on INCOMECOMPENSATIONCONFIRMED_CTE.HOUSEHOLDID = CONSTITUENTID
left join REALESTATEIDENTIFIED_CTE on REALESTATEIDENTIFIED_CTE.HOUSEHOLDID = CONSTITUENTID
left join REALESTATECONFIRMED_CTE on REALESTATECONFIRMED_CTE.HOUSEHOLDID = CONSTITUENTID
left join SECURITIESIDENTIFIED_CTE on SECURITIESIDENTIFIED_CTE.HOUSEHOLDID = CONSTITUENTID
left join SECURITIESCONFIRMED_CTE on SECURITIESCONFIRMED_CTE.HOUSEHOLDID = CONSTITUENTID