USP_DATALIST_MKTSEGMENTATION_EXCLUSIONSSTATUS

CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTATION_EXCLUSIONSSTATUS]
(
  @EFFORTEXCLUSIONPROCESSSTATUSID uniqueidentifier
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @ENDEDON datetime;
  declare @NUMBERPROCESSED int;
  declare @SEGMENTCALCULATEPROCESSSTATUSID uniqueidentifier;
  declare @TABLENAME nvarchar(128);
  declare @SQL nvarchar(max);
  declare @MKTSEGMENTATIONEXCLUSIONSPROCESSID uniqueidentifier;

  --Left join to both process tables so we can use this datalist from both the business process status page and the report page...

  select
    @SEGMENTATIONID = isnull([MKTSEGMENTATIONEXCLUSIONSPROCESS].[SEGMENTATIONID], [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID]),
    @ENDEDON = [BUSINESSPROCESSSTATUS].[ENDEDON],
    @NUMBERPROCESSED = [BUSINESSPROCESSSTATUS].[NUMBERPROCESSED],
    @MKTSEGMENTATIONEXCLUSIONSPROCESSID = [MKTSEGMENTATIONEXCLUSIONSPROCESS].[ID]
  from dbo.[BUSINESSPROCESSSTATUS]
  left join dbo.[MKTSEGMENTATIONEXCLUSIONSPROCESS] on [MKTSEGMENTATIONEXCLUSIONSPROCESS].[ID] = [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID]
  left join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] = [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID]
  where [BUSINESSPROCESSSTATUS].[ID] = @EFFORTEXCLUSIONPROCESSSTATUSID;

  --Grab the segment counts status ID that was started before the exclusions process since the output table is now linked to the segment counts process...

  select top 1
    @SEGMENTCALCULATEPROCESSSTATUSID = [BUSINESSPROCESSSTATUS].[ID]
  from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
  inner join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[PARAMETERSETID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID]
  inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[ID]
  where [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = @SEGMENTATIONID
  and [BUSINESSPROCESSSTATUS].[STATUSCODE] = 0
  and [BUSINESSPROCESSSTATUS].[STARTEDON] < @ENDEDON
  order by [BUSINESSPROCESSSTATUS].[ENDEDON] desc;

  if @MKTSEGMENTATIONEXCLUSIONSPROCESSID is null
    begin
    -- The status id that was passed in was to the segmentation segment calculate process, not the

    -- segmentation exclusion process, so the previous @NUMBERPROCESSED is the number of segments 

    -- processed. Grab the correct value for @NUMBERPROCESSED here.

      select
        @TABLENAME = [BUSINESSPROCESSOUTPUT].[TABLENAME]
      from dbo.[BUSINESSPROCESSSTATUS]
      inner join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] = [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID]
      inner join dbo.[BUSINESSPROCESSOUTPUT] on [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID] = [BUSINESSPROCESSSTATUS].[ID]
      where [BUSINESSPROCESSSTATUS].[ID] = @SEGMENTCALCULATEPROCESSSTATUSID
      and [BUSINESSPROCESSOUTPUT].[TABLEKEY] = 'EFFORTEXCLUSIONS';

      set @SQL = 'select @NUMBERPROCESSED = count(*) from dbo.[' + @TABLENAME + ']';
      exec sp_executesql @SQL, N'@NUMBERPROCESSED int output', @NUMBERPROCESSED = @NUMBERPROCESSED output;
  end

  select
    [BUSINESSPROCESSSTATUS].[ID],
    [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID],
    [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSCATALOGID],
    [BUSINESSPROCESSOUTPUT].[TABLEKEY],
    [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[NAME],
    @NUMBERPROCESSED,
    [BUSINESSPROCESSSTATUS].[STATUS],
    [BUSINESSPROCESSSTATUS].[STARTEDON]
  from dbo.[BUSINESSPROCESSSTATUS]
  inner join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] = [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID]
  inner join dbo.[BUSINESSPROCESSOUTPUT] on [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID] = [BUSINESSPROCESSSTATUS].[ID]
 where [BUSINESSPROCESSSTATUS].[ID] = @SEGMENTCALCULATEPROCESSSTATUSID
  and [BUSINESSPROCESSOUTPUT].[TABLEKEY] = 'EFFORTEXCLUSIONS';

  return 0;