V_QUERY_HOUSEHOLDCONSTITUENT
Rolls up household members into a household view.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| ISORGANIZATION | bit | Is organization | |
| KEYNAME | nvarchar(100) | Last/Organization/Group/Household name | |
| FIRSTNAME | nvarchar(50) | First name | |
| MIDDLENAME | nvarchar(50) | Middle name | |
| MAIDENNAME | nvarchar(100) | Maiden name | |
| NICKNAME | nvarchar(50) | Nickname | |
| MARITALSTATUSCODEID_TRANSLATION | nvarchar(100) | yes | Marital status |
| LOOKUPID | nvarchar(100) | yes | Lookup ID |
| TITLECODEID_TRANSLATION | nvarchar(100) | yes | Title |
| SUFFIXCODEID_TRANSLATION | nvarchar(100) | yes | Suffix |
| GENDER | nvarchar(7) | yes | Gender |
| BIRTHDATE | UDT_FUZZYDATE | Birth date | |
| DECEASEDDATE | UDT_FUZZYDATE | yes | Deceased date |
| ISINACTIVE | bit | Inactive | |
| GIVESANONYMOUSLY | bit | Gives anonymously | |
| DECEASED | bit | yes | Deceased |
| AGE | int | yes | Age |
| DECEASEDYEARS | int | yes | Deceased years |
| NAME | nvarchar(154) | yes | Name |
| WEBADDRESS | UDT_WEBADDRESS | Web address | |
| KEYNAMEPREFIX | nvarchar(50) | Key name prefix | |
| DONOTMAIL | bit | Do not mail | |
| DONOTEMAIL | bit | Do not email | |
| DONOTPHONE | bit | Do not phone | |
| NETCOMMUNITYMEMBER | bit | Blackbaud Internet Solutions member | |
| INDUSTRYCODEID_TRANSLATION | nvarchar(100) | yes | Industry |
| NUMEMPLOYEES | int | yes | Number of employees |
| NUMSUBSIDIARIES | int | yes | Number of subsidiaries |
| PARENTCORPID | uniqueidentifier | yes | Parent corporation ID |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| PICTURE | varbinary | yes | Picture |
| SPOUSE_ID | uniqueidentifier | yes | Spouse ID |
| PRIMARYBUSINESS_ID | uniqueidentifier | yes | Primary business ID |
| PRIMARYCONTACT_ID | uniqueidentifier | yes | Primary contact ID |
| KPICONTEXTRECORDID | nvarchar(100) | yes | KPIs context record ID |
| JOBTITLE | varchar(1) | Job title | |
| CONSTITUENTTYPE | nvarchar(100) | yes | Type |
| ISPRIMARYORGANIZATION | bit | yes | Is primary organization |
| DECEASEDCONFIRMATION | nvarchar(17) | yes | Deceased confirmation |
| DECEASEDSOURCECODEID_TRANSLATION | nvarchar(100) | yes | Deceased source |
| ISREVIEWED | bit | yes | Deceased is reviewed |
| TITLE2CODEID_TRANSLATION | nvarchar(100) | yes | Title 2 |
| SUFFIX2CODEID_TRANSLATION | nvarchar(100) | yes | Suffix 2 |
| CONSTITUENTINACTIVEDETAIL_REASONCODE | nvarchar(63) | yes | Inactive reason |
| CONSTITUENTINACTIVEDETAIL_DETAILS | nvarchar(300) | yes | Inactive details |
| INDUSTRYCODEID | uniqueidentifier | yes | |
| MARITALSTATUSCODEID | uniqueidentifier | yes | |
| TITLECODEID | uniqueidentifier | yes | |
| SUFFIXCODEID | uniqueidentifier | yes | |
| CONSTITUENTTYPECODEID | uniqueidentifier | yes | |
| DECEASEDSOURCECODEID | uniqueidentifier | yes | |
| TITLE2CODEID | uniqueidentifier | yes | |
| SUFFIX2CODEID | uniqueidentifier | yes | |
| CONSTITUENTINACTIVITYREASONCODEID | uniqueidentifier | yes | |
| GENDERCODEID_TRANSLATION | nvarchar(100) | yes | |
| GENDERCODEID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 5/3/2024 2:18:26 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_HOUSEHOLDCONSTITUENT AS
with HOUSEHOLD_GROUPMEMBER as
(
select
GROUPMEMBER.MEMBERID CONSTITUENTID
from GROUPDATA
inner join dbo.GROUPMEMBER on GROUPMEMBER.GROUPID = GROUPDATA.ID
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
where ((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()))
and
GROUPDATA.GROUPTYPECODE = 0
)
select
CONSTITUENT.ID,
CONSTITUENT.ISORGANIZATION,
CONSTITUENT.KEYNAME,
CONSTITUENT.FIRSTNAME,
CONSTITUENT.MIDDLENAME,
CONSTITUENT.MAIDENNAME,
CONSTITUENT.NICKNAME,
(select MARITALSTATUSCODE.DESCRIPTION from dbo.MARITALSTATUSCODE where MARITALSTATUSCODE.ID = CONSTITUENT.MARITALSTATUSCODEID) as [MARITALSTATUSCODEID_TRANSLATION],
CONSTITUENT.LOOKUPID,
(select TITLECODE.DESCRIPTION from dbo.TITLECODE where TITLECODE.ID = CONSTITUENT.TITLECODEID) as [TITLECODEID_TRANSLATION],
(select SUFFIXCODE.DESCRIPTION from dbo.SUFFIXCODE where SUFFIXCODE.ID = CONSTITUENT.SUFFIXCODEID) as [SUFFIXCODEID_TRANSLATION],
CONSTITUENT.GENDER,
CONSTITUENT.BIRTHDATE,
DECEASEDCONSTITUENT.DECEASEDDATE,
CONSTITUENT.ISINACTIVE,
CONSTITUENT.GIVESANONYMOUSLY,
convert(bit, case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end) as DECEASED,
dbo.UFN_CONSTITUENT_GETAGE(CONSTITUENT.ID) as AGE,
DECEASEDCONSTITUENT.DECEASEDYEARS,
CONSTITUENT.NAME,
CONSTITUENT.WEBADDRESS,
CONSTITUENT.KEYNAMEPREFIX,
CONSTITUENT.DONOTMAIL,
CONSTITUENT.DONOTEMAIL,
CONSTITUENT.DONOTPHONE,
CONSTITUENT.NETCOMMUNITYMEMBER,
(select INDUSTRYCODE.DESCRIPTION from dbo.INDUSTRYCODE where INDUSTRYCODE.ID = ORGANIZATIONDATA.INDUSTRYCODEID) as [INDUSTRYCODEID_TRANSLATION],
coalesce(ORGANIZATIONDATA.NUMEMPLOYEES, 0 ) as NUMEMPLOYEES,
coalesce(ORGANIZATIONDATA.NUMSUBSIDIARIES, 0) as NUMSUBSIDIARIES,
ORGANIZATIONDATA.PARENTCORPID,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
CONSTITUENT.DATEADDED,
CONSTITUENT.DATECHANGED,
CONSTITUENT.TSLONG,
CONSTITUENT.PICTURE,
(select SPOUSE.RECIPROCALCONSTITUENTID from dbo.RELATIONSHIP as SPOUSE where SPOUSE.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and SPOUSE.ISSPOUSE = 1) as [SPOUSE_ID],
(select PRIMARYBUSINESS.RECIPROCALCONSTITUENTID from dbo.RELATIONSHIP as PRIMARYBUSINESS where PRIMARYBUSINESS.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and PRIMARYBUSINESS.ISPRIMARYBUSINESS = 1 and CONSTITUENT.ISORGANIZATION = 0) as [PRIMARYBUSINESS_ID],
(select CONTACT.RECIPROCALCONSTITUENTID from dbo.RELATIONSHIP as CONTACT where CONTACT.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and CONTACT.ISPRIMARYCONTACT = 1 and CONSTITUENT.ISORGANIZATION = 1) as [PRIMARYCONTACT_ID],
cast(CONSTITUENT.ID as nvarchar(100)) as [KPICONTEXTRECORDID],
'' as JOBTITLE,
(select CONSTITUENTTYPECODE.DESCRIPTION from dbo.CONSTITUENTTYPECODE where CONSTITUENTTYPECODE.ID =
(case when CONSTITUENT.ISORGANIZATION = 1 then
'AEFFA312-BE88-4446-9FD1-7E2B07CDB973'
when CONSTITUENT.ISGROUP = 1 then
(case when GROUPDATA.GROUPTYPECODE = 0 then
'5777F3C1-FBC1-47F7-BF69-4789DC29D958'
else
'0E9D886B-4016-49D2-A220-809EBC38BB15'
end)
else
'053731F1-CE72-441E-B0D2-C90BEE6E691C'
end)
) CONSTITUENTTYPE,
ORGANIZATIONDATA.ISPRIMARY as ISPRIMARYORGANIZATION,
DECEASEDCONSTITUENT.DECEASEDCONFIRMATION,
(select DECEASEDSOURCECODE.DESCRIPTION from dbo.DECEASEDSOURCECODE where DECEASEDSOURCECODE.ID = DECEASEDCONSTITUENT.DECEASEDSOURCECODEID) as [DECEASEDSOURCECODEID_TRANSLATION],
DECEASEDCONSTITUENT.ISREVIEWED,
(select TITLECODE.DESCRIPTION from dbo.TITLECODE where TITLECODE.ID = CONSTITUENT.TITLE2CODEID) as [TITLE2CODEID_TRANSLATION],
(select SUFFIXCODE.DESCRIPTION from dbo.SUFFIXCODE where SUFFIXCODE.ID = CONSTITUENT.SUFFIX2CODEID) as [SUFFIX2CODEID_TRANSLATION],
(select CODE + ' - ' + DESCRIPTION from dbo.CONSTITUENTINACTIVITYREASONCODE where ID = CONSTITUENTINACTIVEDETAIL.CONSTITUENTINACTIVITYREASONCODEID) as [CONSTITUENTINACTIVEDETAIL_REASONCODE],
CONSTITUENTINACTIVEDETAIL.DETAILS as [CONSTITUENTINACTIVEDETAIL_DETAILS],
[ORGANIZATIONDATA].[INDUSTRYCODEID],
[CONSTITUENT].[MARITALSTATUSCODEID],
[CONSTITUENT].[TITLECODEID],
[CONSTITUENT].[SUFFIXCODEID],
(case when CONSTITUENT.ISORGANIZATION = 1 then
cast('AEFFA312-BE88-4446-9FD1-7E2B07CDB973' as uniqueidentifier)
when CONSTITUENT.ISGROUP = 1 then
(case when GROUPDATA.GROUPTYPECODE = 0 then
cast('5777F3C1-FBC1-47F7-BF69-4789DC29D958' as uniqueidentifier)
else
cast('0E9D886B-4016-49D2-A220-809EBC38BB15' as uniqueidentifier)
end)
else
cast('053731F1-CE72-441E-B0D2-C90BEE6E691C' as uniqueidentifier)
end) as [CONSTITUENTTYPECODEID],
[DECEASEDCONSTITUENT].[DECEASEDSOURCECODEID],
[CONSTITUENT].[TITLE2CODEID],
[CONSTITUENT].[SUFFIX2CODEID],
[CONSTITUENTINACTIVEDETAIL].[CONSTITUENTINACTIVITYREASONCODEID],
(select GENDERCODE.DESCRIPTION from dbo.GENDERCODE where GENDERCODE.ID = CONSTITUENT.GENDERCODEID) as [GENDERCODEID_TRANSLATION],
[CONSTITUENT].[GENDERCODEID]
/*#EXTENSION*/
from
dbo.CONSTITUENT
left join dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = CONSTITUENT.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = CONSTITUENT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = CONSTITUENT.CHANGEDBYID
left join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
left join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
left join dbo.CONSTITUENTINACTIVEDETAIL on CONSTITUENT.ID = CONSTITUENTINACTIVEDETAIL.ID
left join HOUSEHOLD_GROUPMEMBER on HOUSEHOLD_GROUPMEMBER.CONSTITUENTID = CONSTITUENT.ID
where
CONSTITUENT.ISORGANIZATION = 1
or CONSTITUENT.ISGROUP = 1
or HOUSEHOLD_GROUPMEMBER.CONSTITUENTID is null