V_QUERY_BBDW_RELATIONSHIP
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTRELATIONSHIPSYSTEMID | uniqueidentifier | yes | |
| CONSTITUENTDIMID | int | yes | |
| RECIPROCALCONSTITUENTDIMID | int | yes | |
| STARTDATEDIMID | int | yes | |
| STARTDATE | datetime | yes | |
| ENDDATEDIMID | int | yes | |
| ENDDATE | datetime | yes | |
| ISCONTACT | bit | yes | |
| ISPRIMARYBUSINESS | bit | yes | |
| ISPRIMARYCONTACT | bit | yes | |
| ISSPOUSE | bit | yes | |
| RELATIONSHIPTYPE | nvarchar(100) | yes | |
| RECIPROCALTYPE | nvarchar(100) | yes |
Definition
Copy
create view BBDW.[V_QUERY_BBDW_RELATIONSHIP] as
select
fr.[CONSTITUENTRELATIONSHIPSYSTEMID],
fr.[CONSTITUENTDIMID],
fr.[RECIPROCALCONSTITUENTDIMID],
fr.[STARTDATEDIMID],
fr.[STARTDATE],
fr.[ENDDATEDIMID],
fr.[ENDDATE],
rf.[ISCONTACT],
rf.[ISPRIMARYBUSINESS],
rf.[ISPRIMARYCONTACT],
rf.[ISSPOUSE],
rel.[CONSTITUENTRELATIONSHIPTYPE] as [RELATIONSHIPTYPE],
rec.[CONSTITUENTRELATIONSHIPTYPE] as [RECIPROCALTYPE]
from BBDW.[FACT_CONSTITUENTRELATIONSHIP] fr
left join BBDW.[DIM_CONSTITUENTRELATIONSHIPFLAG] rf on rf.[CONSTITUENTRELATIONSHIPFLAGDIMID] = fr.[CONSTITUENTRELATIONSHIPFLAGDIMID] and fr.[CONSTITUENTRELATIONSHIPFLAGDIMID] <> 0
left join BBDW.[DIM_CONSTITUENTRELATIONSHIPTYPE] rel on rel.[CONSTITUENTRELATIONSHIPTYPEDIMID] = fr.[CONSTITUENTRELATIONSHIPTYPEDIMID] and fr.[CONSTITUENTRELATIONSHIPTYPEDIMID] <> 0
left join BBDW.[DIM_CONSTITUENTRELATIONSHIPTYPE] rec on rec.[CONSTITUENTRELATIONSHIPTYPEDIMID] = fr.[RECIPROCALCONSTITUENTRELATIONSHIPTYPEDIMID] and fr.[RECIPROCALCONSTITUENTRELATIONSHIPTYPEDIMID] <> 0