V_QUERY_MKTSEGMENTWHITEMAIL
Provides the ability to query white mail segments.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| SOURCECODE | nvarchar(50) | Source code | |
| NAME | nvarchar(100) | Name | |
| DESCRIPTION | nvarchar(255) | Description | |
| CATEGORY | nvarchar(100) | yes | Category |
| SEGMENTATIONID | uniqueidentifier | yes | Marketing effort ID |
| SEGMENTATION | nvarchar(100) | yes | Marketing effort |
| STATUS | nvarchar(22) | yes | Status |
| ACTIVEFROM | datetime | yes | Active from |
| ACTIVETO | datetime | yes | Active to |
| DATEREFRESHED | datetime | yes | Current as of |
| RESPONDERS | int | yes | Responders |
| RESPONSES | int | yes | Responses |
| TOTALGIFTAMOUNT | money | yes | Total revenue |
| AVERAGEGIFTAMOUNT | money | yes | Average revenue |
| SITEID | uniqueidentifier | yes | Site ID |
| BASECURRENCYID | uniqueidentifier | yes | Base currency |
| 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 |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| ORGANIZATIONTOTALGIFTAMOUNT | money | yes | Total revenue (organization currency) |
| ORGANIZATIONAVERAGEGIFTAMOUNT | money | yes | Average revenue (organization currency) |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:14:11 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MKTSEGMENTWHITEMAIL AS
select
[MKTSEGMENT].[ID],
[MKTSOURCECODEMAP].[SOURCECODE],
[MKTSEGMENT].[NAME],
[MKTSEGMENT].[DESCRIPTION],
[MKTSEGMENTCATEGORYCODE].[DESCRIPTION] as [CATEGORY],
[MKTSEGMENTWHITEMAIL].[SEGMENTATIONID],
[MKTSEGMENTATION].[NAME] as [SEGMENTATION],
[MKTSEGMENTWHITEMAIL].[STATUS],
[MKTSEGMENTWHITEMAIL].[ACTIVEFROM],
[MKTSEGMENTWHITEMAIL].[ACTIVETO],
[MKTSEGMENTWHITEMAILREFRESHPROCESS].[DATEREFRESHED],
[MKTSEGMENTWHITEMAILCACHE].[RESPONDERS],
[MKTSEGMENTWHITEMAILCACHE].[RESPONSES],
[MKTSEGMENTWHITEMAILCACHE].[TOTALGIFTAMOUNT],
[MKTSEGMENTWHITEMAILCACHE].[AVERAGEGIFTAMOUNT],
[MKTSEGMENT].[SITEID],
/* System fields */
[MKTSEGMENT].[BASECURRENCYID],
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[MKTSEGMENT].[DATEADDED],
[MKTSEGMENT].[DATECHANGED],
[MKTSEGMENT].[TSLONG],
[MKTSEGMENTWHITEMAILCACHE].[ORGANIZATIONTOTALGIFTAMOUNT],
[MKTSEGMENTWHITEMAILCACHE].[ORGANIZATIONAVERAGEGIFTAMOUNT]
/*#EXTENSION*/
from dbo.[MKTSEGMENTWHITEMAIL]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
inner join dbo.[MKTSOURCECODEMAP] on [MKTSOURCECODEMAP].[WHITEMAILSEGMENTID] = [MKTSEGMENTWHITEMAIL].[ID]
left outer join dbo.[MKTSEGMENTCATEGORYCODE] on [MKTSEGMENTCATEGORYCODE].[ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]
left outer join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID]
inner join dbo.[MKTSEGMENTWHITEMAILREFRESHPROCESS] on [MKTSEGMENTWHITEMAILREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENTWHITEMAIL].[ID]
left outer join dbo.[MKTSEGMENTWHITEMAILCACHE] on [MKTSEGMENTWHITEMAILCACHE].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [MKTSEGMENT].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [MKTSEGMENT].[CHANGEDBYID]
where [MKTSEGMENT].[SEGMENTTYPECODE] = 9;