V_QUERY_BBDW_ADDRESSES
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTDIMID | int | yes | |
| CONSTITUENTADDRESSSYSTEMID | uniqueidentifier | yes | |
| ADDRESSBLOCK | nvarchar(150) | yes | |
| ADDRESSLINE1 | nvarchar(150) | yes | |
| ADDRESSLINE2 | nvarchar(150) | yes | |
| ADDRESSLINE3 | nvarchar(150) | yes | |
| ADDRESSLINE4 | nvarchar(150) | yes | |
| ADDRESSLINE5 | nvarchar(150) | yes | |
| CITY | nvarchar(50) | yes | |
| STATE | nvarchar(100) | yes | |
| STATEABBREVIATION | nvarchar(50) | yes | |
| POSTCODE | nvarchar(12) | yes | |
| COUNTRY | nvarchar(100) | yes | |
| COUNTRYABBREVIATION | nvarchar(5) | yes | |
| DONOTMAIL | bit | yes | |
| DONOTMAILREASON | nvarchar(100) | yes | |
| ISPRIMARY | bit | yes | |
| CART | nvarchar(10) | yes | |
| DPC | nvarchar(8) | yes | |
| STARTDATEDIMID | int | yes | |
| STARTDATE | datetime | yes | |
| ENDDATEDIMID | int | yes | |
| ENDDATE | datetime | yes | |
| ISCURRENT | bit | yes | |
| CONSTITUENTADDRESSTYPE | nvarchar(100) | yes |
Definition
Copy
create view BBDW.[V_QUERY_BBDW_ADDRESSES] as
select
fca.[CONSTITUENTDIMID],
fca.[CONSTITUENTADDRESSSYSTEMID],
fca.[ADDRESSBLOCK],
fca.[ADDRESSLINE1],
fca.[ADDRESSLINE2],
fca.[ADDRESSLINE3],
fca.[ADDRESSLINE4],
fca.[ADDRESSLINE5],
dca.[CITY],
dca.[STATE],
dca.[STATEABBREVIATION],
dca.[POSTCODE],
dca.[COUNTRY],
dca.[COUNTRYABBREVIATION],
af.[DONOTMAIL],
dnm.[DONOTMAILREASON],
af.[ISPRIMARY],
fca.[CART],
fca.[DPC],
fca.[HISTORICALSTARTDATEDIMID] as [STARTDATEDIMID],
fca.[HISTORICALSTARTDATE] as [STARTDATE],
fca.[HISTORICALENDDATEDIMID] as [ENDDATEDIMID],
fca.[HISTORICALENDDATE] as [ENDDATE],
af.[ISCURRENT],
dcat.[CONSTITUENTADDRESSTYPE]
from BBDW.[FACT_CONSTITUENTADDRESS] fca
left join BBDW.[DIM_CONSTITUENTADDRESSFLAG] af on fca.[CONSTITUENTADDRESSFLAGDIMID] = af.[CONSTITUENTADDRESSFLAGDIMID] and fca.[CONSTITUENTADDRESSFLAGDIMID] <> 0
left join BBDW.[DIM_CONSTITUENTADDRESS] dca on fca.[CONSTITUENTADDRESSDIMID] = dca.[CONSTITUENTADDRESSDIMID] and fca.[CONSTITUENTADDRESSDIMID] <> 0
left join BBDW.[DIM_CONSTITUENTADDRESSTYPE] dcat on dcat.[CONSTITUENTADDRESSTYPEDIMID] = fca.[CONSTITUENTADDRESSTYPEDIMID] and fca.[CONSTITUENTADDRESSTYPEDIMID] <> 0
left join BBDW.[DIM_DONOTMAILREASON] dnm on dnm.[DONOTMAILREASONDIMID] = fca.[DONOTMAILREASONDIMID] and fca.[DONOTMAILREASONDIMID] <> 0