UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETEXPECTEDRESPONSECOUNT

Returns the number of responses expected from a public media marketing effort segment.

Return

Return Type
int

Parameters

Parameter	Parameter Type	Mode
@SEGMENTATIONSEGMENTID	uniqueidentifier	IN
@ACTIVE	bit	IN
@IMPRESSIONCALCULATIONMETHODCODE	tinyint	IN
@IMPRESSIONS	int	IN
@EXPOSURESTARTDATE	date	IN
@EXPOSUREENDDATE	date	IN
@RESPONSERATE	decimal(5, 2)	IN

Definition

Copy



CREATE function dbo.[UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETEXPECTEDRESPONSECOUNT]
(
  @SEGMENTATIONSEGMENTID uniqueidentifier,
  @ACTIVE bit = null,
  @IMPRESSIONCALCULATIONMETHODCODE tinyint = null,
  @IMPRESSIONS integer = null,
  @EXPOSURESTARTDATE date = null,
  @EXPOSUREENDDATE date = null,
  @RESPONSERATE decimal(5, 2)
)
returns integer
as
begin
  declare @RESPONSES integer;

  set @RESPONSES = 0;

  if @ACTIVE is null 
    select top 1
      @ACTIVE = [MKTSEGMENTATION].[ACTIVE] 
    from dbo.[MKTSEGMENTATION] 
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] 
    where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;

  if @IMPRESSIONCALCULATIONMETHODCODE is null or @IMPRESSIONS is null or @EXPOSURESTARTDATE is null or @EXPOSUREENDDATE is null or @RESPONSERATE is null
    select
      @IMPRESSIONCALCULATIONMETHODCODE = case @ACTIVE when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONCALCULATIONMETHODCODE] else [MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHODCODE] end,
      @IMPRESSIONS = case @ACTIVE when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONS] else [MKTSEGMENTPASSIVE].[IMPRESSIONS] end,
      @EXPOSURESTARTDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
      @EXPOSUREENDDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE],
      @RESPONSERATE = [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].[ID] = @SEGMENTATIONSEGMENTID;

  if not (@IMPRESSIONCALCULATIONMETHODCODE is null or @IMPRESSIONS is null or @EXPOSURESTARTDATE is null or @EXPOSUREENDDATE is null)
    set @RESPONSES = floor((case @IMPRESSIONCALCULATIONMETHODCODE when 0 then (@IMPRESSIONS * (datediff(dd, @EXPOSURESTARTDATE, @EXPOSUREENDDATE) + 1)) else @IMPRESSIONS end) * (@RESPONSERATE / 100))

  return @RESPONSES;
end