UFN_CONSTITUENT_GETFUZZYDUPLICATES_2

    create function [dbo].[UFN_CONSTITUENT_GETFUZZYDUPLICATES_2](
        @KEYNAME nvarchar(100),
        @FIRSTNAME nvarchar(50) = '',
        @POSTCODE nvarchar(12) = '',
        @ADDRESSBLOCK nvarchar(150) = '',
        @MAIDENNAME nvarchar(50) = '',
        @MIDDLENAME nvarchar(50) = '',
        @PHONENUMBER nvarchar(50) = '',
        @EMAILADDRESS nvarchar(100) = '',
        @GENDERCODE tinyint = null,
        @CITY nvarchar(100) = '',
        @STATEID uniqueidentifier = null,
        @COUNTRYID uniqueidentifier = null,
        @EMPLOYER nvarchar(100) = '',
        @DUPLICATESEARCHSETTINGID uniqueidentifier = null
    ) 
    returns @DUPLICATECANDIDATES table(
        CONSTITUENTID uniqueidentifier,
        ADDRESSID uniqueidentifier,
        MATCHPERCENTAGE numeric(5, 2)
    ) as
    begin

        declare @LEFTPOSTCODECOUNT int;
        declare @OVERALLMATCHTHRESHOLD numeric(5, 2);
        declare @TRANSACTIONTYPE nvarchar(100);

        if @DUPLICATESEARCHSETTINGID is null
            set @TRANSACTIONTYPE = 'Default Criteria'
        else
            select top(1) 
                @TRANSACTIONTYPE = TRANSACTIONTYPE
            from 
                dbo.CONSTITUENTDUPLICATESEARCHSETTINGS DSS
            where 
                ID = @DUPLICATESEARCHSETTINGID
            order by
                DSS.DATECHANGED;

        select top(1)
            @LEFTPOSTCODECOUNT = DSS.LEFTPOSTCODECOUNT,
            @OVERALLMATCHTHRESHOLD = DSS.OVERALLMATCHTHRESHOLD
        from
            dbo.CONSTITUENTDUPLICATESEARCHSETTINGS DSS
        where 
            TRANSACTIONTYPE = @TRANSACTIONTYPE
        order by
            DSS.DATECHANGED;

        if @LEFTPOSTCODECOUNT = null
            set @LEFTPOSTCODECOUNT = 3;

        if @OVERALLMATCHTHRESHOLD = null
            set @OVERALLMATCHTHRESHOLD = 70;            

        declare 
            @ISORGANIZATION bit = 0,
            @CONFIG_INCLUDENICKNAME as bit = 0,
            @CONFIG_INCLUDEALIAS  as bit = 0,
            @CONFIG_INCLUDEINACTIVE as bit = 0,
            @CONFIG_INCLUDEDECEASED as bit = 0;

        if @TRANSACTIONTYPE <> 'Default Criteria'
            begin                        
                select  
                      @CONFIG_INCLUDEINACTIVE = case when @ISORGANIZATION=1 then 0 else [INACTIVE] end,  
                      @CONFIG_INCLUDEDECEASED = case when @ISORGANIZATION=1 then 0 else [DECEASED] end,     
                      @CONFIG_INCLUDENICKNAME = case when @ISORGANIZATION=1 then 0 else [INCLUDENICKNAME] end,
                      @CONFIG_INCLUDEALIAS = case when @ISORGANIZATION=1 then 0 else [INCLUDEALIAS] end
                from  dbo.CONSTITUENTDUPLICATESEARCHSETTINGS    
                where [TRANSACTIONTYPE] = @TRANSACTIONTYPE;                    
            end

        declare @KEYNAMESOUNDEX varchar(5);
        set @KEYNAMESOUNDEX = soundex(@KEYNAME);

        declare @LEFTPOSTCODE varchar(6);
        set @LEFTPOSTCODE = left(@POSTCODE, @LEFTPOSTCODECOUNT) + '%';

        with [CTE_CONSTITUENT_DECEASED]
        as
        (
        select
            C.ID,
            case when DC.ID is null then 0 else 1 end as ISDECEASED
        from 
            dbo.CONSTITUENT C 
            left join dbo.DECEASEDCONSTITUENT DC on C.ID = DC.ID
        ),

        [CTE_CONSTITUENT_WITH_EXACT_MATCH]
        as
        (
        select
            C.ID,
            C.FIRSTNAME,
            C.MIDDLENAME,
            C.KEYNAME,
            C.MAIDENNAME,
            C.NICKNAME,
            C.GENDERCODE,
            C.KEYNAMESOUNDEX,
            C.ISGROUP,
            C.ISORGANIZATION
        from 
            dbo.CONSTITUENT C
            inner join [CTE_CONSTITUENT_DECEASED] CTE on C.ID = CTE.ID

        where
            C.ISINACTIVE = case when @CONFIG_INCLUDEINACTIVE = 1 then C.ISINACTIVE else 0 end
            and CTE.ISDECEASED = case when @CONFIG_INCLUDEDECEASED = 1 then CTE.ISDECEASED else 0 end        
        ),

        [CTE_CONSTITUENT_WITH_ALIAS_AND_NICKNAMES]
        as
        (
        select
            ID,
            FIRSTNAME,
            MIDDLENAME,
            KEYNAME,
            MAIDENNAME,
            GENDERCODE,
            KEYNAMESOUNDEX,
            ISGROUP,
            ISORGANIZATION
        from
            [CTE_CONSTITUENT_WITH_EXACT_MATCH]
        union all
        select
            C.ID,
            A.FIRSTNAME,
            A.MIDDLENAME,
            A.KEYNAME,
            C.MAIDENNAME,
            C.GENDERCODE,
            C.KEYNAMESOUNDEX,
            C.ISGROUP,
            C.ISORGANIZATION
        from
            [CTE_CONSTITUENT_WITH_EXACT_MATCH] C
            inner join dbo.ALIAS A on C.ID = A.ID
        where    
            @CONFIG_INCLUDEALIAS = 1
        union all
        select
            ID,
            NICKNAME,
            MIDDLENAME,
            KEYNAME,
            MAIDENNAME,
            GENDERCODE,
            KEYNAMESOUNDEX,
            ISGROUP,
            ISORGANIZATION
        from
            [CTE_CONSTITUENT_WITH_EXACT_MATCH]
        where 
            @CONFIG_INCLUDENICKNAME = 1
        )

        insert into @DUPLICATECANDIDATES (
            CONSTITUENTID,
            ADDRESSID,
            MATCHPERCENTAGE
        )

        select
            C.ID,
            A.ID,
            cast((FCC.OVERALLSCORE * 100) as numeric(5, 2))
        from
            [CTE_CONSTITUENT_WITH_ALIAS_AND_NICKNAMES] C
            inner join dbo.ADDRESS A on C.ID = A.CONSTITUENTID
            left join dbo.PHONE P on C.ID = P.CONSTITUENTID
            left join dbo.EMAILADDRESS E on C.ID = E.CONSTITUENTID
            cross apply 
                dbo.UFN_FUZZYCONSTITUENTCOMPARER_COMPARECONSTITUENTS_2
                (coalesce(@FIRSTNAME, ''),
                C.FIRSTNAME,
                0,
                coalesce(@MIDDLENAME, ''),
                coalesce(C.MIDDLENAME, ''), 
                0,            
                @KEYNAME, 
                C.KEYNAME,
                0,        
                coalesce(@MAIDENNAME, ''), 
                coalesce(C.MAIDENNAME, ''),
                0,
                coalesce(@GENDERCODE, ''),
                coalesce(C.GENDERCODE, ''),
                1,        
                coalesce(@ADDRESSBLOCK, ''),
                coalesce(A.ADDRESSBLOCK, ''),
                0,            
                coalesce(@CITY, ''), 
                coalesce(A.CITY, ''),
                0,            
                coalesce(cast(@STATEID as nvarchar(36)), ''),
                coalesce(cast(A.STATEID as nvarchar(36)), ''),
                1,        
                coalesce(cast(@COUNTRYID as nvarchar(36)), ''),
                coalesce(cast(A.COUNTRYID as nvarchar(36)), ''),
                1,
                coalesce(@PHONENUMBER, ''),
                coalesce(P.NUMBER, ''), 
                0,            
                coalesce(@EMAILADDRESS, ''), 
                coalesce(E.EMAILADDRESS, ''),
                0,            
                coalesce(@EMPLOYER, ''), 
                '',
                0) FCC
        where
            C.KEYNAMESOUNDEX = @KEYNAMESOUNDEX 
        and  
            C.ISGROUP = 0
        and
            C.ISORGANIZATION = 0
        and
            A.POSTCODE like @LEFTPOSTCODE
        and
            cast( (FCC.OVERALLSCORE * 100) as numeric(5, 2) ) >= @OVERALLMATCHTHRESHOLD;

        return;
    end