V_QUERY_BBDW_REVENUE_MEMBERSHIP
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTDIMID | int | yes | |
| MEMBERSHIPSYSTEMID | uniqueidentifier | yes | |
| MEMBERSHIPDIMID | int | yes | |
| JOINDATEDIMID | int | yes | |
| JOINDATE | datetime | yes | |
| EXPIRATIONDATEDIMID | int | yes | |
| EXPIRATIONDATE | datetime | yes | |
| MEMBERSHIPPROGRAMCATEGORY | nvarchar(50) | yes | |
| MEMBERSHIPPROGRAM | nvarchar(100) | yes | |
| MEMBERSHIPPROGRAMDESCRIPTION | nvarchar(255) | yes | |
| MEMBERSHIPPROGRAMTYPE | nvarchar(50) | yes | |
| MEMBERSHIPLEVEL | nvarchar(100) | yes | |
| MEMBERSHIPLEVELDESCRIPTION | nvarchar(255) | yes | |
| MEMBERSHIPSTATUS | nvarchar(50) | yes | |
| MEMBERISDROPPED | bit | yes | |
| MEMBERISPRIMARY | bit | yes | |
| MEMBERSHIPREPORTINGGROUP | nvarchar(100) | yes | |
| MEMBERSHIPTYPE | nvarchar(100) | yes | |
| MEMBERSHIPTERMAMOUNT | money | yes | |
| TERMLENGTH | nvarchar(65) | yes | |
| CURRENCYSYSTEMID | uniqueidentifier | yes | |
| BASEMEMBERSHIPTERMAMOUNT | money | yes | |
| BASECURRENCYSYSTEMID | uniqueidentifier | yes |
Definition
Copy
create view BBDW.[V_QUERY_BBDW_REVENUE_MEMBERSHIP] as
select
fm.[CONSTITUENTDIMID],
dm.[MEMBERSHIPSYSTEMID],
dm.[MEMBERSHIPDIMID],
dm.[JOINDATEDIMID],
dm.[JOINDATE],
dm.[EXPIRATIONDATEDIMID],
dm.[EXPIRATIONDATE],
mp.[MEMBERSHIPPROGRAMCATEGORY],
mp.[MEMBERSHIPPROGRAM],
mp.[MEMBERSHIPPROGRAMDESCRIPTION],
mp.[MEMBERSHIPPROGRAMTYPE],
mp.[MEMBERSHIPLEVEL],
mp.[MEMBERSHIPLEVELDESCRIPTION],
ms.[MEMBERSHIPSTATUS],
mf.[MEMBERISDROPPED],
mf.[MEMBERISPRIMARY],
mp.[MEMBERSHIPREPORTINGGROUP],
mp.[MEMBERSHIPTYPE],
mp.[MEMBERSHIPTERMAMOUNT],
case MP.[MEMBERSHIPPROGRAMBASEDONCODE]
when 0 then cast(MP.[ANNUALTERMTIMELENGTH] as nvarchar(5)) + ' ' + MP.[ANNUALTERMLENGTH]
when 1 then MP.[RECURRINGPAYMENTOPTION]
when 2 then case when MP.[LIFETIMEPAYMENTOPTIONCODE] = 0 then MP.[LIFETIMEPAYMENTOPTION]
else cast(MP.[LIFETIMENUMBEROFPAYMENTS] as nvarchar(5)) + ' ' + MP.[LIFETIMEPAYMENTOPTION] + ' payments'
end
end as [TERMLENGTH],
c.[CURRENCYSYSTEMID],
mp.[BASEMEMBERSHIPTERMAMOUNT],
bc.[CURRENCYSYSTEMID] [BASECURRENCYSYSTEMID]
from BBDW.[FACT_MEMBER] fm
left join BBDW.[DIM_MEMBERSHIP] dm on fm.[MEMBERSHIPDIMID] = dm.[MEMBERSHIPDIMID] and fm.[MEMBERSHIPDIMID] <> 0
left join BBDW.[DIM_MEMBERSHIPPROGRAM] mp on dm.[MEMBERSHIPPROGRAMDIMID] = mp.[MEMBERSHIPPROGRAMDIMID] and dm.[MEMBERSHIPPROGRAMDIMID] <> 0
left join BBDW.[DIM_MEMBERFLAG] mf on mf.[MEMBERFLAGDIMID] = fm.[MEMBERFLAGDIMID] and fm.[MEMBERFLAGDIMID] <> 0
left join BBDW.[DIM_MEMBERSHIPSTATUS] ms on dm.[MEMBERSHIPSTATUSDIMID] = ms.[MEMBERSHIPSTATUSDIMID] and dm.[MEMBERSHIPSTATUSDIMID] <> 0
left join BBDW.[DIM_CURRENCY] bc on bc.[CURRENCYDIMID] = mp.[BASECURRENCYDIMID]
outer apply BBDW.[DIM_CURRENCY] c
where c.[ISORGANIZATIONCURRENCY] = 1