v_QUERY_FACT_CONSTITUENTATTRIBUTE

Stores information about constituent attributes for use in ad-hoc query

Fields

Field	Field Type	Null	Description
ATTRIBUTESYSTEMID	uniqueidentifier	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[ATTRIBUTESYSTEMID]
CONSTITUENTATTRIBUTESYSTEMID	uniqueidentifier	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[CONSTITUENTATTRIBUTESYSTEMID]
ATTRIBUTECATEGORYSYSTEMID	uniqueidentifier	yes	BBDW.[DIM_ATTRIBUTE].[ATTRIBUTECATEGORYSYSTEMID]
CONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[CONSTITUENTSYSTEMID]
ATTRIBUTESTARTDATEDIMID	int	yes	BBDW.[FACT_OPPORTUNITYATTRIBUTE].[ATTRIBUTESTARTDATEDIMID]
ATTRIBUTESTARTDATE	datetime	yes	BBDW.[FACT_OPPORTUNITYATTRIBUTE].[ATTRIBUTESTARTDATE]
ATTRIBUTEENDDATEDIMID	int	yes	BBDW.[FACT_OPPORTUNITYATTRIBUTE].[ATTRIBUTEENDDATEDIMID]
ATTRIBUTEENDDATE	datetime	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[ATTRIBUTEENDDATE]
ATTRIBUTEVALUE	nvarchar(255)	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[ATTRIBUTEVALUE]
ATTRIBUTECOMMENT	nvarchar(255)	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[ATTRIBUTECOMMENT]
ATTRIBUTEVALUECONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTSYSTEMID]
ATTRIBUTEVALUECONSTITUENTFULLNAME	nvarchar(154)	yes	BBDW.[DIM_CONSTITUENT].[FULLNAME]
VALUEDATE	date	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[VALUEDATE]
VALUEDATEDIMID	int	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[VALUEDATEDIMID]
VALUEYESNO	bit	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[VALUEYESNO]
VALUECURRENCY	money	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[VALUECURRENCY]
VALUEHOURMINUTE	UDT_HOURMINUTE	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[VALUEHOURMINUTE]
VALUEFUZZYDATE	UDT_FUZZYDATE	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[VALUEFUZZYDATE]
VALUENUMBER	int	yes	BBDW.[FACT_CONSTITUENTATTRIBUTE].[VALUENUMBER]

Definition

Copy


CREATE view [BBDW].[v_QUERY_FACT_CONSTITUENTATTRIBUTE] as

  select
    ca.[ATTRIBUTESYSTEMID],
    ca.[CONSTITUENTATTRIBUTESYSTEMID],
    a.[ATTRIBUTECATEGORYSYSTEMID],
    ca.[CONSTITUENTSYSTEMID],
    ca.[ATTRIBUTESTARTDATEDIMID],
    ca.[ATTRIBUTESTARTDATE],
    ca.[ATTRIBUTEENDDATEDIMID],
    ca.[ATTRIBUTEENDDATE],
    ca.[ATTRIBUTEVALUE], 
    ca.[ATTRIBUTECOMMENT],
    null as [ATTRIBUTEVALUECONSTITUENTSYSTEMID],
    null as [ATTRIBUTEVALUECONSTITUENTFULLNAME],
    ca.[VALUEDATE],
    ca.[VALUEDATEDIMID],
    ca.[VALUEYESNO],
    ca.[VALUECURRENCY],
    ca.[VALUEHOURMINUTE],
    ca.[VALUEFUZZYDATE],
    ca.[VALUENUMBER]
  from 
    BBDW.[FACT_CONSTITUENTATTRIBUTE] ca
    inner join BBDW.[DIM_ATTRIBUTE] a on ca.[ATTRIBUTEDIMID]  = a.[ATTRIBUTEDIMID] and a.[ATTRIBUTEDATATYPE] <> 'Constituent record'

union all

  select
    ca.[ATTRIBUTESYSTEMID],
    ca.[CONSTITUENTATTRIBUTESYSTEMID],
    a.[ATTRIBUTECATEGORYSYSTEMID],
    ca.[CONSTITUENTSYSTEMID],
    ca.[ATTRIBUTESTARTDATEDIMID],
    ca.[ATTRIBUTESTARTDATE],
    ca.[ATTRIBUTEENDDATEDIMID],
    ca.[ATTRIBUTEENDDATE],
    ca.[ATTRIBUTEVALUE], 
    ca.[ATTRIBUTECOMMENT],
    c.[CONSTITUENTSYSTEMID] as [ATTRIBUTEVALUECONSTITUENTSYSTEMID],
    c.[FULLNAME] as [ATTRIBUTEVALUECONSTITUENTFULLNAME],
    ca.[VALUEDATE],
    ca.[VALUEDATEDIMID],
    ca.[VALUEYESNO],
    ca.[VALUECURRENCY],
    ca.[VALUEHOURMINUTE],
    ca.[VALUEFUZZYDATE],
    ca.[VALUENUMBER]
  from 
    BBDW.[FACT_CONSTITUENTATTRIBUTE] ca 
    inner join BBDW.[DIM_ATTRIBUTE] a on ca.[ATTRIBUTEDIMID] = a.[ATTRIBUTEDIMID] and a.[ATTRIBUTEDATATYPE] = 'Constituent record'
    left join BBDW.[DIM_CONSTITUENT] c on ca.[ATTRIBUTEVALUE] = c.[CONSTITUENTSYSTEMID];