UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETRESPONSECOUNT

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
@IMPRESSIONCALCULATIONMETHODCODE	tinyint	IN
@IMPRESSIONS	int	IN
@EXPOSURESTARTDATE	date	IN
@EXPOSUREENDDATE	date	IN
@RESPONSERATE	decimal(5, 2)	IN

Definition

Copy



create function dbo.[UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETRESPONSECOUNT]
(
  @SEGMENTATIONSEGMENTID uniqueidentifier,
  @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 @IMPRESSIONCALCULATIONMETHODCODE is null or @IMPRESSIONS is null or @EXPOSURESTARTDATE is null or @EXPOSUREENDDATE is null or @RESPONSERATE is null
    select
      @IMPRESSIONCALCULATIONMETHODCODE = [MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHODCODE],
      @IMPRESSIONS = [MKTSEGMENTPASSIVE].[IMPRESSIONS],
      @EXPOSURESTARTDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
      @EXPOSUREENDDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE],
      @RESPONSERATE = [MKTSEGMENTATIONSEGMENT].[RESPONSERATE]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    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