UFN_MKTSEGMENTATIONASKLADDER_GETITEMS

Returns a table containing the segments and test segments for a given marketing effort, and whether or not they are assigned to a specific ask ladder.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@SEGMENTATIONID	uniqueidentifier	IN
@ASKLADDERID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_MKTSEGMENTATIONASKLADDER_GETITEMS
(
  @SEGMENTATIONID uniqueidentifier,
  @ASKLADDERID uniqueidentifier
)
returns table
as
  return 
  (
    select
      (case when [MKTSEGMENTATIONSEGMENT].[ASKLADDERID]=@ASKLADDERID then 1 else 0 end) [SELECTED]
      ,coalesce((select [NAME] from dbo.[MKTASKLADDER] where [ID]=[MKTSEGMENTATIONSEGMENT].[ASKLADDERID]),'<none>') [ASKLADDER]
      ,coalesce((select [RECORDSOURCEID] from dbo.[MKTASKLADDER] where [ID]=[MKTSEGMENTATIONSEGMENT].[ASKLADDERID]),'<none>') [ASKLADDERRECORDSOURCE]
      ,[MKTSEGMENT].[NAME]
      ,[MKTSEGMENT].[QUERYVIEWCATALOGID]
      ,(select [P].[NAME] from dbo.[MKTPACKAGE] [P] where [P].[ID]=[MKTSEGMENTATIONSEGMENT].[PACKAGEID]) [PACKAGE]
      ,0 [TYPE]
      ,[MKTSEGMENTATIONSEGMENT].[ID] [TYPEID]
    from
      dbo.[MKTSEGMENTATIONSEGMENT] 
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID]=[MKTSEGMENT].[ID]
    where 
      dbo.[MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]=@SEGMENTATIONID

    union select
      (case when [MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID]=@ASKLADDERID then 1 else 0 end) [SELECTED]
      ,coalesce((select [NAME] from dbo.[MKTASKLADDER] where [ID]=[MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID]),'<none>') [ASKLADDER]
      ,coalesce((select [RECORDSOURCEID] from dbo.[MKTASKLADDER] where [ID]=[MKTSEGMENTATIONSEGMENT].[ASKLADDERID]),'<none>') [ASKLADDERRECORDSOURCE]
      ,[MKTSEGMENT].[NAME] + ' \ ' +[MKTSEGMENTATIONTESTSEGMENT].[NAME]
      ,[MKTSEGMENT].[QUERYVIEWCATALOGID]
      ,(select [P].[NAME] from dbo.[MKTPACKAGE] [P] where [P].[ID]=[MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]) [PACKAGE]
      ,1 [TYPE]
      ,[MKTSEGMENTATIONTESTSEGMENT].[ID] [TYPEID]
    from
      dbo.[MKTSEGMENTATIONTESTSEGMENT]
      inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]=[MKTSEGMENTATIONSEGMENT].[ID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID]=[MKTSEGMENT].[ID]
    where 
      dbo.[MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]=@SEGMENTATIONID
  )