V_QUERY_REVENUESEGMENT
Provides the ability to query marketing information (such as mailings, segments, and source code parts) related to revenue from the 'Revenue Marketing' source view.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| REVENUEID | uniqueidentifier | Revenue ID | |
| SEGMENTATIONID | uniqueidentifier | Mailing ID | |
| SEGMENTID | uniqueidentifier | Segment ID | |
| TESTSEGMENTID | uniqueidentifier | yes | Test segment ID |
| SOURCECODE | nvarchar(50) | Source code | |
| MAILINGCODE | nvarchar(11) | yes | Mailing |
| SEGMENTCODE | nvarchar(11) | yes | Segment |
| PACKAGECODE | nvarchar(11) | yes | Package |
| CHANNELCODE | nvarchar(11) | yes | Channel |
| LISTCODE | nvarchar(11) | yes | List |
| TESTSEGMENTCODE | nvarchar(11) | yes | Test Segment |
| MAILINGCODENAME | int | yes | Mailing code name |
| CHANNELCODENAME | int | yes | Channel code name |
| SEGMENTCODENAME | int | yes | Segment code name |
| PACKAGECODENAME | int | yes | Package code name |
| LISTCODENAME | int | yes | List code name |
| USERDEFINEDCODENAME | int | yes | User defined code name |
| USERDEFINEDCODE | int | yes | User defined code |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 1/27/2010 7:34:27 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.5.465.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REVENUESEGMENT AS
select
[REVENUESEGMENT].[REVENUEID],
[MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
[REVENUESEGMENT].[SEGMENTID],
[REVENUESEGMENT].[TESTSEGMENTID],
[SCM].[SOURCECODE],
[SCM].[MAILINGCODE],
[SCM].[SEGMENTCODE],
[SCM].[PACKAGECODE],
[SCM].[CHANNELCODE],
[SCM].[LISTCODE],
[SCM].[TESTSEGMENTCODE],
--Begin obsolete fields--
null as [MAILINGCODENAME],
null as [CHANNELCODENAME],
null as [SEGMENTCODENAME],
null as [PACKAGECODENAME],
null as [LISTCODENAME],
null as [USERDEFINEDCODENAME],
null as [USERDEFINEDCODE]
--End obsolete fields--
/*#EXTENSION*/
from (
select
[ID],
[SOURCECODE],
[Mailing] as [MAILINGCODE],
[Segment] as [SEGMENTCODE],
[Package] as [PACKAGECODE],
[Channel] as [CHANNELCODE],
[List] as [LISTCODE],
[Test Segment] as [TESTSEGMENTCODE]
from (
select
[MKTSOURCECODEMAP].[ID],
[MKTSOURCECODEMAP].[SOURCECODE],
[MKTSOURCECODEPARTDEFINITION].[NAME],
[MKTSOURCECODEMAPPART].[CODE]
from dbo.[MKTSOURCECODEMAP]
inner join dbo.[MKTSOURCECODEMAPPART] on [MKTSOURCECODEMAPPART].[MKTSOURCECODEMAPID] = [MKTSOURCECODEMAP].[ID]
inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEMAPPART].[MKTSOURCECODEPARTDEFINITIONID]
) as [SOURCECODES]
pivot
(
max([CODE])
for [NAME]
in ([Mailing], [Segment], [Package], [Channel], [List], [Test Segment])
) as [PIVOTTABLE]
) as [SCM]
inner join dbo.[REVENUESEGMENT] on [REVENUESEGMENT].[SOURCECODEMAPID] = [SCM].[ID]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [REVENUESEGMENT].[SEGMENTID]