v_FACT_CONSTITUENTADDRESS_FULL
Contains all of the information about constituent addresses.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTADDRESSFACTID | int | BBDW.[FACT_CONSTITUENTADDRESS].[CONSTITUENTADDRESSFACTID] | |
| CONSTITUENTADDRESSSYSTEMID | uniqueidentifier | yes | BBDW.[FACT_CONSTITUENTADDRESS].[CONSTITUENTADDRESSSYSTEMID] |
| CONSTITUENTDIMID | int | yes | BBDW.[FACT_CONSTITUENTADDRESS].[CONSTITUENTDIMID] |
| CONSTITUENTSYSTEMID | uniqueidentifier | yes | BBDW.[FACT_CONSTITUENTADDRESS].[CONSTITUENTSYSTEMID] |
| CONSTITUENTADDRESSTYPEDIMID | int | yes | BBDW.[FACT_CONSTITUENTADDRESS].[CONSTITUENTADDRESSTYPEDIMID] |
| CONSTITUENTADDRESSTYPE | nvarchar(100) | yes | BBDW.[FACT_CONSTITUENTADDRESS].[CONSTITUENTADDRESSTYPE] |
| CONSTITUENTADDRESSFLAGDIMID | int | yes | BBDW.[FACT_CONSTITUENTADDRESS].[CONSTITUENTADDRESSFLAGDIMID] |
| DONOTMAIL | bit | yes | BBDW.[DIM_CONSTITUENTADDRESSFLAG].[DONOTMAIL] |
| ISCONFIDENTIAL | bit | yes | BBDW.[DIM_CONSTITUENTADDRESSFLAG].[ISCONFIDENTIAL] |
| ISPRIMARY | bit | yes | BBDW.[DIM_CONSTITUENTADDRESSFLAG].[ISPRIMARY] |
| ISCURRENT | bit | yes | BBDW.[DIM_CONSTITUENTADDRESSFLAG].[ISCURRENT] |
| DONOTMAILREASONDIMID | int | yes | BBDW.[FACT_CONSTITUENTADDRESS].[DONOTMAILREASONDIMID] |
| DONOTMAILREASON | nvarchar(100) | yes | BBDW.[DIM_DONOTMAILREASON].[DONOTMAILREASON] |
| ADDRESSBLOCK | nvarchar(150) | yes | BBDW.[FACT_CONSTITUENTADDRESS].[ADDRESSBLOCK] |
| ADDRESSLINE1 | nvarchar(150) | yes | BBDW.[FACT_CONSTITUENTADDRESS].[ADDRESSLINE1] |
| ADDRESSLINE2 | nvarchar(150) | yes | BBDW.[FACT_CONSTITUENTADDRESS].[ADDRESSLINE2] |
| ADDRESSLINE3 | nvarchar(150) | yes | BBDW.[FACT_CONSTITUENTADDRESS].[ADDRESSLINE3] |
| ADDRESSLINE4 | nvarchar(150) | yes | BBDW.[FACT_CONSTITUENTADDRESS].[ADDRESSLINE4] |
| ADDRESSLINE5 | nvarchar(150) | yes | BBDW.[FACT_CONSTITUENTADDRESS].[ADDRESSLINE5] |
| CONSTITUENTADDRESSDIMID | int | yes | BBDW.[FACT_CONSTITUENTADDRESS].[CONSTITUENTADDRESSDIMID] |
| CITY | nvarchar(50) | yes | BBDW.[DIM_CONSTITUENTADDRESS].[CITY] |
| STATE | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESS].[STATE] |
| STATEABBREVIATION | nvarchar(50) | yes | BBDW.[DIM_CONSTITUENTADDRESS].[STATEABBREVIATION] |
| COUNTRY | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESS].[COUNTRY] |
| COUNTRYABBREVIATION | nvarchar(5) | yes | BBDW.[DIM_CONSTITUENTADDRESS].[COUNTRYABBREVIATION] |
| POSTCODE | nvarchar(12) | yes | BBDW.[DIM_CONSTITUENTADDRESS].[POSTCODE] |
| CONSTITUENTADDRESSDETAILDIMID | int | yes | BBDW.[FACT_CONSTITUENTADDRESS].[CONSTITUENTADDRESSDETAILDIMID] |
| COUNTY | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[COUNTY] |
| CONGRESSIONALDISTRICT | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[CONGRESSIONALDISTRICT] |
| STATEHOUSEDISTRICT | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[STATEHOUSEDISTRICT] |
| STATESENATEDISTRICT | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[STATESENATEDISTRICT] |
| LOCALPRECINCT | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[LOCALPRECINCT] |
| INFOSOURCE | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[INFOSOURCE] |
| REGION | nvarchar(100) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[REGION] |
| ORIGIN | nvarchar(10) | yes | BBDW.[DIM_CONSTITUENTADDRESSDETAIL].[ORIGIN] |
| CART | nvarchar(10) | yes | BBDW.[FACT_CONSTITUENTADDRESS].[CART] |
| DPC | nvarchar(8) | yes | BBDW.[FACT_CONSTITUENTADDRESS].[DPC] |
| LOT | nvarchar(5) | yes | BBDW.[FACT_CONSTITUENTADDRESS].[LOT] |
| HISTORICALSTARTDATEDIMID | int | yes | BBDW.[FACT_CONSTITUENTADDRESS].[HISTORICALSTARTDATEDIMID] |
| HISTORICALSTARTDATE | datetime | yes | BBDW.[FACT_CONSTITUENTADDRESS].[HISTORICALSTARTDATE] |
| HISTORICALENDDATEDIMID | int | yes | BBDW.[FACT_CONSTITUENTADDRESS].[HISTORICALENDDATEDIMID] |
| HISTORICALENDDATE | datetime | yes | BBDW.[FACT_CONSTITUENTADDRESS].[HISTORICALENDDATE] |
| STARTMONTHDAY | UDT_MONTHDAY | BBDW.[FACT_CONSTITUENTADDRESS].[STARTMONTHDAY] | |
| ENDMONTHDAY | UDT_MONTHDAY | BBDW.[FACT_CONSTITUENTADDRESS].[ENDMONTHDAY] |
Definition
Copy
CREATE view [BBDW].[v_FACT_CONSTITUENTADDRESS_FULL] as
select
ca.[CONSTITUENTADDRESSFACTID],
ca.[CONSTITUENTADDRESSSYSTEMID],
ca.[CONSTITUENTDIMID],
ca.[CONSTITUENTSYSTEMID],
ca.[CONSTITUENTADDRESSTYPEDIMID],
cat.[CONSTITUENTADDRESSTYPE],
ca.[CONSTITUENTADDRESSFLAGDIMID],
caf.[DONOTMAIL],
caf.[ISCONFIDENTIAL],
caf.[ISPRIMARY],
caf.[ISCURRENT],
ca.[DONOTMAILREASONDIMID],
d.[DONOTMAILREASON],
ca.[ADDRESSBLOCK],
ca.[ADDRESSLINE1],
ca.[ADDRESSLINE2],
ca.[ADDRESSLINE3],
ca.[ADDRESSLINE4],
ca.[ADDRESSLINE5],
ca.[CONSTITUENTADDRESSDIMID],
dca.[CITY],
dca.[STATE],
dca.[STATEABBREVIATION],
dca.[COUNTRY],
dca.[COUNTRYABBREVIATION],
dca.[POSTCODE],
ca.[CONSTITUENTADDRESSDETAILDIMID],
cad.[COUNTY],
cad.[CONGRESSIONALDISTRICT],
cad.[STATEHOUSEDISTRICT],
cad.[STATESENATEDISTRICT],
cad.[LOCALPRECINCT],
cad.[INFOSOURCE],
cad.[REGION],
cad.[ORIGIN],
ca.[CART],
ca.[DPC],
ca.[LOT],
ca.[HISTORICALSTARTDATEDIMID],
ca.[HISTORICALSTARTDATE],
ca.[HISTORICALENDDATEDIMID],
ca.[HISTORICALENDDATE],
ca.[STARTMONTHDAY],
ca.[ENDMONTHDAY]
from BBDW.[FACT_CONSTITUENTADDRESS] ca
left join BBDW.[DIM_CONSTITUENTADDRESSTYPE] cat on ca.[CONSTITUENTADDRESSTYPEDIMID] = cat.[CONSTITUENTADDRESSTYPEDIMID]
left join BBDW.[DIM_CONSTITUENTADDRESSFLAG] caf on ca.[CONSTITUENTADDRESSFLAGDIMID] = caf.[CONSTITUENTADDRESSFLAGDIMID]
left join BBDW.[DIM_DONOTMAILREASON] d on ca.[DONOTMAILREASONDIMID] = d.[DONOTMAILREASONDIMID]
left join BBDW.[DIM_CONSTITUENTADDRESS] dca on ca.[CONSTITUENTADDRESSDIMID] = dca.[CONSTITUENTADDRESSDIMID]
left join BBDW.[DIM_CONSTITUENTADDRESSDETAIL] cad on ca.[CONSTITUENTADDRESSDETAILDIMID] = cad.[CONSTITUENTADDRESSDETAILDIMID]