V_QUERY_RE7_CONSTITUENTMEMBERSHIPSUMMARY
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENT_LOCALID | int | ||
| TRANSACTIONCOUNT | int | yes | |
| TOTALDUES | money | yes | |
| AVGDUES | money | yes | |
| MINDUES | money | yes | |
| MAXDUES | money | yes | |
| FIRSTDATE | datetime | yes | |
| LASTDATE | datetime | yes | |
| YEARSSINCE | int | ||
| MONTHSSINCE | int | ||
| DAYSSINCE | int | ||
| TRANSACTIONYEARS | int | yes | |
| DISTINCTCYEARS | int | yes | |
| DISTINCTFYEARS | int | yes | |
| AVGLATENCY | numeric(19, 4) | yes | |
| MINLATENCY | int | yes | |
| MAXLATENCY | int | yes | |
| FIRSTACTIVITY | nvarchar(100) | yes | |
| FIRSTAMOUNT | money | yes | |
| LASTACTIVITY | nvarchar(100) | yes | |
| LASTAMOUNT | money | 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_CONSTITUENTMEMBERSHIPSUMMARY AS
select
[CM].[CONSTITUENT_LOCALID],
[CM].[TRANSACTIONCOUNT],
[CM].[TOTALDUES],
[CM].[AVGDUES],
[CM].[MINDUES],
[CM].[MAXDUES],
[CM].[FIRSTDATE],
[CM].[LASTDATE],
isnull(case when current_timestamp < dateadd(year, datediff(year, [CM].[LASTDATE], current_timestamp), [CM].[LASTDATE]) then datediff(year, [CM].[LASTDATE], current_timestamp) - 1 else datediff(year, [CM].[LASTDATE], current_timestamp) end,0) [YEARSSINCE],
isnull(case when current_timestamp < dateadd(month, datediff(month, [CM].[LASTDATE], current_timestamp), [CM].[LASTDATE]) then datediff(month, [CM].[LASTDATE], current_timestamp) - 1 else datediff(month, [CM].[LASTDATE], current_timestamp) end,0) [MONTHSSINCE],
isnull(datediff(dd, [CM].[LASTDATE], current_timestamp),0) [DAYSSINCE],
[CM].[TRANSACTIONYEARS],
[CM].[DISTINCTCYEARS],
[CM].[DISTINCTFYEARS],
[CM].[AVGLATENCY],
[CM].[MINLATENCY],
[CM].[MAXLATENCY],
[CCF].[DESCRIPTION] [FIRSTACTIVITY],
[CM].[FIRSTAMOUNT],
[CCL].[DESCRIPTION] [LASTACTIVITY],
[CM].[LASTAMOUNT]
from [dbo].[RE7_CONSTITUENTLTMEMBERSHIP] [CM]
left outer join [dbo].[RE7_MEMBERSHIPCATEGORYCODE] [CCF] on [CM].[FIRSTACTIVITY_LOCALID] = [CCF].[LOCALID]
left outer join [dbo].[RE7_MEMBERSHIPCATEGORYCODE] [CCL] on [CM].[LASTACTIVITY_LOCALID] = [CCL].[LOCALID]