UFN_ORGANIZATION_GETFUZZYDUPLICATES

            CREATE function dbo.UFN_ORGANIZATION_GETFUZZYDUPLICATES(
                @KEYNAME nvarchar(100),
                @FIRSTNAME nvarchar(50) = '',
                @POSTCODE nvarchar(12),
                @ADDRESSBLOCK nvarchar(150) = ''
            ) 
            returns @DUPLICATECANDIDATES table(
                CONSTITUENTID uniqueidentifier,
                ADDRESSID uniqueidentifier,
                MATCHPERCENTAGE numeric(5, 2)
            ) as
            begin

                -- grab thresholds from settings table or default

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

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

                if @LEFTPOSTCODECOUNT = null
                    set @LEFTPOSTCODECOUNT = 3;

                if @OVERALLMATCHTHRESHOLD = null
                    set @OVERALLMATCHTHRESHOLD = 70;

                -- search fields

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

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

                declare @NAME nvarchar(150);
                set @NAME = coalesce(@FIRSTNAME, '') + ' ' + @KEYNAME;    

                insert into @DUPLICATECANDIDATES (
                    CONSTITUENTID,
                    ADDRESSID,
                    MATCHPERCENTAGE
                )
                select 
                    C.ID,
                    A.ID,
                    cast( (FCC.OVERALLSCORE * 100) as numeric(5, 2) )
                from 
                    dbo.CONSTITUENT C
                inner join 
                    dbo.ADDRESS A on C.ID = A.CONSTITUENTID
                -- original parameters were intended for slightly different data, but the underlying CLR code treats them all in the same manner for the comparison.

                cross apply 
                    dbo.UFN_FUZZYCONSTITUENTCOMPARER_COMPARECONSTITUENTS
                    (@NAME, coalesce(@ADDRESSBLOCK, ''), @POSTCODE, '', C.NAME, coalesce(A.ADDRESSBLOCK, ''), A.POSTCODE,'') FCC
                where
                    C.KEYNAMESOUNDEX = @KEYNAMESOUNDEX 
                and  -- Constituent dupe search only applies for individuals

                    (C.ISGROUP = 1 or C.ISORGANIZATION = 1)
                and
                    A.POSTCODE like @LEFTPOSTCODE
                and
                    cast( (FCC.OVERALLSCORE * 100) as numeric(5, 2) ) >= @OVERALLMATCHTHRESHOLD;

                return;
            end