USP_CONSTITUENTDUPLICATESEARCHPROCESS_GETSOUNDEXLIST

CREATE procedure dbo.USP_CONSTITUENTDUPLICATESEARCHPROCESS_GETSOUNDEXLIST    
(    
 @CONTEXTID uniqueidentifier    
)    
as    
begin    
 set nocount on;

 declare @ISINCREMENTAL bit = 0    
 declare @IDSETREGISTERID uniqueidentifier = null    
 declare @LASTRUNON datetime = null    
 declare @LIMITED bit = 0    

 select    
  @ISINCREMENTAL =     
   case     
    when ISINCREMENTAL = 1 and LASTRUNON is null then 0     
    else ISINCREMENTAL    
   end,    
  @LASTRUNON = LASTRUNON,    
  @IDSETREGISTERID = IDSETREGISTERID,    
  @LIMITED = case when IDSETREGISTERID is not null then 1 else 0 end    
 from dbo.CONSTITUENTDUPLICATESEARCHPROCESS    
 where ID = @CONTEXTID;    

 if @ISINCREMENTAL = 0 and @LIMITED = 0    
 begin    
 with CTE_CONSTITUENTS as  
 (select case when CONSTITUENT.ISGROUP = 1 and GROUPDATA.GROUPTYPECODE = 0 then  
  CONSTITUENTMEMBER.KEYNAMESOUNDEX  
 else  
  CONSTITUENT.KEYNAMESOUNDEX  
  end KEYNAMESOUNDEX  
  from dbo.CONSTITUENT  
  left join GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID  
  left join GROUPMEMBER on GROUPMEMBER.GROUPID = GROUPDATA.ID and GROUPMEMBER.ISPRIMARY = 1  
  left join CONSTITUENT CONSTITUENTMEMBER on CONSTITUENTMEMBER.ID = GROUPMEMBER.MEMBERID  
  )  
  select     
   KEYNAMESOUNDEX,
   count(1) as SOUNDEXCOUNT
  from CTE_CONSTITUENTS  
  group by KEYNAMESOUNDEX 
  having count(1) > 1 -- More than one constituent in the group    

  order by SOUNDEXCOUNT desc;
 end    
 else    
 begin    
  with CTE_CONSTITUENTS as    
  (    
   select    
    case when CONSTITUENT.ISGROUP = 1 and GROUPDATA.GROUPTYPECODE = 0 then  
  CONSTITUENTMEMBER.KEYNAMESOUNDEX  
 else  
  CONSTITUENT.KEYNAMESOUNDEX  
  end KEYNAMESOUNDEX  
    ,  
    case     
     when @LIMITED = 0 or LIMIT.ID is not null then    
      case    
       when @ISINCREMENTAL = 0 then 1    
       else    
        case    
         when     
          CONSTITUENT.DATEADDED > @LASTRUNON or    
          CONSTITUENT.DATECHANGED > @LASTRUNON or    
          (    
           PHONE.ID is not null and     
           (    
            PHONE.DATEADDED > @LASTRUNON or    
            PHONE.DATECHANGED > @LASTRUNON    
           )     
          ) or    
          (    
           ADDRESS.ID is not null and     
           (    
            ADDRESS.DATEADDED > @LASTRUNON or    
            ADDRESS.DATECHANGED > @LASTRUNON    
           )    
          ) or    
          (    
           EMAILADDRESS.ID is not null and     
           (    
            EMAILADDRESS.DATEADDED > @LASTRUNON or    
            EMAILADDRESS.DATECHANGED > @LASTRUNON    
           )    
          )    
           then 1    
         else 0    
        end    
      end    
     else 0     
    end as CHECKFORMATCH    
   from dbo.CONSTITUENT    
   left join GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID  
 left join GROUPMEMBER on GROUPMEMBER.GROUPID = GROUPDATA.ID and GROUPMEMBER.ISPRIMARY = 1  
 left join CONSTITUENT CONSTITUENTMEMBER on CONSTITUENTMEMBER.ID = GROUPMEMBER.MEMBERID  

   left join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) LIMIT on    
    LIMIT.ID = CONSTITUENT.ID    
   left join dbo.PHONE on    
    PHONE.CONSTITUENTID = CONSTITUENT.ID and    
    PHONE.ISPRIMARY = 1    
   left join dbo.ADDRESS on    
    ADDRESS.CONSTITUENTID = CONSTITUENT.ID and    
    ADDRESS.ISPRIMARY = 1    
   left join dbo.EMAILADDRESS on    
    EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and    
    EMAILADDRESS.ISPRIMARY = 1    
  )     
  select
   KEYNAMESOUNDEX,
   count(1) as SOUNDEXCOUNT
  from CTE_CONSTITUENTS
  where
   CHECKFORMATCH = 1
  group by KEYNAMESOUNDEX
  having count(1) > 0 -- At least one check for match

  order by SOUNDEXCOUNT desc;
 end

end