USP_MKTSEGMENTATION_GETSEEDTOTALS

Returns the total count and cost for all seeds in a marketing effort, or package within the marketing effort.

Parameters

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

Definition

Copy



CREATE procedure dbo.[USP_MKTSEGMENTATION_GETSEEDTOTALS]
(
  @SEGMENTATIONID uniqueidentifier,
  @PACKAGEID uniqueidentifier = null
)
as
  set nocount on;

  declare @QUANTITY int;
  declare @TOTALCOST money;
  declare @ORGANIZATIONTOTALCOST money;

  select
    @QUANTITY = count(1),
    @TOTALCOST = isnull(sum(case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] else [MKTPACKAGE].[UNITCOST] end),0),
    @ORGANIZATIONTOTALCOST = isnull(sum(case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else [MKTPACKAGE].[ORGANIZATIONUNITCOST] end),0)
  from dbo.[MKTSEGMENTATIONPACKAGE]
  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONPACKAGE].[PACKAGEID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID]
  inner join dbo.[MKTSEGMENTATIONSEED] on [MKTSEGMENTATIONSEED].[SEGMENTATIONID] = [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID]
  where [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @SEGMENTATIONID
  and (@PACKAGEID is null or [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = @PACKAGEID)
  and [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] != 2;

  select
    isnull(@QUANTITY,0) as [QUANTITY],
    isnull(@TOTALCOST,0) as [TOTALCOST],
    isnull(@ORGANIZATIONTOTALCOST,0) as [ORGANIZATIONTOTALCOST];

  return 0;