UFN_MKTSEGMENTATIONPASSIVEPACKAGE_GETEXPECTEDRESPONSECOUNT

Returns the number of responses expected from a package used in a public media marketing effort.

Return

Return Type
int

Parameters

Parameter	Parameter Type	Mode
@SEGMENTATIONID	uniqueidentifier	IN
@PACKAGEID	uniqueidentifier	IN
@ACTIVE	bit	IN

Definition

Copy



CREATE function dbo.[UFN_MKTSEGMENTATIONPASSIVEPACKAGE_GETEXPECTEDRESPONSECOUNT]
(
  @SEGMENTATIONID uniqueidentifier,
  @PACKAGEID uniqueidentifier,
  @ACTIVE bit = null
)
returns integer
as
begin
  declare @RESPONSES integer;

  if @ACTIVE is null select @ACTIVE = [ACTIVE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

  select 
    @RESPONSES = sum(dbo.[UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETEXPECTEDRESPONSECOUNT](
                           [MKTSEGMENTATIONSEGMENT].[ID],
                           @ACTIVE,
                           case @ACTIVE when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONCALCULATIONMETHODCODE] else [MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHODCODE] end,
                           case @ACTIVE when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONS] else [MKTSEGMENTPASSIVE].[IMPRESSIONS] end,
                           [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
                           [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE],
                           [MKTSEGMENTATIONSEGMENT].[RESPONSERATE]))
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  left outer join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
  where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
  and [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = @PACKAGEID;

  return @RESPONSES;
end