V_QUERY_RE7_CONSTITUENTMEMBERSHIP
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| LOCALID | int | ||
| MEMBERSHIP_LOCALID | int | ||
| CONSTITUENT_LOCALID | int | ||
| ACTIVITYDATE | datetime | yes | |
| PROGRAM | nvarchar(100) | yes | |
| REASON | nvarchar(100) | yes | |
| CATEGORY | nvarchar(100) | yes | |
| SUBCATEGORY | nvarchar(100) | yes | |
| EXPIRESON | datetime | yes | |
| EXPIRESDATEMONTHSDIFF | int | yes | |
| DUESLEVEL | nvarchar(50) | yes | |
| RENEWALTYPE | varchar(9) | yes | |
| ISPRIMARYMEMBER | bit | ||
| WAIVEBENEFITS | bit | ||
| SENDBENEFITSTO | varchar(14) | yes | |
| LIFETIMEMEMBERSHIP | bit | ||
| TYPE | varchar(9) | ||
| ONLINETRANSACTION | bit | ||
| IMPORTID | nvarchar(20) | yes | |
| DUES | money | yes | |
| ISCURRENT | bit | ||
| STANDING | varchar(7) | ||
| SEQUENCE | int | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 2/6/2025 8:50:25 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3901.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RE7_CONSTITUENTMEMBERSHIP AS
select
[CM].[LOCALID],
[CM].[MEMBERSHIP_LOCALID],
[CM].[CONSTITUENT_LOCALID],
[CM].[ACTIVITYDATE],
[PC].[DESCRIPTION] [PROGRAM],
[RC].[DESCRIPTION] [REASON],
[CC].[DESCRIPTION] [CATEGORY],
[SC].[DESCRIPTION] [SUBCATEGORY],
[CM].[EXPIRESON],
datediff( month, getdate(), [CM].[EXPIRESON]) [EXPIRESDATEMONTHSDIFF],
[CM].[DUESLEVEL],
case [CM].[RENEWALTYPE]
when 0 then 'Upgrade'
when 1 then 'Downgrade'
when 2 then 'Same'
end [RENEWALTYPE],
[CM].[ISPRIMARYMEMBER],
[CM].[WAIVEBENEFITS],
case [CM].[SENDBENEFITSTO]
when 0 then 'Primary Member'
when 1 then 'Donor'
end [SENDBENEFITSTO],
[CM].[LIFETIMEMEMBERSHIP],
case [CM].[TYPE]
when 1 then 'Joined'
when 2 then 'Renewal'
when 3 then 'Rejoined'
when 4 then 'Upgrade'
when 5 then 'Downgrade'
when 6 then 'Dropped'
else ''
end [TYPE],
[CM].[ONLINETRANSACTION],
[CM].[IMPORTID],
[CM].[DUES],
[CM].[ISCURRENT],
case [CM].[STANDINGCODE_LOCALID]
when 0 then 'New'
when 1 then 'Active'
when 2 then 'Lapsed'
when 3 then 'Dropped'
else ''
end [STANDING],
[CM].[SEQUENCE]
from [dbo].[RE7_CONSTITUENTMEMBERSHIP] [CM]
left outer join [dbo].[RE7_MEMBERSHIPPROGRAMCODE] [PC] on [CM].[PROGRAMCODE_LOCALID] = [PC].[LOCALID]
left outer join [dbo].[RE7_MEMBERSHIPREASONCODE] [RC] on [CM].[REASONCODE_LOCALID] = [RC].[LOCALID]
left outer join [dbo].[RE7_MEMBERSHIPCATEGORYCODE] [CC] on [CM].[CATEGORYCODE_LOCALID] = [CC].[LOCALID]
left outer join [dbo].[RE7_MEMBERSHIPSUBCATEGORYCODE] [SC] on [CM].[SUBCATEGORYCODE_LOCALID] = [SC].[LOCALID];