USP_DATALIST_REMOVEMEMBERSCOUNTS

CREATE procedure dbo.[USP_DATALIST_REMOVEMEMBERSCOUNTS]
(
  @SEGMENTATIONID uniqueidentifier,
  @STATUSID uniqueidentifier = null
)
with execute as owner
as
  set nocount on;

  declare @TEMP table ([NAME] nvarchar(255), [SEGMENT] nvarchar(255), [SOURCECODE] nvarchar(255), [ORIGINAL] int, [DELETED] int, [RESULTING] int);
  declare @DATATABLE nvarchar(128);
  declare @EXCLTABLE nvarchar(128);
  declare @PROCESSDATE datetime;
  declare @SEGMENTATIONEXCLUSIONID uniqueidentifier;

  if @STATUSID is not null 
  begin
    set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);
    set @EXCLTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME](@SEGMENTATIONID);
    select @SEGMENTATIONEXCLUSIONID = [ID], @PROCESSDATE = [DATEADDED]  from dbo.[MKTSEGMENTATIONEXCLUSION] where [STATUSID] = @STATUSID;

    declare @SQL nvarchar(4000);

    set @SQL = 
      'with EXCLUSION([SEGMENTID], [SOURCECODE], [DELETED])' + char(13) +
      'as' + char(13) +
      '(' + char(13) +
      '  select [SEGMENTID], [SOURCECODE], count(1)' + char(13) +
      '  from dbo.[' + @EXCLTABLE + ']' + char(13) +
      '  where [SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID' + char(13) +
      '  group by [SEGMENTID], [SOURCECODE]' + char(13) +
      '),' + char(13) +
      'POST([SEGMENTID], [DELETED])' + char(13) +
      'as' + char(13) +
      '(' + char(13) +
      '  select [PREV].[SEGMENTID],  count(1)' + char(13) +
      '  from dbo.[' + @EXCLTABLE + '] [PREV]' + char(13) +
      '  inner join dbo.[MKTSEGMENTATIONEXCLUSION] on [PREV].[SEGMENTATIONEXCLUSIONID] = [MKTSEGMENTATIONEXCLUSION].[ID]' + char(13) +
      '  where [MKTSEGMENTATIONEXCLUSION].[DATEADDED] > @PROCESSDATE' + char(13) +
      '  group by [PREV].[SEGMENTID]' + char(13) +
      '),' + char(13) +
      'DATA([SEGMENTID],[CURRENT])' + char(13) +
      'as' + char(13) +
      '(' + char(13) +
      '   select [SEGMENTID], count(1)' + char(13) +
      '   from dbo.[' + @DATATABLE + '] [DATA] ' + char(13) +
      '   group by [DATA].[SEGMENTID]' + char(13) +
      ')' + char(13) +
      'select' + char(13) +
      '  [MKTSEGMENTATION].[NAME],' + char(13) +
      '  [MKTSEGMENT].[NAME],' + char(13) +
      '  [EXCLUSION].[SOURCECODE],' + char(13) +
      '  [EXCLUSION].[DELETED] + isnull([DATA].[CURRENT],0) + isnull([POST].[DELETED],0),' + char(13) +
      '  [EXCLUSION].[DELETED],' + char(13) +
      '  isnull([DATA].[CURRENT],0) + isnull([POST].[DELETED],0)' + char(13) +
      'from' + char(13) +
      '  dbo.[MKTSEGMENTATION] ' + char(13) +
      '  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]' + char(13) +
      '  inner join dbo.[MKTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]' + char(13) +
      '  inner join [EXCLUSION] on [EXCLUSION].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]' + char(13) +
      '  left join [DATA] on [EXCLUSION].[SEGMENTID] = [DATA].[SEGMENTID]' + char(13) +
      '  left join [POST] on [EXCLUSION].[SEGMENTID] = [POST].[SEGMENTID]';

    insert into @TEMP
      exec sp_executesql @SQL, N'@SEGMENTATIONEXCLUSIONID uniqueidentifier, @PROCESSDATE datetime', @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID, @PROCESSDATE = @PROCESSDATE;
  end

  if exists (select 1 from @TEMP)
    select * from @TEMP;
  else
    select [NAME], null as [SEGMENT], null as [SOURCECODE], null as [ORIGINAL], null as [DELETED], null as [RESULTING] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

  return 0;