v_FACT_CONSTITUENTPHONE_FULL

The Constituent Phone full view stores information about constituent phone numbers

Fields

Field	Field Type	Null	Description
CONSTITUENTPHONEFACTID	int		BBDW.[FACT_CONSTITUENTPHONE].[CONSTITUENTPHONEFACTID]
CONSTITUENTPHONESYSTEMID	uniqueidentifier	yes	BBDW.[FACT_CONSTITUENTPHONE].[CONSTITUENTPHONESYSTEMID]
CONSTITUENTDIMID	int	yes	BBDW.[FACT_CONSTITUENTPHONE].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_CONSTITUENTPHONE].[CONSTITUENTSYSTEMID]
PHONENUMBER	nvarchar(100)	yes	BBDW.[FACT_CONSTITUENTPHONE].[PHONENUMBER]
PHONENUMBERNOFORMAT	nvarchar(100)	yes	BBDW.[FACT_CONSTITUENTPHONE].[PHONENUMBERNOFORMAT]
STARTDATEDIMID	int	yes	BBDW.[FACT_CONSTITUENTPHONE].[STARTDATEDIMID]
STARTDATE	datetime	yes	BBDW.[FACT_CONSTITUENTPHONE].[STARTDATE]
ENDDATEDIMID	int	yes	BBDW.[FACT_CONSTITUENTPHONE].[ENDDATEDIMID]
ENDDATE	datetime	yes	BBDW.[FACT_CONSTITUENTPHONE].[ENDDATE]
STARTTIME	char(4)	yes	BBDW.[FACT_CONSTITUENTPHONE].[STARTTIME]
STARTTIMEDIMID	int	yes	BBDW.[FACT_CONSTITUENTPHONE].[STARTTIMEDIMID]
ENDTIME	char(4)	yes	BBDW.[FACT_CONSTITUENTPHONE].[ENDTIME]
ENDTIMEDIMID	int	yes	BBDW.[FACT_CONSTITUENTPHONE].[ENDTIMEDIMID]
SEASONALSTARTMONTHDAY	char(4)	yes	BBDW.[FACT_CONSTITUENTPHONE].[SEASONALSTARTMONTHDAY]
SEASONALENDMONTHDAY	char(4)	yes	BBDW.[FACT_CONSTITUENTPHONE].[SEASONALENDMONTHDAY]
CONSTITUENTPHONETYPEDIMID	int	yes	BBDW.[FACT_CONSTITUENTPHONE].[CONSTITUENTPHONETYPEDIMID]
CONSTITUENTPHONETYPE	nvarchar(100)	yes	BBDW.[DIM_CONSTITUENTPHONETYPE].[CONSTITUENTPHONETYPE]
CONSTITUENTPHONEDIMID	int	yes	BBDW.[FACT_CONSTITUENTPHONE].[CONSTITUENTPHONEDIMID]
INFORMATIONSOURCE	nvarchar(100)	yes	BBDW.[DIM_CONSTITUENTPHONE].[INFORMATIONSOURCE]
ORIGINCODE	tinyint	yes	BBDW.[DIM_CONSTITUENTPHONE].[ORIGINCODE]
ORIGIN	nvarchar(9)	yes	BBDW.[DIM_CONSTITUENTPHONE].[ORIGIN]
CONSTITUENTPHONEFLAGDIMID	int	yes	BBDW.[FACT_CONSTITUENTPHONE].[CONSTITUENTPHONEFLAGDIMID]
ISPRIMARY	bit	yes	BBDW.[DIM_CONSTITUENTPHONEFLAG].[ISPRIMARY]
DONOTCALL	bit	yes	BBDW.[DIM_CONSTITUENTPHONEFLAG].[DONOTCALL]
ISCONFIDENTIAL	bit	yes	BBDW.[DIM_CONSTITUENTPHONEFLAG].[ISCONFIDENTIAL]
DONOTCALLREASONDIMID	int	yes	BBDW.[FACT_CONSTITUENTPHONE].[DONOTCALLREASONDIMID]
DONOTCALLREASON	nvarchar(100)	yes	BBDW.[DIM_DONOTCALLREASON].[DONOTCALLREASON]
COUNTRYDIMID	int	yes	BBDW.[FACT_CONSTITUENTPHONE].[COUNTRYDIMID]
COUNTRY	nvarchar(100)	yes	BBDW.[DIM_COUNTRY].[COUNTRY]
COUNTRYABBREVIATION	nvarchar(5)	yes	BBDW.[DIM_COUNTRY].[COUNTRYABBREVIATION]
CONSTITUENTRELATIONSHIPFACTID	int	yes	BBDW.[FACT_CONSTITUENTPHONE].[CONSTITUENTRELATIONSHIPFACTID]
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_CONSTITUENTPHONE_FULL] as
  select
    p.[CONSTITUENTPHONEFACTID],
    p.[CONSTITUENTPHONESYSTEMID],  
    p.CONSTITUENTDIMID,
    p.[CONSTITUENTSYSTEMID],
    p.[PHONENUMBER],
    p.[PHONENUMBERNOFORMAT],
    p.[STARTDATEDIMID],
    p.[STARTDATE],
    p.[ENDDATEDIMID],
    p.[ENDDATE],
    p.[STARTTIME],
    p.[STARTTIMEDIMID],
    p.[ENDTIME],
    p.[ENDTIMEDIMID],
    p.[SEASONALSTARTMONTHDAY],
    p.[SEASONALENDMONTHDAY],
    p.[CONSTITUENTPHONETYPEDIMID],
    t.[CONSTITUENTPHONETYPE],
    p.[CONSTITUENTPHONEDIMID],
    dp.[INFORMATIONSOURCE],
    dp.[ORIGINCODE],
    dp.[ORIGIN],
    p.[CONSTITUENTPHONEFLAGDIMID],
    f.[ISPRIMARY],
    f.[DONOTCALL],
    f.[ISCONFIDENTIAL],
    p.[DONOTCALLREASONDIMID],
    dnc.[DONOTCALLREASON],
    p.[COUNTRYDIMID],
    cn.[COUNTRY],
    cn.[COUNTRYABBREVIATION],
    p.[CONSTITUENTRELATIONSHIPFACTID],
    c.[AGE] as [CONSTITUENTAGE],
    c.[CONSTITUENTINFORMATIONDIMID],
    c.[CONSTITUENTADDRESSDIMID],
    c.[CONSTITUENTADDRESSDETAILDIMID],
    c.[CONSTITUENTADDRESSFLAGDIMID],
    c.[CONSTITUENTADDRESSTYPEDIMID],
    c.[CONSTITUENTDEMOGRAPHICDIMID]
  from BBDW.[FACT_CONSTITUENTPHONE] p
  left join BBDW.[DIM_CONSTITUENT] c on p.[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID]
  left join BBDW.[DIM_CONSTITUENTPHONETYPE] t on t.[CONSTITUENTPHONETYPEDIMID] = p.[CONSTITUENTPHONETYPEDIMID]
  left join BBDW.[DIM_CONSTITUENTPHONE] dp on p.[CONSTITUENTPHONEDIMID] = dp.[CONSTITUENTPHONEDIMID]
  left join BBDW.[DIM_CONSTITUENTPHONEFLAG] f on f.[CONSTITUENTPHONEFLAGDIMID] = p.[CONSTITUENTPHONEFLAGDIMID]
  left join BBDW.[DIM_DONOTCALLREASON] dnc on p.[DONOTCALLREASONDIMID] = dnc.[DONOTCALLREASONDIMID]
  left join BBDW.[DIM_COUNTRY] cn on cn.[COUNTRYDIMID] = p.[COUNTRYDIMID]