V_QUERY_RESEARCHGROUPADDMEMBERS

Fields

Field Field Type Null Description
ID uniqueidentifier
KEYNAME nvarchar(100)
FIRSTNAME nvarchar(50)
AGE int yes
DATEADDED datetime
LASTSYNCDATE datetime yes
RE7LINKED int
SPOUSENAME nvarchar(154) yes
PROSPECTMANAGERNAME nvarchar(154) yes
FIRSTGIFTAMOUNT money yes
FIRSTGIFTDATE datetime yes
FIRSTGIFTDESIGNATION nvarchar(100) yes
FIRSTGIFTTYPE nvarchar(100) yes
LARGESTGIFTAMOUNT money yes
LARGESTGIFTDATE datetime yes
LARGESTGIFTDESIGNATION nvarchar(100) yes
LARGESTGIFTTYPE nvarchar(100) yes
LATESTGIFTAMOUNT money yes
LATESTGIFTDATE datetime yes
LATESTGIFTDESIGNATION nvarchar(100) yes
LATESTGIFTTYPE nvarchar(100) yes
TOTALGIFTSGIVEN int yes
TOTALGIFTAMOUNT money yes
PHILANTHROPICGIFTNUMBERCONFIRMED int yes
PHILANTHROPICGIFTNUMBER int yes
POLITICALDONATIONNUMBERCONFIRMED int yes
POLITICALDONATIONNUMBER int yes
BUSINESSOWNERSHIPASSETS money yes
BUSINESSOWNERSHIPASSETSCONFIRMED money yes
TOTALASSETSCONFIRMED money yes
ESTIMATEDWEALTHVALUE money yes
TOTALASSETS money yes
INCOMECOMPENSATIONASSETSCONFIRMED money yes
INCOMECOMPENSATIONASSETS money yes
WEALTHPOINTDATE datetime yes
MAJORGIVINGCAPACITYVALUE money yes
OTHERASSETSCONFIRMED money yes
REALESTATEASSETSCONFIRMED money yes
REALESTATEASSETS money yes
SECURITIESASSETSCONFIRMED money yes
SECURITIESASSETS money yes
AFFLUENCEINDICATORNUMBERCONFIRMED int yes
AFFLUENCEINDICATORNUMBER int yes
MAJORGIVINGCAPACITYBASISVALUE nvarchar(250) yes

Definition

Copy 
/*
Generated by Blackbaud AppFx Platform
Date:  7/27/2018 10:10:29 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.182.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RESEARCHGROUPADDMEMBERS AS



        select
          CONSTITUENT.ID,
          CONSTITUENT.KEYNAME,
          CONSTITUENT.FIRSTNAME,
          (case when CONSTITUENT.BIRTHDATE <> '00000000'
            then dbo.UFN_AGEFROMFUZZYDATE(CONSTITUENT.BIRTHDATE, GETDATE())
            else null
          end) as AGE,
          CONSTITUENT.DATEADDED,
          RE7INTEGRATIONCONSTITUENTMAP.LASTSYNCDATE,
          case when RE7INTEGRATIONCONSTITUENTMAP.ID is null then 0 else 1 end RE7LINKED,
          SPOUSE.NAME SPOUSENAME,
          PROSPECTMANAGER.NAME PROSPECTMANAGERNAME,
          RE7INTEGRATIONGIVINGSUMMARY.FIRSTGIFTAMOUNT,
          RE7INTEGRATIONGIVINGSUMMARY.FIRSTGIFTDATE,
          RE7INTEGRATIONGIVINGSUMMARY.FIRSTGIFTDESIGNATION,
          RE7INTEGRATIONGIVINGSUMMARY.FIRSTGIFTTYPE,
          RE7INTEGRATIONGIVINGSUMMARY.LARGESTGIFTAMOUNT,
          RE7INTEGRATIONGIVINGSUMMARY.LARGESTGIFTDATE,
          RE7INTEGRATIONGIVINGSUMMARY.LARGESTGIFTDESIGNATION,       
          RE7INTEGRATIONGIVINGSUMMARY.LARGESTGIFTTYPE,
          RE7INTEGRATIONGIVINGSUMMARY.LATESTGIFTAMOUNT,
          RE7INTEGRATIONGIVINGSUMMARY.LATESTGIFTDATE,
          RE7INTEGRATIONGIVINGSUMMARY.LATESTGIFTDESIGNATION,
          RE7INTEGRATIONGIVINGSUMMARY.LATESTGIFTTYPE,
          RE7INTEGRATIONGIVINGSUMMARY.TOTALGIFTSGIVEN,
          RE7INTEGRATIONGIVINGSUMMARY.TOTALGIFTAMOUNT,
          COALESCE(WEALTH.PHILANTHROPICGIFTNUMBERCONFIRMED, 0) as PHILANTHROPICGIFTNUMBERCONFIRMED,
          COALESCE(WEALTH.PHILANTHROPICGIFTNUMBER, 0) as PHILANTHROPICGIFTNUMBER,
          COALESCE(WEALTH.POLITICALDONATIONNUMBERCONFIRMED, 0) as POLITICALDONATIONNUMBERCONFIRMED,
          COALESCE(WEALTH.POLITICALDONATIONNUMBER, 0) as POLITICALDONATIONNUMBER,
          COALESCE(WEALTH.BUSINESSOWNERSHIPASSETS, 0) as BUSINESSOWNERSHIPASSETS,
          COALESCE(WEALTH.BUSINESSOWNERSHIPASSETSCONFIRMED, 0) as BUSINESSOWNERSHIPASSETSCONFIRMED,
          COALESCE(WEALTH.TOTALASSETSCONFIRMED, 0) as TOTALASSETSCONFIRMED,
          COALESCE(WEALTHCAPACITY.ESTIMATEDWEALTHVALUE, 0) as ESTIMATEDWEALTHVALUE,
          COALESCE(WEALTH.TOTALASSETS, 0) as TOTALASSETS,
          COALESCE(WEALTH.INCOMECOMPENSATIONASSETSCONFIRMED, 0) as INCOMECOMPENSATIONASSETSCONFIRMED,
          COALESCE(WEALTH.INCOMECOMPENSATIONASSETS, 0) as INCOMECOMPENSATIONASSETS,
          WEALTH.WEALTHPOINTDATE,
          COALESCE(WEALTHCAPACITY.MAJORGIVINGCAPACITYVALUE, 0) as MAJORGIVINGCAPACITYVALUE,
          COALESCE(WEALTH.OTHERASSETSCONFIRMED, 0) as OTHERASSETSCONFIRMED,
          COALESCE(WEALTH.REALESTATEASSETSCONFIRMED, 0) as REALESTATEASSETSCONFIRMED,
          COALESCE(WEALTH.REALESTATEASSETS, 0) as REALESTATEASSETS,
          COALESCE(WEALTH.SECURITIESASSETSCONFIRMED, 0) as SECURITIESASSETSCONFIRMED,
          COALESCE(WEALTH.SECURITIESASSETS, 0) as SECURITIESASSETS,
          COALESCE(WEALTH.AFFLUENCEINDICATORNUMBERCONFIRMED, 0) as AFFLUENCEINDICATORNUMBERCONFIRMED,
          COALESCE(WEALTH.AFFLUENCEINDICATORNUMBER, 0) as AFFLUENCEINDICATORNUMBER,
          COALESCE(WEALTHCAPACITY.MAJORGIVINGCAPACITYBASISVALUE, '') as MAJORGIVINGCAPACITYBASISVALUE
        from 
            dbo.CONSTITUENT
        left join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID                
        left join dbo.RE7INTEGRATIONCONSTITUENTMAP on CONSTITUENT.ID = RE7INTEGRATIONCONSTITUENTMAP.ID
        left join dbo.RELATIONSHIP SPOUSERELATIONSHIP on CONSTITUENT.ID = SPOUSERELATIONSHIP.RELATIONSHIPCONSTITUENTID 
            and SPOUSERELATIONSHIP.ISSPOUSE = 1
        left join dbo.PROSPECT on CONSTITUENT.ID = PROSPECT.ID
        left join dbo.RE7INTEGRATIONGIVINGSUMMARY on RE7INTEGRATIONGIVINGSUMMARY.ID = CONSTITUENT.ID
        left join dbo.WEALTH on WEALTH.ID = CONSTITUENT.ID
        left join dbo.WEALTHCAPACITY on WEALTHCAPACITY.ID = CONSTITUENT.ID
        left join dbo.CONSTITUENT PROSPECTMANAGER on PROSPECTMANAGER.ID = PROSPECT.PROSPECTMANAGERFUNDRAISERID
        left join dbo.CONSTITUENT SPOUSE on SPOUSE.ID = SPOUSERELATIONSHIP.RECIPROCALCONSTITUENTID
        where (CONSTITUENT.ISCONSTITUENT = 1
              and CONSTITUENT.ISORGANIZATION = 0
              and CONSTITUENT.ISGROUP = 0)