V_QUERY_MKTSEGMENT
Provides the ability to query segments.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| NAME | nvarchar(100) | Name | |
| DESCRIPTION | nvarchar(255) | Description | |
| CATEGORY | nvarchar(100) | yes | Category |
| CODE | nvarchar(10) | Code | |
| ISINUSE | bit | yes | Is in use? |
| RECORDCOUNT | int | yes | Record count |
| DATEREFRESHED | datetime | yes | Current as of |
| SEGMENTTYPE | nvarchar(18) | yes | Segment type |
| LISTID | uniqueidentifier | yes | List record |
| LISTNAME | nvarchar(43) | yes | List name |
| TYPE | nvarchar(14) | yes | Type |
| STATUS | nvarchar(14) | yes | Status |
| ORDERDATE | datetime | yes | Order date |
| EXPIRATIONDATE | datetime | yes | Expiration date |
| NUMBEROFCONTACTS | smallint | yes | Number of contacts |
| LAYOUTNAME | nvarchar(50) | yes | Layout name |
| FILENAME | nvarchar(255) | yes | File name |
| RENTALQUANTITY | int | yes | Rental quantity |
| RENTALCOSTADJUSTMENT | money | yes | Rental cost adjustment |
| RENTALCOSTBASIS | nvarchar(12) | yes | Rental cost basis |
| EXCHANGEQUANTITY | int | yes | Exchange quantity |
| EXCHANGECOSTADJUSTMENT | money | yes | Exchange cost adjustment |
| EXCHANGECOSTBASIS | nvarchar(12) | yes | Exchange cost basis |
| RECEIVEDVIA | varchar(8) | yes | Received via |
| SITEID | uniqueidentifier | yes | Site ID |
| KPICONTEXTRECORDID | nvarchar(100) | yes | KPIs context record ID |
| 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 |
| ORGANIZATIONRENTALCOSTADJUSTMENT | money | yes | Rental cost adjustment (organization currency) |
| ORGANIZATIONEXCHANGECOSTADJUSTMENT | money | yes | Exchange cost adjustment (organization currency) |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| CURRENCYEXCHANGERATEID | uniqueidentifier | yes | Organization currency exchange rate ID |
| SEGMENTCATEGORYCODEID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:50 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MKTSEGMENT AS
select
[MKTSEGMENT].[ID],
[MKTSEGMENT].[NAME],
[MKTSEGMENT].[DESCRIPTION],
(select [DESCRIPTION] from dbo.[MKTSEGMENTCATEGORYCODE] where [ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]) as [CATEGORY],
[MKTSEGMENT].[CODE],
dbo.[UFN_MKTSEGMENT_ISINUSE]([MKTSEGMENT].[ID]) as [ISINUSE],
(case when [MKTSEGMENT].[SEGMENTTYPECODE] = 2 then [MKTSEGMENTLIST].[TOTALRECORDCOUNT] else [IDSETREGISTER].[NUMROWS] end) as [RECORDCOUNT],
coalesce([MKTSEGMENT].[DATEREFRESHED], [IDSETREGISTER].[DATECHANGED], [MKTSEGMENTLIST].[DATECHANGED]) as [DATEREFRESHED],
[MKTSEGMENT].[SEGMENTTYPE],
[MKTLIST].[ID] as [LISTID],
[MKTLIST].[NAME] as [LISTNAME],
[MKTSEGMENTLIST].[TYPE],
[MKTSEGMENTLIST].[STATUS],
[MKTSEGMENTLIST].[ORDERDATE],
[MKTSEGMENTLIST].[EXPIRATIONDATE],
[MKTSEGMENTLIST].[NUMBEROFCONTACTS],
(select [NAME] from dbo.[MKTLISTLAYOUT] where [ID] = [MKTSEGMENTLIST].[LISTLAYOUTID]) as [LAYOUTNAME],
[MKTSEGMENTLIST].[FILENAME],
[MKTSEGMENTLIST].[RENTALQUANTITY],
[MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT],
[MKTSEGMENTLIST].[RENTALCOSTBASIS],
[MKTSEGMENTLIST].[EXCHANGEQUANTITY],
[MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT],
[MKTSEGMENTLIST].[EXCHANGECOSTBASIS],
[MKTSEGMENTLIST].[RECEIVEDVIA],
/* hidden fields */
[MKTSEGMENT].[SITEID],
cast([MKTSEGMENT].[ID] as nvarchar(100)) as [KPICONTEXTRECORDID],
/* System fields */
[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],
[MKTSEGMENTLIST].[ORGANIZATIONRENTALCOSTADJUSTMENT],
[MKTSEGMENTLIST].[ORGANIZATIONEXCHANGECOSTADJUSTMENT],
[MKTSEGMENTLIST].[BASECURRENCYID],
[MKTSEGMENTLIST].[CURRENCYEXCHANGERATEID],
[MKTSEGMENT].[SEGMENTCATEGORYCODEID]
/*#EXTENSION*/
from dbo.[MKTSEGMENT]
left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
left outer join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
left outer join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
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] not in (6, 7, 8, 9) -- exclude public media and white mail segments
--exclude appeal mailing segments
and not exists(select 1
from dbo.[APPEALMAILING]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID]
where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID])
--exclude system records
and [MKTSEGMENT].[ISSYSTEM] = 0