UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETQUANTITY

Returns the number of impressions provided by 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

Definition

Copy



CREATE function dbo.[UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETQUANTITY]
(
  @SEGMENTATIONSEGMENTID uniqueidentifier,
  @ACTIVE bit = null,
  @IMPRESSIONCALCULATIONMETHODCODE tinyint = null,
  @IMPRESSIONS integer = null,
  @EXPOSURESTARTDATE date = null,
  @EXPOSUREENDDATE date = null
)
returns integer
as
begin
  declare @QUANTITY integer;

  set @QUANTITY = 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
    if @ACTIVE = 1 and exists (select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENTACTIVE] where [SEGMENTID] = @SEGMENTATIONSEGMENTID)
      select
        @IMPRESSIONCALCULATIONMETHODCODE = [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONCALCULATIONMETHODCODE],
        @IMPRESSIONS = [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONS],
        @EXPOSURESTARTDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
        @EXPOSUREENDDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;
    else
      select
        @IMPRESSIONCALCULATIONMETHODCODE = [MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHODCODE],
        @IMPRESSIONS = [MKTSEGMENTPASSIVE].[IMPRESSIONS],
        @EXPOSURESTARTDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
        @EXPOSUREENDDATE = [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE]
      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 @QUANTITY = case @IMPRESSIONCALCULATIONMETHODCODE when 0 then (@IMPRESSIONS * (datediff(dd, @EXPOSURESTARTDATE, @EXPOSUREENDDATE) + 1)) else @IMPRESSIONS end

  return @QUANTITY;
end