v_DIM_CONSTITUENTLOOKUP
The constituent lookup dimension contains detail information about constituents.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTSYSTEMID | uniqueidentifier | yes | BBDW.[DIM_CONSTITUENT].[CONSTITUENTSYSTEMID] |
| CONSTITUENTLOOKUPID | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[CONSTITUENTLOOKUPID] |
| CONSTITUENTDIMID | int | BBDW.[DIM_CONSTITUENT].[CONSTITUENTDIMID] | |
| FULLNAME | nvarchar(154) | yes | BBDW.[DIM_CONSTITUENT].[FULLNAME] |
| TITLE | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[TITLE] |
| TITLE2 | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[TITLE2] |
| FIRSTNAME | nvarchar(50) | yes | BBDW.[DIM_CONSTITUENT].[FIRSTNAME] |
| MIDDLENAME | nvarchar(50) | yes | BBDW.[DIM_CONSTITUENT].[MIDDLENAME] |
| NICKNAME | nvarchar(50) | yes | BBDW.[DIM_CONSTITUENT].[NICKNAME] |
| KEYNAMEPREFIX | nvarchar(50) | yes | BBDW.[DIM_CONSTITUENT].[KEYNAMEPREFIX] |
| KEYNAME | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[KEYNAME] |
| MAIDENNAME | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[MAIDENNAME] |
| SUFFIX | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[SUFFIX] |
| SUFFIX2 | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[SUFFIX2] |
| WEBADDRESS | UDT_WEBADDRESS | BBDW.[DIM_CONSTITUENT].[WEBADDRESS] | |
| BIRTHDATE | UDT_FUZZYDATE | BBDW.[DIM_CONSTITUENT].[BIRTHDATE] | |
| CONSTITUENTAGE | int | yes | BBDW.[DIM_CONSTITUENT].[AGE] |
| PRIMARYADDRESSTYPE | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSTYPE].[CONSTITUENTADDRESSTYPE] |
| PRIMARYADDRESS | nvarchar(150) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESS] |
| PRIMARYADDRESSLINE1 | nvarchar(150) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSLINE1] |
| PRIMARYADDRESSLINE2 | nvarchar(150) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSLINE2] |
| PRIMARYADDRESSLINE3 | nvarchar(150) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSLINE3] |
| PRIMARYADDRESSLINE4 | nvarchar(150) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSLINE4] |
| PRIMARYADDRESSLINE5 | nvarchar(150) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSLINE5] |
| PRIMARYADDRESSCITY | nvarchar(50) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSCITY] |
| PRIMARYADDRESSSTATE | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSSTATE] |
| PRIMARYADDRESSSTATEABBREVIATION | nvarchar(50) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSSTATEABBREVIATION] |
| PRIMARYADDRESSCOUNTRY | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSCOUNTRY] |
| PRIMARYADDRESSCOUNTRYABBREVIATION | nvarchar(5) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSCOUNTRYABBREVIATION] |
| PRIMARYADDRESSPOSTCODE | nvarchar(12) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSPOSTCODE] |
| PRIMARYADDRESSCART | nvarchar(10) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSCART] |
| PRIMARYADDRESSDPC | nvarchar(8) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSDPC] |
| PRIMARYADDRESSLOT | nvarchar(5) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYADDRESSLOT] |
| DONOTMAIL | bit | yes | BBDW.[DIM_CONSTITUENT].[DONOTMAIL] |
| PRIMARYADDRESSISCONFIDENTIAL | bit | yes | BBDW.[DIM_CONSTITUENTADDRESSFLAG].[ISCONFIDENTIAL] |
| PRIMARYADDRESSCOUNTY | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[COUNTY] |
| PRIMARYADDRESSCONGRESSIONALDISTRICT | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[CONGRESSIONALDISTRICT] |
| PRIMARYADDRESSSTATEHOUSEDISTRICT | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[STATEHOUSEDISTRICT] |
| PRIMARYADDRESSLOCALPRECINCT | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[LOCALPRECINCT] |
| PRIMARYADDRESSINFOSOURCE | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[INFOSOURCE] |
| PRIMARYADDRESSREGION | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[REGION] |
| PRIMARYADDRESSORIGIN | nvarchar(10) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[ORIGIN] |
| INCOME | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[INCOME] |
| RELIGION | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[RELIGION] |
| TARGET | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[TARGET] |
| GENDER | nvarchar(10) | yes | BBDW.[DIM_CONSTITUENT].[GENDER] |
| GENDERDESCRIPTION | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[GENDERDESCRIPTION] |
| MARITALSTATUS | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[MARITALSTATUS] |
| ORIGINATION | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[ORIGINATION] |
| GIVESANONYMOUSLY | bit | yes | BBDW.[DIM_CONSTITUENT].[GIVESANONYMOUSLY] |
| ISACTIVE | bit | yes | BBDW.[DIM_CONSTITUENT].[ISACTIVE] |
| ISCONSTITUENT | bit | yes | BBDW.[DIM_CONSTITUENT].[ISCONSTITUENT] |
| ISDECEASED | bit | yes | BBDW.[DIM_CONSTITUENT].[ISDECEASED] |
| ISINDIVIDUAL | bit | yes | BBDW.[DIM_CONSTITUENT].[ISINDIVIDUAL] |
| ISHOUSEHOLD | bit | yes | BBDW.[DIM_CONSTITUENT].[ISHOUSEHOLD] |
| ISGROUP | bit | yes | BBDW.[DIM_CONSTITUENT].[ISGROUP] |
| ISNETCOMMUNITYMEMBER | bit | yes | BBDW.[DIM_CONSTITUENT].[ISNETCOMMUNITYMEMBER] |
| ISORGANIZATION | bit | yes | BBDW.[DIM_CONSTITUENT].[ISORGANIZATION] |
| ISACTIVEBOARDMEMBER | bit | yes | BBDW.[DIM_CONSTITUENT].[ISACTIVEBOARDMEMBER] |
| ISACTIVECOMMITTEE | bit | yes | BBDW.[DIM_CONSTITUENT].[ISACTIVECOMMITTEE] |
| ISACTIVEFUNDRAISER | bit | yes | BBDW.[DIM_CONSTITUENT].[ISACTIVEFUNDRAISER] |
| ISACTIVEPROSPECT | bit | yes | BBDW.[DIM_CONSTITUENT].[ISACTIVEPROSPECT] |
| ISACTIVESTAFF | bit | yes | BBDW.[DIM_CONSTITUENT].[ISACTIVESTAFF] |
| ISACTIVESPONSOR | bit | yes | BBDW.[DIM_CONSTITUENT].[ISACTIVESPONSOR] |
| ISACTIVEVOLUNTEER | bit | yes | BBDW.[DIM_CONSTITUENT].[ISACTIVEVOLUNTEER] |
| PRIMARYPHONENUMBER | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYPHONE] |
| DONOTCALL | bit | yes | BBDW.[DIM_CONSTITUENT].[DONOTCALL] |
| PRIMARYEMAILADDRESS | UDT_EMAILADDRESS | yes | BBDW.[DIM_CONSTITUENT].[PRIMARYEMAILADDRESS] |
| DONOTEMAIL | bit | yes | BBDW.[DIM_CONSTITUENT].[DONOTEMAIL] |
| ADDEDDATEDIMID | int | yes | BBDW.[DIM_CONSTITUENT].[ADDEDDATEDIMID] |
| ADDEDDATE | datetime | yes | BBDW.[DIM_CONSTITUENT].[ADDEDDATE] |
| CHANGEDDATEDIMID | int | yes | BBDW.[DIM_CONSTITUENT].[CHANGEDDATEDIMID] |
| CHANGEDDATE | datetime | yes | BBDW.[DIM_CONSTITUENT].[CHANGEDDATE] |
Definition
Copy
CREATE view [BBDW].[v_DIM_CONSTITUENTLOOKUP] as
select
c.[CONSTITUENTSYSTEMID],
c.[CONSTITUENTLOOKUPID],
c.[CONSTITUENTDIMID],
c.[FULLNAME],
c.[TITLE],
c.[TITLE2],
c.[FIRSTNAME],
c.[MIDDLENAME],
c.[NICKNAME],
c.[KEYNAMEPREFIX],
c.[KEYNAME],
c.[MAIDENNAME],
c.[SUFFIX],
c.[SUFFIX2],
c.[WEBADDRESS],
c.[BIRTHDATE],
c.[AGE] [CONSTITUENTAGE],
cat.[CONSTITUENTADDRESSTYPE] [PRIMARYADDRESSTYPE],
c.[PRIMARYADDRESS],
c.[PRIMARYADDRESSLINE1],
c.[PRIMARYADDRESSLINE2],
c.[PRIMARYADDRESSLINE3],
c.[PRIMARYADDRESSLINE4],
c.[PRIMARYADDRESSLINE5],
c.[PRIMARYADDRESSCITY],
c.[PRIMARYADDRESSSTATE],
c.[PRIMARYADDRESSSTATEABBREVIATION],
c.[PRIMARYADDRESSCOUNTRY],
c.[PRIMARYADDRESSCOUNTRYABBREVIATION],
c.[PRIMARYADDRESSPOSTCODE],
c.[PRIMARYADDRESSCART],
c.[PRIMARYADDRESSDPC],
c.[PRIMARYADDRESSLOT],
c.[DONOTMAIL],
caf.[ISCONFIDENTIAL] [PRIMARYADDRESSISCONFIDENTIAL],
cad.[COUNTY] [PRIMARYADDRESSCOUNTY],
cad.[CONGRESSIONALDISTRICT] [PRIMARYADDRESSCONGRESSIONALDISTRICT],
cad.[STATEHOUSEDISTRICT] [PRIMARYADDRESSSTATEHOUSEDISTRICT],
cad.[LOCALPRECINCT] [PRIMARYADDRESSLOCALPRECINCT],
cad.[INFOSOURCE] [PRIMARYADDRESSINFOSOURCE],
cad.[REGION] [PRIMARYADDRESSREGION],
cad.[ORIGIN] [PRIMARYADDRESSORIGIN],
c.[INCOME],
c.[RELIGION],
c.[TARGET],
c.[GENDER],
c.[GENDERDESCRIPTION],
c.[MARITALSTATUS],
c.[ORIGINATION],
c.[GIVESANONYMOUSLY],
c.[ISACTIVE],
c.[ISCONSTITUENT],
c.[ISDECEASED],
c.[ISINDIVIDUAL],
c.[ISHOUSEHOLD],
c.[ISGROUP],
c.[ISNETCOMMUNITYMEMBER],
c.[ISORGANIZATION],
c.[ISACTIVEBOARDMEMBER],
c.[ISACTIVECOMMITTEE],
c.[ISACTIVEFUNDRAISER],
c.[ISACTIVEPROSPECT],
c.[ISACTIVESTAFF],
c.[ISACTIVESPONSOR],
c.[ISACTIVEVOLUNTEER],
c.[PRIMARYPHONE] as [PRIMARYPHONENUMBER],
c.[DONOTCALL],
c.[PRIMARYEMAILADDRESS],
c.[DONOTEMAIL],
c.[ADDEDDATEDIMID],
c.[ADDEDDATE],
c.[CHANGEDDATEDIMID],
c.[CHANGEDDATE]
from BBDW.[DIM_CONSTITUENT] c
left join BBDW.[DIM_CONSTITUENTADDRESSDETAIL] cad on c.[CONSTITUENTADDRESSDETAILDIMID] = cad.[CONSTITUENTADDRESSDETAILDIMID]
left join BBDW.[DIM_CONSTITUENTADDRESSTYPE] cat on c.[CONSTITUENTADDRESSTYPEDIMID] = cat.[CONSTITUENTADDRESSTYPEDIMID]
left join BBDW.[DIM_CONSTITUENTADDRESSFLAG] caf on c.[CONSTITUENTADDRESSFLAGDIMID] = caf.[CONSTITUENTADDRESSFLAGDIMID];