v_FACT_CONSTITUENTEMAILADDRESS_FULL
The Constituent Email Address fact tables contains information about constituent email addresses
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTEMAILADDRESSFACTID | int | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[CONSTITUENTEMAILADDRESSFACTID] | |
| CONSTITUENTEMAILADDRESSSYSTEMID | uniqueidentifier | yes | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[CONSTITUENTEMAILADDRESSSYSTEMID] |
| CONSTITUENTDIMID | int | yes | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[CONSTITUENTDIMID] |
| CONSTITUENTSYSTEMID | uniqueidentifier | yes | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[CONSTITUENTSYSTEMID] |
| CONSTITUENTEMAILADDRESSTYPEDIMID | int | yes | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[CONSTITUENTEMAILADDRESSTYPEDIMID] |
| CONSTITUENTEMAILADDRESSTYPE | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTEMAILADDRESSTYPE].[CONSTITUENTEMAILADDRESSTYPE] |
| EMAILADDRESS | UDT_EMAILADDRESS | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[EMAILADDRESS] | |
| STARTDATEDIMID | int | yes | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[STARTDATEDIMID] |
| STARTDATE | datetime | yes | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[STARTDATE] |
| ENDDATEDIMID | int | yes | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[ENDDATEDIMID] |
| ENDDATE | datetime | yes | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[ENDDATE] |
| CONSTITUENTRELATIONSHIPFACTID | int | yes | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[CONSTITUENTRELATIONSHIPFACTID] |
| CONSTITUENTEMAILADDRESSDIMID | int | yes | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[CONSTITUENTEMAILADDRESSDIMID] |
| INFORMATIONSOURCE | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTEMAILADDRESS].[INFORMATIONSOURCE] |
| ORIGIN | nvarchar(9) | yes | BBDW.[DIM_CONSTITUENTEMAILADDRESS].[ORIGIN] |
| ORIGINCODE | tinyint | yes | BBDW.[DIM_CONSTITUENTEMAILADDRESS].[ORIGINCODE] |
| CONSTITUENTEMAILADDRESSFLAGDIMID | int | yes | BBDW.[FACT_CONSTITUENTEMAILADDRESS].[CONSTITUENTEMAILADDRESSFLAGDIMID] |
| DONOTEMAIL | bit | yes | BBDW.[DIM_CONSTITUENTEMAILADDRESSFLAG].[DONOTEMAIL] |
| ISPRIMARY | bit | yes | BBDW.[DIM_CONSTITUENTEMAILADDRESSFLAG].[ISPRIMARY] |
| CONSTITUENTAGE | int | yes | BBDW.[DIM_CONSTITUENT].[AGE] |
| CONSTITUENTINFORMATIONDIMID | int | yes | BBDW.[DIM_CONSTITUENT].[CONSTITUENTINFORMATIONDIMID] |
| CONSTITUENTADDRESSDIMID | int | yes | BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDIMID] |
| CONSTITUENTADDRESSDETAILDIMID | int | yes | BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDETAILDIMID] |
| CONSTITUENTADDRESSFLAGDIMID | int | yes | BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSFLAGDIMID] |
| CONSTITUENTADDRESSTYPEDIMID | int | yes | BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSTYPEDIMID] |
| CONSTITUENTDEMOGRAPHICDIMID | int | yes | BBDW.[DIM_CONSTITUENT].[CONSTITUENTDEMOGRAPHICDIMID] |
Definition
Copy
create view [BBDW].[v_FACT_CONSTITUENTEMAILADDRESS_FULL] as
select
ce.[CONSTITUENTEMAILADDRESSFACTID],
ce.[CONSTITUENTEMAILADDRESSSYSTEMID],
ce.[CONSTITUENTDIMID],
ce.[CONSTITUENTSYSTEMID],
ce.[CONSTITUENTEMAILADDRESSTYPEDIMID],
cet.[CONSTITUENTEMAILADDRESSTYPE],
ce.[EMAILADDRESS],
ce.[STARTDATEDIMID],
ce.[STARTDATE],
ce.[ENDDATEDIMID],
ce.[ENDDATE],
ce.[CONSTITUENTRELATIONSHIPFACTID],
ce.[CONSTITUENTEMAILADDRESSDIMID],
dce.[INFORMATIONSOURCE],
dce.[ORIGIN],
dce.[ORIGINCODE],
ce.[CONSTITUENTEMAILADDRESSFLAGDIMID],
cef.[DONOTEMAIL],
cef.[ISPRIMARY],
c.[AGE] as [CONSTITUENTAGE],
c.[CONSTITUENTINFORMATIONDIMID],
c.[CONSTITUENTADDRESSDIMID],
c.[CONSTITUENTADDRESSDETAILDIMID],
c.[CONSTITUENTADDRESSFLAGDIMID],
c.[CONSTITUENTADDRESSTYPEDIMID],
c.[CONSTITUENTDEMOGRAPHICDIMID]
from BBDW.[FACT_CONSTITUENTEMAILADDRESS] ce
left join BBDW.[DIM_CONSTITUENT] c on ce.CONSTITUENTDIMID = c.[CONSTITUENTDIMID]
left join BBDW.[DIM_CONSTITUENTEMAILADDRESS] dce on ce.[CONSTITUENTEMAILADDRESSDIMID] = dce.[CONSTITUENTEMAILADDRESSDIMID]
left join BBDW.[DIM_CONSTITUENTEMAILADDRESSTYPE] cet on cet.[CONSTITUENTEMAILADDRESSTYPEDIMID] = ce.[CONSTITUENTEMAILADDRESSTYPEDIMID]
left join BBDW.[DIM_CONSTITUENTEMAILADDRESSFLAG] cef on cef.[CONSTITUENTEMAILADDRESSFLAGDIMID] = ce.[CONSTITUENTEMAILADDRESSFLAGDIMID]