USP_MKTSEGMENT_FINDDUPLICATES

CREATE procedure dbo.[USP_MKTSEGMENT_FINDDUPLICATES]
(
  @ID uniqueidentifier = null,
  @SEGMENTTYPECODE tinyint,
  @CODE nvarchar(10) = null,
  @SELECTIONS xml = null,
  @LISTID uniqueidentifier = null,
  @MAXROWS smallint = 100
)
as
  set nocount on;

  declare @SEGMENTSWITHDUPLICATESELECTIONS table ([ID] uniqueidentifier primary key);
  declare @SEGMENTSWITHDUPLICATECODES table ([ID] uniqueidentifier primary key);


  --If selections are specified, then gather all segments that have duplicate selections...

  if isnull(cast(@SELECTIONS as nvarchar(max)), '') <> ''
    begin
      declare @COUNT integer;

      select
        @COUNT = count(*)
      from dbo.[UFN_MKTSEGMENT_GETSELECTIONS_FROMITEMLISTXML](@SELECTIONS);

      insert into @SEGMENTSWITHDUPLICATESELECTIONS ([ID])
        select distinct [MKTSEGMENTSELECTION].[SEGMENTID]
        from dbo.[MKTSEGMENTSELECTION]
        inner join dbo.[UFN_MKTSEGMENT_GETSELECTIONS_FROMITEMLISTXML](@SELECTIONS) as [SELECTIONS] on [SELECTIONS].[SELECTIONID] = [MKTSEGMENTSELECTION].[SELECTIONID]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTSELECTION].[SEGMENTID]
        where (@ID is null or [MKTSEGMENTSELECTION].[SEGMENTID] <> @ID)
        and (select count(*) from dbo.[MKTSEGMENTSELECTION] as [SS] where [SS].[SEGMENTID] = [MKTSEGMENTSELECTION].[SEGMENTID]) = @COUNT
        and [MKTSEGMENT].[ISSYSTEM] = 0
        group by [MKTSEGMENTSELECTION].[SEGMENTID]
        having count([MKTSEGMENTSELECTION].[SEGMENTID]) = @COUNT;
    end


  --If a code is specified, then gather all segments that have a duplicate code...

  if isnull(@CODE, '') <> '' and @SEGMENTTYPECODE is not null
    begin
      if @SEGMENTTYPECODE = 1  --Constituent

        insert into @SEGMENTSWITHDUPLICATECODES ([ID])
          select [ID]
          from dbo.[MKTSEGMENT]
          where (@ID is null or [ID] <> @ID)
          and [SEGMENTTYPECODE] in (1, 2)
          and [ISSYSTEM] = 0
          and [CODE] = @CODE;

      else if @SEGMENTTYPECODE = 2  --List

        begin
          --A list segment's code is required and must be not be in use by a constituent segment 

          --and must be unique amongst all segments with the same parent list.

          if @LISTID is not null
            insert into @SEGMENTSWITHDUPLICATECODES ([ID])
              --Check against all constituent segments...

              select [ID]
              from dbo.[MKTSEGMENT]
              where (@ID is null or [ID] <> @ID)
              and [SEGMENTTYPECODE] = 1
              and [ISSYSTEM] = 0
              and [CODE] = @CODE

              union all

              --Check against all list segments from the same parent list...

              select [MKTSEGMENT].[ID]
              from dbo.[MKTSEGMENT]
              inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
              where (@ID is null or [MKTSEGMENT].[ID] <> @ID)
              and [SEGMENTTYPECODE] = 2
              and [MKTSEGMENT].[ISSYSTEM] = 0
              and [MKTSEGMENTLIST].[LISTID] = @LISTID
              and [MKTSEGMENT].[CODE] = @CODE;
        end

      else if @SEGMENTTYPECODE in (6, 7, 8)  --Public media

        insert into @SEGMENTSWITHDUPLICATECODES ([ID])
          select [ID]
          from dbo.[MKTSEGMENT]
          where (@ID is null or [ID] <> @ID)
          and [SEGMENTTYPECODE] in (6, 7, 8)
          and [ISSYSTEM] = 0
          and [CODE] = @CODE;

      else  --All other segment types

        insert into @SEGMENTSWITHDUPLICATECODES ([ID])
          select [ID]
          from dbo.[MKTSEGMENT]
          where (@ID is null or [ID] <> @ID)
          and [SEGMENTTYPECODE] = @SEGMENTTYPECODE
          and [ISSYSTEM] = 0
          and [CODE] = @CODE;
    end;


  --Find all segments that meet any of the duplicate criteria (ie: selections, code)

  with [DUPSEGMENTS] ([SEGMENTID], [DUPLICATESELECTION], [DUPLICATECODE]) as
  (
    select [ID], 1, 0 from @SEGMENTSWITHDUPLICATESELECTIONS
    union all
    select [ID], 0, 1 from @SEGMENTSWITHDUPLICATECODES  
  ),
  [DUPLICATES] ([SEGMENTID], [DUPLICATESELECTION], [DUPLICATECODE]) as
  (
    --Flatten/merge the results from the DUPSEGMENTS 'with' block...

    select
      [SEGMENTID],
      cast(max([DUPLICATESELECTION]) as bit) as [DUPLICATESELECTION],
      cast(max([DUPLICATECODE]) as bit) as [DUPLICATECODE]
    from [DUPSEGMENTS]
    group by [SEGMENTID]
  )
  select top (@MAXROWS)
    [MKTSEGMENT].[ID], 
    [MKTSEGMENT].[NAME], 
    [MKTSEGMENT].[DESCRIPTION], 
    [MKTSEGMENT].[CODE],
    dbo.[UFN_MKTSEGMENT_ISINUSE]([MKTSEGMENT].[ID]) as [INUSE],
    [DUPLICATES].[DUPLICATESELECTION],
    [DUPLICATES].[DUPLICATECODE]
  from dbo.[MKTSEGMENT]
  inner join [DUPLICATES] on [DUPLICATES].[SEGMENTID] = [MKTSEGMENT].[ID]
  where not exists(
    --Exclude appeal mailing segments

    select 1
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[APPEALMAILING] on [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]
  )
  and [MKTSEGMENT].[ISSYSTEM] = 0
  order by [MKTSEGMENT].[NAME];

  return 0;