V_QUERY_APPEALMAILING
This provides the ability to query for mailings associated with appeals
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| NAME | nvarchar(100) | Name | |
| DESCRIPTION | nvarchar(255) | Description | |
| ACTIVE | bit | Active | |
| DATESENT | datetime | yes | Date sent |
| APPEALID | uniqueidentifier | Appeal | |
| ACTIVATEDATE | datetime | yes | Activate date |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| MKTPACKAGEID | uniqueidentifier | yes | Package system record ID |
| MKTPACKAGE_DESCRIPTION | nvarchar(100) | yes | Package |
| MAILDATE | datetime | yes | Mail date |
| HOUSEHOLDINGTYPE | nvarchar(49) | yes | Householding rules |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| RESPONDERS | int | yes | Responders |
| RESPONSES | int | yes | Responses |
| COSTPERDOLLARRAISED | money | yes | Cost/dollar raised |
| TOTALGIFTAMOUNT | money | yes | Total gift amount |
| AVERAGEGIFTAMOUNT | money | yes | Average gift amount |
| RESPONSERATE | decimal(23, 8) | yes | Response rate |
| ROIAMOUNT | money | yes | ROI (Amount) |
| ROIPERCENT | decimal(23, 8) | yes | ROI (Percent) |
| INDIRECTRESPONDERS | int | yes | Indirect responders |
| INDIRECTRESPONSES | int | yes | Indirect responses |
| INDIRECTTOTALGIFTAMOUNT | money | yes | Indirect total gift amount |
| INDIRECTAVERAGEGIFTAMOUNT | money | yes | Indirect average gift amount |
| UNRESOLVEDRESPONDERS | int | yes | Unresolved responders |
| UNRESOLVEDRESPONSES | int | yes | Unresolved responses |
| UNRESOLVEDTOTALGIFTAMOUNT | money | yes | Unresolved total gift amount |
| UNRESOLVEDAVERAGEGIFTAMOUNT | money | yes | Unresolved average gift amount |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 10/27/2018 10:42:47 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.186.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_APPEALMAILING AS
select
[MKTSEGMENTATION].[ID],
[MKTSEGMENTATION].[NAME],
[MKTSEGMENTATION].[DESCRIPTION],
[MKTSEGMENTATION].[ACTIVE],
(select top 1 [CONSTITUENTAPPEAL].[DATESENT] from dbo.[CONSTITUENTAPPEAL] where [CONSTITUENTAPPEAL].[MKTSEGMENTATIONID] = [MKTSEGMENTATION].ID) as [DATESENT],
[APPEALMAILING].[APPEALID],
[MKTSEGMENTATION].[ACTIVATEDATE],
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
(select top 1 [PACKAGEID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].ID) as [MKTPACKAGEID],
(select top 1 dbo.[UFN_MKTPACKAGE_GETNAME]([PACKAGEID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [MKTPACKAGE_DESCRIPTION],
[MKTSEGMENTATION].[MAILDATE],
coalesce([APPEALMAILINGSETUP].[HOUSEHOLDINGTYPE], [MKTSEGMENTATION].[HOUSEHOLDINGTYPE]) as [HOUSEHOLDINGTYPE],
[MKTSEGMENTATION].[DATEADDED],
[MKTSEGMENTATION].[DATECHANGED],
[MKTSEGMENTATION].[TSLONG],
/* Calculation fields */
[MKTSEGMENTATIONACTIVE].[RESPONDERS],
[MKTSEGMENTATIONACTIVE].[RESPONSES],
[MKTSEGMENTATIONACTIVE].[COSTPERDOLLARRAISED],
[MKTSEGMENTATIONACTIVE].[TOTALGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[AVERAGEGIFTAMOUNT],
([MKTSEGMENTATIONACTIVE].[RESPONSERATE] / 100) as [RESPONSERATE],
[MKTSEGMENTATIONACTIVE].[ROIAMOUNT],
([MKTSEGMENTATIONACTIVE].[ROIPERCENT] / 100) as [ROIPERCENT],
[MKTSEGMENTATIONACTIVE].[INDIRECTRESPONDERS],
[MKTSEGMENTATIONACTIVE].[INDIRECTRESPONSES],
[MKTSEGMENTATIONACTIVE].[INDIRECTTOTALGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[INDIRECTAVERAGEGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[UNRESOLVEDRESPONDERS],
[MKTSEGMENTATIONACTIVE].[UNRESOLVEDRESPONSES],
[MKTSEGMENTATIONACTIVE].[UNRESOLVEDTOTALGIFTAMOUNT],
[MKTSEGMENTATIONACTIVE].[UNRESOLVEDAVERAGEGIFTAMOUNT],
[MKTSEGMENTATION].[BASECURRENCYID]
/*#EXTENSION*/
from dbo.[APPEALMAILING]
left join dbo.[APPEALMAILINGSETUP] on [APPEALMAILING].ID = [APPEALMAILINGSETUP].ID
inner join dbo.[MKTSEGMENTATION] on [APPEALMAILING].ID = [MKTSEGMENTATION].ID
left outer join dbo.[MKTSEGMENTATIONACTIVE] on [MKTSEGMENTATIONACTIVE].[ID] = [MKTSEGMENTATION].[ID]
left join dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = [MKTSEGMENTATION].ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = [MKTSEGMENTATION].CHANGEDBYID;