V_QUERY_BBDW_CONSTITUENT_NORELATIONSHIPSNORECOGNITION
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTDIMID | int | ||
| CONSTITUENTSYSTEMID | uniqueidentifier | yes | |
| CONSTITUENTLOOKUPID | nvarchar(100) | yes | |
| FULLNAME | nvarchar(154) | yes | |
| FIRSTNAME | nvarchar(50) | yes | |
| MIDDLENAME | nvarchar(50) | yes | |
| KEYNAME | nvarchar(100) | yes | |
| BIRTHDATE | UDT_FUZZYDATE | ||
| AGE | int | yes | |
| ISDECEASED | bit | yes | |
| DONOTMAIL | bit | yes | |
| DONOTEMAIL | bit | yes | |
| DONOTCALL | bit | yes | |
| GIVESANONYMOUSLY | bit | yes | |
| TITLE | nvarchar(100) | yes | |
| SUFFIX | nvarchar(100) | yes | |
| NICKNAME | nvarchar(50) | yes | |
| MARITALSTATUS | nvarchar(100) | yes | |
| PRIMARYADDRESS | nvarchar(150) | yes | |
| PRIMARYADDRESSLINE1 | nvarchar(150) | yes | |
| PRIMARYADDRESSLINE2 | nvarchar(150) | yes | |
| PRIMARYADDRESSLINE3 | nvarchar(150) | yes | |
| PRIMARYADDRESSLINE4 | nvarchar(150) | yes | |
| PRIMARYADDRESSLINE5 | nvarchar(150) | yes | |
| PRIMARYADDRESSCITY | nvarchar(50) | yes | |
| PRIMARYADDRESSSTATEABBREVIATION | nvarchar(50) | yes | |
| PRIMARYADDRESSPOSTCODE | nvarchar(12) | yes | |
| PRIMARYADDRESSCOUNTRY | nvarchar(100) | yes | |
| PRIMARYEMAILADDRESS | UDT_EMAILADDRESS | yes | |
| PRIMARYPHONE | nvarchar(100) | yes | |
| EDUCATIONINSTITUTION | nvarchar(100) | yes | |
| EDUCATIONDEGREE | nvarchar(100) | yes | |
| CLASSOF | int | yes | |
| PREFERREDCLASSYEAR | int | yes | |
| EDUCATIONSTATUS | nvarchar(100) | yes | |
| EDUCATIONLEVEL | nvarchar(100) | yes | |
| EDUCATIONPROGRAM | nvarchar(100) | yes | |
| EDUCATIONGRADUATEFUZZYDATE | UDT_FUZZYDATE | yes | |
| EDUCATIONSTARTFUZZYDATE | UDT_FUZZYDATE | yes | |
| EDUCATIONENDFUZZYDATE | UDT_FUZZYDATE | yes | |
| PRIMARYBUSINESS | nvarchar(154) | yes | |
| JOBTITLE | nvarchar(100) | yes | |
| JOBCATEGORY | nvarchar(100) | yes | |
| CAREERLEVEL | nvarchar(100) | yes | |
| BUSINESSADDRESS | nvarchar(150) | yes | |
| BUSINESSCITY | nvarchar(50) | yes | |
| BUSINESSSTATE | nvarchar(50) | yes | |
| BUSINESSPOSTCODE | nvarchar(12) | yes | |
| BUSINESSCOUNTRY | nvarchar(100) | yes | |
| INCOME | nvarchar(100) | yes | |
| TARGET | nvarchar(100) | yes | |
| RELIGION | nvarchar(100) | yes | |
| GENDER | nvarchar(10) | yes | |
| ISORGANIZATION | bit | yes | |
| ISINDIVIDUAL | bit | yes | |
| ISHOUSEHOLD | bit | yes | |
| ISGROUP | bit | yes | |
| GROUPTYPE | nvarchar(150) | yes | |
| ISACTIVE | bit | yes | |
| SPOUSECONSTITUENTDIMID | int | yes | |
| DATEADDEDDIMID | int | yes | |
| DATEADDED | datetime | yes | |
| DATECHANGEDDIMID | int | yes | |
| DATECHANGED | datetime | yes | |
| TITLE2 | nvarchar(100) | yes | |
| SUFFIX2 | nvarchar(100) | yes | |
| GENDERDESCRIPTION | nvarchar(100) | yes |
Definition
Copy
create view BBDW.[V_QUERY_BBDW_CONSTITUENT_NORELATIONSHIPSNORECOGNITION] as
select
c.[CONSTITUENTDIMID],
c.[CONSTITUENTSYSTEMID],
c.[CONSTITUENTLOOKUPID],
c.[FULLNAME],
c.[FIRSTNAME],
c.[MIDDLENAME],
c.[KEYNAME],
c.[BIRTHDATE],
c.[AGE],
c.[ISDECEASED],
c.[DONOTMAIL],
c.[DONOTEMAIL],
c.[DONOTCALL],
c.[GIVESANONYMOUSLY],
c.[TITLE],
c.[SUFFIX],
c.[NICKNAME],
c.[MARITALSTATUS],
c.[PRIMARYADDRESS],
c.[PRIMARYADDRESSLINE1],
c.[PRIMARYADDRESSLINE2],
c.[PRIMARYADDRESSLINE3],
c.[PRIMARYADDRESSLINE4],
c.[PRIMARYADDRESSLINE5],
c.[PRIMARYADDRESSCITY],
c.[PRIMARYADDRESSSTATEABBREVIATION],
c.[PRIMARYADDRESSPOSTCODE],
c.[PRIMARYADDRESSCOUNTRY],
c.[PRIMARYEMAILADDRESS],
c.[PRIMARYPHONE],
ed.[EDUCATIONINSTITUTION],
ed.[EDUCATIONDEGREE],
ed.[CLASSOF],
ed.[PREFERREDCLASSYEAR],
ed.[EDUCATIONSTATUS],
ed.[EDUCATIONLEVEL],
ed.[EDUCATIONPROGRAM],
ed.[EDUCATIONGRADUATEFUZZYDATE],
ed.[EDUCATIONSTARTFUZZYDATE],
ed.[EDUCATIONENDFUZZYDATE],
pb.[FULLNAME] as [PRIMARYBUSINESS],
cre.[JOBTITLE],
cre.[JOBCATEGORY],
cre.[CAREERLEVEL],
pb.[PRIMARYADDRESS] as [BUSINESSADDRESS],
pb.[PRIMARYADDRESSCITY] as [BUSINESSCITY],
pb.[PRIMARYADDRESSSTATEABBREVIATION] as [BUSINESSSTATE],
pb.[PRIMARYADDRESSPOSTCODE] as [BUSINESSPOSTCODE],
pb.[PRIMARYADDRESSCOUNTRY] as [BUSINESSCOUNTRY],
c.[INCOME],
c.[TARGET],
c.[RELIGION],
c.[GENDER],
c.[ISORGANIZATION],
c.[ISINDIVIDUAL],
c.[ISHOUSEHOLD],
c.[ISGROUP],
cg.[GROUPTYPE],
c.[ISACTIVE],
c.[SPOUSECONSTITUENTDIMID],
c.[ADDEDDATEDIMID] as [DATEADDEDDIMID],
c.[ADDEDDATE] as [DATEADDED],
c.[CHANGEDDATEDIMID] as [DATECHANGEDDIMID],
c.[CHANGEDDATE] as [DATECHANGED],
c.[TITLE2],
c.[SUFFIX2],
c.[GENDERDESCRIPTION]
from BBDW.[DIM_CONSTITUENT] c
left join BBDW.[DIM_EDUCATION] ed on c.[CONSTITUENTDIMID] = ed.[CONSTITUENTDIMID] and ed.[ISPRIMARYRECORD] = 1
left join BBDW.[DIM_CONSTITUENT] pb on c.[PRIMARYBUSINESSCONSTITUENTDIMID] = pb.[CONSTITUENTDIMID] and c.[PRIMARYBUSINESSCONSTITUENTDIMID] <> 0
left join BBDW.[DIM_CONSTITUENTRELATIONSHIPEMPLOYMENT] cre on c.[PRIMARYBUSINESSCONSTITUENTRELATIONSHIPEMPLOYMENTDIMID] = cre.[CONSTITUENTRELATIONSHIPEMPLOYMENTDIMID] and c.[PRIMARYBUSINESSCONSTITUENTRELATIONSHIPEMPLOYMENTDIMID] <> 0
left join BBDW.[DIM_CONSTITUENTGROUP] cg on cg.[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID] and c.[CONSTITUENTDIMID] <> 0
where c.[ISCONSTITUENT] = 1