USP_MKTSEGMENTATION_GETOFFERCOUNT

CREATE procedure dbo.[USP_MKTSEGMENTATION_GETOFFERCOUNT]
(
  @SEGMENTATIONID uniqueidentifier,
  @PACKAGEID uniqueidentifier = null,
  @FORTESTSEGMENTS bit = 0,
  @FORACTIVATE bit = 0
)
as
  set nocount on;

  if @FORTESTSEGMENTS is null
    set @FORTESTSEGMENTS = 0;

  if @FORACTIVATE is null
    set @FORACTIVATE = 0;

  if @FORTESTSEGMENTS = 1
    --Grab all test segments under each segment where one of the test segments meets the criteria.

    --We need all the test segments so we can correctly calculate the number of offers for the parent segment.

    select
      [MKTSEGMENTATIONTESTSEGMENT].[ID],
      [MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZE],
      [MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZETYPECODE],
      [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID],
      [MKTSEGMENTATIONTESTSEGMENT].[RESPONSERATE],
      [MKTSEGMENTATIONTESTSEGMENT].[GIFTAMOUNT],
      [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] as [PARENTSEGMENTID],
      [MKTSEGMENTATIONSEGMENT].[SEGMENTID] as [MKTSEGMENTID],
      cast(null as bit) as [SAMPLESIZEEXCLUDEREMAINDER],
      [MKTSEGMENTATIONTESTSEGMENT].[FRACTION],
      dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ID]) as [NAME],
      [IDSETREGISTER].[RECORDTYPEID],
      (case when [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] > 0 then [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] else [MKTSEGMENT].[HISTORICALQUANTITY] end) as [HISTORICALQUANTITY]
    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
    where [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] in (select distinct [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
                                                       from dbo.[MKTSEGMENTATIONTESTSEGMENT]
                                                       inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
                                                       where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
                                                       and (@PACKAGEID is null or [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = @PACKAGEID)
                                                       and (@FORACTIVATE = 0 or dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]) = 0))  --Exclude vendor managed list segments

    order by [MKTSEGMENTATIONSEGMENT].[SEQUENCE], [MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE];
  else
    select
      [MKTSEGMENTATIONSEGMENT].[ID],
      [MKTSEGMENTATIONSEGMENT].[SAMPLESIZE],
      [MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE],
      [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
      [MKTSEGMENTATIONSEGMENT].[RESPONSERATE],
      [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT],
      cast(null as uniqueidentifier) as [PARENTSEGMENTID],
      [MKTSEGMENTATIONSEGMENT].[SEGMENTID] as [MKTSEGMENTID],
      [MKTSEGMENTATIONSEGMENT].[SAMPLESIZEEXCLUDEREMAINDER],
      cast(null as nvarchar(10)) as [FRACTION],
      [MKTSEGMENT].[NAME],
      [IDSETREGISTER].[RECORDTYPEID],
      (case when [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] > 0 then [MKTSEGMENTATIONSEGMENT].[HISTORICALQUANTITY] else [MKTSEGMENT].[HISTORICALQUANTITY] end) as [HISTORICALQUANTITY]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
    where [SEGMENTATIONID] = @SEGMENTATIONID
    and (@PACKAGEID is null or [PACKAGEID] = @PACKAGEID)
    and (@FORACTIVATE = 0 or dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]) = 0)  --Exclude vendor managed list segments

    order by [SEQUENCE];

  return 0;