UFN_COMPARECONSTITUENTS_4

CREATE function dbo.UFN_COMPARECONSTITUENTS_4 (
  @TITLECODE1 uniqueidentifier,
  @FIRSTNAME1 nvarchar(50),
  @MIDDLENAME1 nvarchar(50),
  @KEYNAME1 nvarchar(100),
  @SUFFIXCODE1 uniqueidentifier,
  @STREETNUMBER1 nvarchar(20),
  @STREETNAME1 nvarchar(150),
  @POSTCODE1 nvarchar(12),
  @TITLECODE2 uniqueidentifier,
  @FIRSTNAME2 nvarchar(50),
  @MIDDLENAME2 nvarchar(50),
  @KEYNAME2 nvarchar(100),
  @SUFFIXCODE2 uniqueidentifier,
  @STREETNUMBER2 nvarchar(20),
  @STREETNAME2 nvarchar(150),
  @POSTCODE2 nvarchar(12),
  @COUNTRYID uniqueidentifier,
  @ADDRESSONLY bit,
  @ISORGANIZATION bit,
  @LOGICALRESULTMAPPING dbo.UDT_GENERIC_NVARCHAR100_TINYINT readonly,
  @BAILTHRESHOLD tinyint
)
returns @RESULTS table (
  TITLERESULT tinyint,
  FIRSTNAMERESULT tinyint,
  MIDDLENAMERESULT tinyint,
  KEYNAMERESULT tinyint,
  SUFFIXRESULT tinyint,
  STREETNUMBERRESULT tinyint,
  STREETNAMERESULT tinyint,
  POSTCODERESULT tinyint,
  -- scores are included for informational purposes, the results will be what is used to determine matches

  FIRSTNAMESCORE tinyint,
  MIDDLENAMESCORE tinyint,
  KEYNAMESCORE tinyint,
  STREETNUMBERSCORE tinyint,
  STREETNAMESCORE tinyint,
  POSTCODESCORE tinyint,
  -- this constituent is not a match, but the two constituents may be a household

  HOUSEHOLDMATCH bit,
  COMPOSITESCORE int,
  BAILEDCOMPARISON bit
)
begin
  -- ******

  -- This function assumes all strings are "clean" per UFN_CLEANCONSTITUENTSTRINGS.

  -- ******


  -- RESULT CODES

  -- ---------------

  -- 0 = Match

  -- 1 = Likely match

  -- 2 = Possible match

  -- 3 = Not a match

  -- 4 = Both blank

  -- 5 = Constituent 1 blank, constituent 2 non-blank

  -- 6 = Constituent 1 non-blank, constituent 1 blank


  declare 
    @TITLERESULT tinyint,
    @FIRSTNAMERESULT tinyint,
    @MIDDLENAMERESULT tinyint,
    @KEYNAMERESULT tinyint,
    @SUFFIXRESULT tinyint,
    @STREETNUMBERRESULT tinyint,
    @STREETNAMERESULT tinyint,
    @POSTCODERESULT tinyint,
    @FIRSTNAMESCORE tinyint,
    @MIDDLENAMESCORE tinyint,
    @KEYNAMESCORE tinyint,
    @STREETNUMBERSCORE tinyint,
    @STREETNAMESCORE tinyint,
    @POSTCODESCORE tinyint,
    @HOUSEHOLDMATCH bit = 0

  -- Address Only

  if @ADDRESSONLY = 1
  begin
    set @TITLERESULT = 0
    set @FIRSTNAMERESULT = 0
    set @MIDDLENAMERESULT = 0
    set @KEYNAMERESULT = 0
    set @SUFFIXRESULT = 0
  end

-----CONSTITUENT SCORING-------------------------------------------------------------------------------------------------------------------------

-- We are maintaining this score as we determine the result of each field--as we go

-- This will allow us to bail from scoring the remaining fields when we determine that the max score for the constituent is lower than the provided threshold

-- This score will also be passed back to the caller in @RESULTS

------------------------------------------------------------------------------------------------------------------------------------------------------------


  declare @SCORE smallint = 100
  insert into @RESULTS values (3, 3, 3, 3, 3, 3, 3, 3, 0, 0, 0, 0, 0, 0, 0, 0, 1) --Worst result possible in case we decide to bail


-----MORE ON BAILING--------------------------------------------------------------------------------------------------------------------------------

-- We want to test as few cases as possible for performance reasons

-- The quicker we can determine that there's no possible way these two constituents can match, the better

-- We'll start by testing simplier cases and get to more expensive comparisons as we go


-- Testing order:

-- 1. In-memory comparisons

-- 2. Comparisons requiring table resources

-- 3. Fuzzy-match comparisons


-- Because nearly every field (e.g. first name, key name, etc...) has scoring logic based in at least 2 of these types,

-- it will be necessary to check more than one 'block' for logic pertaining to that field

-- An attempt has been made to state the number of times a field has appeared in the code an how many more times it will

-- The accuracy of that number depends on how well it is maintained, 

-- but seeing it should at least serve as a reminder that the field appears throughout the function--not in isolation

------------------------------------------------------------------------------------------------------------------------------------------------------------


  -- Not everyone wants to bail. Caller can use a threshold of 0 to maintain pervious versions' behavior.

  declare @CANPERFORMBAIL bit = 0
  if @BAILTHRESHOLD > 0
    set @CANPERFORMBAIL = 1

  -- Depending on how early we bail, we might go ahead and factor in the best-case scenario when we know the provided field data doesn't match

  -- These will be set in their respective field logic 'blocks' as necessary

  declare @MINIMUMNONMATCHKEYNAMEDEDUCTION smallint
  declare @MINIMUMNONMATCHSTREETNUMBERDEDUCTION smallint
  declare @MINIMUMNONMATCHPOSTCODEDEDUCTION smallint

  -- The result for three digits matching on the post code may factor into the the best-case non-match result for post code

  -- Setting in @MINIMUMNONMATCHPOSTCODEDEDUCTION logic. Value maybe needed twice (if we don't bail), so storing globally

  declare @THREEDIGITZIPMATCHRESULT tinyint

  -----------------------------------------------------------------------------

  -- 1. In-memory comparison scoring

  ------------------------------------------------------------------------------

  -- KEYNAME scoring tests 1 of 2

  if @KEYNAMERESULT is null
  begin
    if @KEYNAME1 = '' or @KEYNAME2 = ''
    begin
      if @KEYNAME1 = ''
        if @KEYNAME2 = ''
          set @KEYNAMERESULT = 4
        else
          set @KEYNAMERESULT = 5
      else
        set @KEYNAMERESULT = 6
    end

    else if @KEYNAME1 = @KEYNAME2
      -- exact match

      set @KEYNAMERESULT = 0

    else if @KEYNAME1 like @KEYNAME2+'-%' or @KEYNAME1 like '%-'+@KEYNAME2 or
            @KEYNAME2 like @KEYNAME1+'-%' or @KEYNAME2 like '%-'+@KEYNAME1
      -- maiden name vs. married name

      select @KEYNAMERESULT = [NUMBER]
      from @LOGICALRESULTMAPPING
      where STRING = 'Last name / Hyphenated last name'
  end

  if @KEYNAMERESULT is not null
    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_KEYNAMESCOREDEDUCTION_BYRESULT(@KEYNAMERESULT, @ISORGANIZATION);
  else -- If we don't have a RESULT now, the best possible KEYNAMERESULT is 1, so let's factor that into the score

  begin
    select @MINIMUMNONMATCHKEYNAMEDEDUCTION = DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_KEYNAMESCOREDEDUCTION_BYRESULT(1, @ISORGANIZATION);
    set @SCORE -= @MINIMUMNONMATCHKEYNAMEDEDUCTION;
  end

  -- Key name bail test

  if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
      return;
  ------------------------------------------------------------------------------

  -- STREETNAME scoring tests 1 of 2

  if @STREETNAME1 = '' or @STREETNAME2 = ''
  begin
    if @STREETNAME1 = ''
      if @STREETNAME2 = ''
        set @STREETNAMERESULT = 4
      else
        set @STREETNAMERESULT = 5
    else
      set @STREETNAMERESULT = 6
  end

  else if @STREETNAME1 = @STREETNAME2
    -- exact match

    set @STREETNAMERESULT = 0

  if @STREETNAMERESULT is not null
  begin
    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_STREETNAMESCOREDEDUCTION_BYRESULT(@STREETNAMERESULT);

  -- Street name bail test

    if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
        return;
  end
  ------------------------------------------------------------------------------

  -- STREETNUMBER scoring tests 1 of 2

  if @STREETNUMBER1 = '' or @STREETNUMBER2 = ''
  begin
    if @STREETNUMBER1 = ''
      if @STREETNUMBER2 = ''
        set @STREETNUMBERRESULT = 4
      else
        set @STREETNUMBERRESULT = 5
    else
      set @STREETNUMBERRESULT = 6
  end

  else if @STREETNUMBER1 = @STREETNUMBER2
    -- exact match

    set @STREETNUMBERRESULT = 0

  else if @STREETNUMBER1 like @STREETNUMBER2+'-%' or @STREETNUMBER2 like @STREETNUMBER1+'-%'
    -- street number vs. street number range or street/apartment combination

    select @STREETNUMBERRESULT = [NUMBER]
    from @LOGICALRESULTMAPPING
    where STRING = 'Street number / Hyphenated street number'

  if @STREETNUMBERRESULT is not null
    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_STREETNUMBERSCOREDEDUCTION_BYRESULT(@STREETNUMBERRESULT);
  else -- If we don't have a RESULT now, the best possible STREETNUMBERRESULT is 1, so let's factor that into the score

  begin
    select @MINIMUMNONMATCHSTREETNUMBERDEDUCTION = DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_STREETNUMBERSCOREDEDUCTION_BYRESULT(1);
    set @SCORE -= @MINIMUMNONMATCHSTREETNUMBERDEDUCTION;
  end

  -- Street number bail test

  if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
      return;
  ------------------------------------------------------------------------------

  -- POSTCODE scoring tests 1 of 2

  if @POSTCODE1 = '' or @POSTCODE2 = ''
  begin
    if @POSTCODE1 = ''
      if @POSTCODE2 = ''
        set @POSTCODERESULT = 4
      else
        set @POSTCODERESULT = 5
    else
      set @POSTCODERESULT = 6
  end

  else if @POSTCODE1 = @POSTCODE2
    -- exact match

    set @POSTCODERESULT = 0

  if @POSTCODERESULT is not null
    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_POSTCODESCOREDEDUCTION_BYRESULT(@POSTCODERESULT);
  else -- If we don't have a POSTCODERESULT now, the let's factor in the best-case deduction

  begin
    declare @BESTCASEPOSTCODERESULT tinyint
    select @THREEDIGITZIPMATCHRESULT = [NUMBER]
    from @LOGICALRESULTMAPPING
    where STRING = 'Three digits of ZIP code match, but not first three'

    if isnull(@THREEDIGITZIPMATCHRESULT, 2) > 1 --Unless above is less than 1, 1 is best case

      set @BESTCASEPOSTCODERESULT = 1
    else
      set @BESTCASEPOSTCODERESULT = @THREEDIGITZIPMATCHRESULT

    select @MINIMUMNONMATCHPOSTCODEDEDUCTION = DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_POSTCODESCOREDEDUCTION_BYRESULT(@BESTCASEPOSTCODERESULT);

    set @SCORE -= @MINIMUMNONMATCHPOSTCODEDEDUCTION;
  end

  -- Post code bail test

  if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
      return;
  ------------------------------------------------------------------------------

  -- TITLECODE scoring tests 1 of 2

  if @TITLERESULT is null
  begin
    if @TITLECODE1 is null or @TITLECODE2 is null
    begin
      if @TITLECODE1 is null
        if @TITLECODE2 is null
          set @TITLERESULT = 4
        else
          set @TITLERESULT = 5
      else
        set @TITLERESULT = 6
    end

    else if @TITLECODE1 = @TITLECODE2
      -- exact match

      set @TITLERESULT = 0
  end

  -- Title bail test

  if @TITLERESULT is not null
  begin
    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_TITLESCOREDEDUCTION_BYRESULT(@TITLERESULT);

    if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
        return;
  end
  ------------------------------------------------------------------------------

  -- FIRSTNAME scoring tests 1 of 3

  if @FIRSTNAMERESULT is null
  begin
    if @FIRSTNAME1 = '' or @FIRSTNAME2 = ''
    begin
      if @FIRSTNAME1 = ''
        if @FIRSTNAME2 = ''
          set @FIRSTNAMERESULT = 4
        else
          set @FIRSTNAMERESULT = 5
      else
        set @FIRSTNAMERESULT = 6
    end

    else if @FIRSTNAME1 = @FIRSTNAME2
      -- exact match

      set @FIRSTNAMERESULT = 0

    else if @FIRSTNAME1 = left(@FIRSTNAME2,1) or @FIRSTNAME2 = left(@FIRSTNAME1,1)
      -- first initial vs. first name

      select @FIRSTNAMERESULT = [NUMBER]
      from @LOGICALRESULTMAPPING
      where STRING = 'First name / First initial'

    else if @FIRSTNAME1 like '% AND %' or
            @FIRSTNAME1 like '% & %'
    begin
      if charindex('|'+@FIRSTNAME2+'|',
                    '|'+replace(replace(replace(replace(@FIRSTNAME1,' AND ','|'),' & ','|'),', ','|'),',','|')+'|') > 0
      begin
        -- one name vs. two names (household) = not a constituent match, a household match

        set @FIRSTNAMERESULT = 3
        set @HOUSEHOLDMATCH = 1 --TODO

      end
    end

    else if @FIRSTNAME2 like '% AND %' or
            @FIRSTNAME2 like '% & %'
    begin
      if charindex('|'+@FIRSTNAME1+'|',
                    '|'+replace(replace(replace(replace(@FIRSTNAME2,' AND ','|'),' & ','|'),', ','|'),',','|')+'|') > 0
      begin
        -- one name vs. two names (household) = not a constituent match, a household match

        set @FIRSTNAMERESULT = 3
        set @HOUSEHOLDMATCH = 1 --TODO

      end
    end

    else if @MIDDLENAME1 <> '' or @MIDDLENAME2 <> ''
    begin
      if @FIRSTNAME1 = @FIRSTNAME2 + ' ' + @MIDDLENAME2 or
          @FIRSTNAME2 = @FIRSTNAME1 + ' ' + @MIDDLENAME1
      begin
        -- first/middle name combined vs. separate

        set @FIRSTNAMERESULT = 0

        if @MIDDLENAME1 <> '' and @MIDDLENAME2 <> ''
          -- fn1=John, mn1=Roy, fn2=John Roy, mn2=Bob

          set @MIDDLENAMERESULT = 4
        else
          -- fn1=John, mn1=Roy, fn2=John Roy

          set @MIDDLENAMERESULT = 0
      end

      else if @FIRSTNAME1 = @FIRSTNAME2 + ' ' + left(@MIDDLENAME2,1) or
              @FIRSTNAME2 = @FIRSTNAME1 + ' ' + left(@MIDDLENAME1,1) or
              @FIRSTNAME1 = left(@MIDDLENAME2,1) + ' ' + @FIRSTNAME2 or
              @FIRSTNAME2 = left(@MIDDLENAME1,1) + ' ' + @FIRSTNAME1
      begin
        -- middle initial included in first name = match

        select @FIRSTNAMERESULT = [NUMBER]
        from @LOGICALRESULTMAPPING
        where STRING = 'First name / First name & middle initial'

        if @MIDDLENAME1 <> '' and @MIDDLENAME2 <> ''
          -- fn1=John, mn1=Roy, fn2=John R, mn2=Bob

          set @MIDDLENAMERESULT = 4
        else
          -- fn1=John, mn1=Roy, fn2=John R

          select @MIDDLENAMERESULT = [NUMBER]
          from @LOGICALRESULTMAPPING
          where STRING = 'First name / First initial'
      end
    end

    else if @FIRSTNAME1 like @FIRSTNAME2 + ' _' or
            @FIRSTNAME2 like @FIRSTNAME1 + ' _' or
            @FIRSTNAME1 like '_ ' + @FIRSTNAME2 or
            @FIRSTNAME2 like '_ ' + @FIRSTNAME1
      -- middle initial included in first name = match

      select @FIRSTNAMERESULT = [NUMBER]
      from @LOGICALRESULTMAPPING
      where STRING = 'First name / First name & middle initial'
  end

  --Not putting in logic to bail now since it's possible for the middle name score to credit points lost by first name

  --Otherwise, we might bail in error

  ------------------------------------------------------------------------------

  -- MIDDLENAME scoring tests 1 of 2

  -- use first name logical results and score mappings

  if @MIDDLENAMERESULT is null
  begin
    if @MIDDLENAME1 = '' or @MIDDLENAME2 = ''
    begin
      if @MIDDLENAME1 = ''
        if @MIDDLENAME2 = ''
          set @MIDDLENAMERESULT = 4
        else
          set @MIDDLENAMERESULT = 5
      else
        set @MIDDLENAMERESULT = 6
    end

    else if @MIDDLENAME1 = @MIDDLENAME2
      -- exact match

      set @MIDDLENAMERESULT = 0

    else if @MIDDLENAME1 = left(@MIDDLENAME2,1) or @MIDDLENAME2 = left(@MIDDLENAME1,1)
      -- middle initial vs. middle name

      select @MIDDLENAMERESULT = [NUMBER]
      from @LOGICALRESULTMAPPING
      where STRING = 'First name / First initial'
  end

    -- Middle name bail test

  if @MIDDLENAMERESULT is not null and @FIRSTNAMERESULT is not null
  begin
    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_FIRSTNAMESCOREDEDUCTION_BYRESULT(@FIRSTNAMERESULT);

    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_MIDDLENAMESCOREDEDUCTION_BYRESULT(@MIDDLENAMERESULT, @FIRSTNAMERESULT);

    if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
        return;
  end

  ------------------------------------------------------------------------------

  -- 2. Scoring tests that need table resources

  ------------------------------------------------------------------------------

  -- FIRSTNAME scoring tests 2 of 3

  if @FIRSTNAMERESULT is null
  begin
    declare @MATCHTYPECODE tinyint

    select @MATCHTYPECODE = MATCHTYPECODE
    from dbo.FIRSTNAMEMATCH
    where FIRSTNAME1 = @FIRSTNAME1
    and FIRSTNAME2 = @FIRSTNAME2

    if @MATCHTYPECODE = 0
      -- nickname

      select @FIRSTNAMERESULT = [NUMBER]
      from @LOGICALRESULTMAPPING
      where STRING = 'Nicknames'

    else if @MATCHTYPECODE = 1
      -- different spellings of same name

      select @FIRSTNAMERESULT = [NUMBER]
      from @LOGICALRESULTMAPPING
      where STRING = 'First name spelling variation'

    else if @MATCHTYPECODE = 2
      -- two different common names, may still be a typo/match

      select @FIRSTNAMERESULT = [NUMBER]
      from @LOGICALRESULTMAPPING
      where STRING = 'Known different first names'

    -- First name bail test

    if @MIDDLENAMERESULT is not null and @FIRSTNAMERESULT is not null
    begin
      select @SCORE -= DEDUCTION
      from dbo.UFN_CONSTITUENTMATCH_FIRSTNAMESCOREDEDUCTION_BYRESULT(@FIRSTNAMERESULT);

      select @SCORE -= DEDUCTION
      from dbo.UFN_CONSTITUENTMATCH_MIDDLENAMESCOREDEDUCTION_BYRESULT(@MIDDLENAMERESULT, @FIRSTNAMERESULT);

      if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
          return;
    end
  end
  ------------------------------------------------------------------------------

  -- TITLECODE scoring tests 2 of 2

  if @TITLERESULT is null
  begin
    select @TITLERESULT = [NUMBER]
    from @LOGICALRESULTMAPPING
    left outer join dbo.TITLECODECONFIGURATION c1 on c1.ID = @TITLECODE1
    left outer join dbo.TITLECODECONFIGURATION c2 on c2.ID = @TITLECODE2
    where STRING = case 
        when c1.GENDERCODE = c2.GENDERCODE and c1.GENDERCODE > 0 then 'Title: Same gender'
        when isnull(c1.GENDERCODE,0) = 0 or isnull(c2.GENDERCODE,0) = 0 then 'Title: Gender unknown'
        else 'Title: Different genders'
      end

    -- Title bail test

    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_TITLESCOREDEDUCTION_BYRESULT(@TITLERESULT);

    if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
        return;
  end
  ------------------------------------------------------------------------------

  -- SUFFIXCODE scoring tests 1 of 1

  if @SUFFIXRESULT is null
  begin
    if @SUFFIXCODE1 is null and @SUFFIXCODE2 is null -- some single blank cases will be given different scores

      set @SUFFIXRESULT = 4

    else if @SUFFIXCODE1 = @SUFFIXCODE2
      -- exact match

      set @SUFFIXRESULT = 0

    else
    begin
      with TCLEANSUFFIX as (
        select ID, replace(replace(replace(DESCRIPTION,',',''),'.',''),' ','') CLEANSUFFIX
        from dbo.SUFFIXCODE
        where ID in (@SUFFIXCODE1,@SUFFIXCODE2)
        union all
        select '00000000-0000-0000-0000-000000000000', ''
        where @SUFFIXCODE1 is null or @SUFFIXCODE2 is null
      ),
      TSUFFIXES as (
        select s1.CLEANSUFFIX SUFFIX1,
               s2.CLEANSUFFIX SUFFIX2
        from TCLEANSUFFIX s1, TCLEANSUFFIX s2
        where s1.CLEANSUFFIX < s2.CLEANSUFFIX  -- so we don't have to handle both permutations of each pair

      )
      select @SUFFIXRESULT = [NUMBER]
      from TSUFFIXES, @LOGICALRESULTMAPPING 
      where STRING = 'Suffix: ' + case when SUFFIX1 = '' then 'Blank' else SUFFIX1 end + '|' + SUFFIX2;

      if @SUFFIXRESULT is null
      begin
        if @SUFFIXCODE1 is null
          set @SUFFIXRESULT = 5
        else if @SUFFIXCODE2 is null
          set @SUFFIXRESULT = 6
     else
        begin
          -- Both suffixes non-blank, use the "all others" result

          select @SUFFIXRESULT = [NUMBER]
          from @LOGICALRESULTMAPPING
          where STRING = 'Suffix: All others'
        end
      end
    end
  end

  select @SCORE -= DEDUCTION
  from dbo.UFN_CONSTITUENTMATCH_SUFFIXSCOREDEDUCTION_BYRESULT(@SUFFIXRESULT);

  --Suffix bail test

  if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
      return;

  ------------------------------------------------------------------------------

  -- 3. Last resort: Fuzzy matching scoring.

  ------------------------------------------------------------------------------

  -- KEYNAME scoring tests 2 of 2

  if @KEYNAMERESULT is null
  begin
    -- fuzzy score

    set @KEYNAMESCORE = dbo.UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS(@KEYNAME1,@KEYNAME2)*100
    set @KEYNAMERESULT = dbo.UFN_CONSTITUENTMATCHSCORERESULT(1,@KEYNAMESCORE)

    set @SCORE += @MINIMUMNONMATCHKEYNAMEDEDUCTION; -- We factored in the best-case scenario into the score earlier.

    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_KEYNAMESCOREDEDUCTION_BYRESULT(@KEYNAMERESULT, @ISORGANIZATION)

    if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
        return;
  end
  ------------------------------------------------------------------------------

  -- FIRSTNAME scoring 3 of 3

  if @FIRSTNAMERESULT is null
  begin
    -- fuzzy score

    set @FIRSTNAMESCORE = dbo.UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS(@FIRSTNAME1,@FIRSTNAME2)*100
    set @FIRSTNAMERESULT = dbo.UFN_CONSTITUENTMATCHSCORERESULT(0,@FIRSTNAMESCORE)

    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_FIRSTNAMESCOREDEDUCTION_BYRESULT(@FIRSTNAMERESULT);

    if @MIDDLENAMERESULT is not null 
    begin
      select @SCORE -= DEDUCTION
      from dbo.UFN_CONSTITUENTMATCH_MIDDLENAMESCOREDEDUCTION_BYRESULT(@MIDDLENAMERESULT, @FIRSTNAMERESULT);

      if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
          return;
    end
  end
  ------------------------------------------------------------------------------

  -- MIDDLENAME scoring 2 of 2

  if @MIDDLENAMERESULT is null
  begin
    -- fuzzy score

    set @MIDDLENAMESCORE = dbo.UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS(@MIDDLENAME1,@MIDDLENAME2)*100
    set @MIDDLENAMERESULT = dbo.UFN_CONSTITUENTMATCHSCORERESULT(0,@MIDDLENAMESCORE)

    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_MIDDLENAMESCOREDEDUCTION_BYRESULT(@MIDDLENAMERESULT, @FIRSTNAMERESULT);

    if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
        return;
  end
  ------------------------------------------------------------------------------

  -- STREETNUMBER scoring tests 2 of 2

  if @STREETNUMBERRESULT is null
  begin
    -- fuzzy score

    set @STREETNUMBERSCORE = dbo.UFN_FUZZYSTRINGCOMPARER_EDITDISTANCESCORE(@STREETNUMBER1,@STREETNUMBER2)*100
    set @STREETNUMBERRESULT = dbo.UFN_CONSTITUENTMATCHSCORERESULT(2,@STREETNUMBERSCORE)

    set @SCORE += @MINIMUMNONMATCHSTREETNUMBERDEDUCTION; -- We factored in the best-case scenario into the score earlier.

    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_STREETNUMBERSCOREDEDUCTION_BYRESULT(@STREETNUMBERRESULT);

    if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
        return;
  end
  ------------------------------------------------------------------------------

  -- POSTCODE scoring tests 2 of 2

  if @POSTCODERESULT is null
  begin
    -- fuzzy score

    set @POSTCODESCORE = dbo.UFN_FUZZYSTRINGCOMPARER_EDITDISTANCESCORE(@POSTCODE1,@POSTCODE2)*100
    set @POSTCODERESULT = dbo.UFN_CONSTITUENTMATCHSCORERESULT(4,@POSTCODESCORE)

    if @POSTCODESCORE = 60 and left(@POSTCODE1,3) <> left(@POSTCODE2,3)
      -- first three digits of ZIP code not the same

      set @POSTCODERESULT = @THREEDIGITZIPMATCHRESULT

    set @SCORE += @MINIMUMNONMATCHPOSTCODEDEDUCTION; --Already factored in the best-case scenario.

    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_POSTCODESCOREDEDUCTION_BYRESULT(@POSTCODERESULT);

    if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
        return;
  end
  ------------------------------------------------------------------------------

-- STREETNAME scoring tests 2 of 2

  if @STREETNAMERESULT is null
  begin
    declare @TRANSLATEDSTREETNAME1 nvarchar(300), @TRANSLATEDSTREETNAME2 nvarchar(300)
    declare @BASESTREETNAME1 nvarchar(300), @BASESTREETNAME2 nvarchar(300)
    declare @STREETTOKEN1 nvarchar(20), @STREETTOKEN2 nvarchar(20)
    declare @DIRECTIONAL1 nvarchar(20), @DIRECTIONAL2 nvarchar(20);

  -- find known words in the street names

  -- @TRANSLATEDSTREETNAMEn will have all words translated to consistent words (Street->St, North->N, Apartment->Apt)

  -- @BASESTREETNAMEn will have street words and directionals removed (N Main St->Main) and apartment words converted to #

  -- The reason for converting to # is so that we can find the apartment section again later if needed.

  -- The removed street words and directionals are captured - if the base names are the same but these other pieces are different,

  -- we will adjust the score accordingly (Main St vs. Main Rd)


    with CTE as (
      select 
        cast(' '+@STREETNAME1+' ' as nvarchar(4000)) TRANSLATEDSTREETNAME,
        cast(' '+@STREETNAME1+' ' as nvarchar(4000)) BASESTREETNAME,
        cast(null as nvarchar(4000)) STREETWORDS,
        cast(null as nvarchar(4000)) DIRECTIONALS,
        0 LEVEL
      union all
      select 
        replace(TRANSLATEDSTREETNAME,TOKEN,TRANSLATION),
        replace(BASESTREETNAME,TOKEN,case when TOKENTYPECODE = 2 then ' #' else ' ' end),
        case when TOKENTYPECODE = 0 then isnull(STREETWORDS,'') + TRANSLATION else STREETWORDS end,
        case when TOKENTYPECODE = 1 then isnull(DIRECTIONALS,'') + TRANSLATION else DIRECTIONALS end,
        LEVEL+1
      from CTE
      inner join dbo.ADDRESSTOKEN m on charindex(TOKEN,BASESTREETNAME) > 0
      where not exists(select 'x' from dbo.ADDRESSTOKEN m2 where m2.TOKEN<m.TOKEN and charindex(m2.TOKEN,BASESTREETNAME) > 0)
      and COUNTRYID = isnull(@COUNTRYID,COUNTRYID)
    )
    select top 1 
      @TRANSLATEDSTREETNAME1 = ltrim(rtrim(TRANSLATEDSTREETNAME)),
      @BASESTREETNAME1 = ltrim(rtrim(BASESTREETNAME)),
      @STREETTOKEN1 = ltrim(rtrim(STREETWORDS)),
      @DIRECTIONAL1 = ltrim(rtrim(DIRECTIONALS))
    from CTE
    order by LEVEL desc;

    -- repeat for street name 2

    with CTE as (
      select 
        cast(' '+@STREETNAME2+' ' as nvarchar(4000)) TRANSLATEDSTREETNAME,
        cast(' '+@STREETNAME2+' ' as nvarchar(4000)) BASESTREETNAME,
        cast(null as nvarchar(4000)) STREETWORDS,
        cast(null as nvarchar(4000)) DIRECTIONALS,
        0 LEVEL
      union all
      select 
        replace(TRANSLATEDSTREETNAME,TOKEN,TRANSLATION),
        replace(BASESTREETNAME,TOKEN,case when TOKENTYPECODE = 2 then ' #' else ' ' end),
        case when TOKENTYPECODE = 0 then isnull(STREETWORDS,'') + TRANSLATION else STREETWORDS end,
        case when TOKENTYPECODE = 1 then isnull(DIRECTIONALS,'') + TRANSLATION else DIRECTIONALS end,
        LEVEL+1
      from CTE
      inner join dbo.ADDRESSTOKEN m on charindex(TOKEN,BASESTREETNAME) > 0
      where not exists(select 'x' from dbo.ADDRESSTOKEN m2 where m2.TOKEN<m.TOKEN and charindex(m2.TOKEN,BASESTREETNAME) > 0)
      and COUNTRYID = isnull(@COUNTRYID,COUNTRYID)
    )
    select top 1 
      @TRANSLATEDSTREETNAME2 = ltrim(rtrim(TRANSLATEDSTREETNAME)),
      @BASESTREETNAME2 = ltrim(rtrim(BASESTREETNAME)),
      @STREETTOKEN2 = ltrim(rtrim(STREETWORDS)),
      @DIRECTIONAL2 = ltrim(rtrim(DIRECTIONALS))
    from CTE
    order by LEVEL desc

    if @TRANSLATEDSTREETNAME1 = @TRANSLATEDSTREETNAME2
      -- exact match after translations

      set @STREETNAMERESULT = 0

    else if @BASESTREETNAME1 = @BASESTREETNAME2
    begin
      -- street names are same after removing street words and directionals

      if @STREETTOKEN1 <> @STREETTOKEN2 or
          @DIRECTIONAL1 <> @DIRECTIONAL2
        -- street words and/or directionals are different

        select @STREETNAMERESULT = [NUMBER]
        from @LOGICALRESULTMAPPING
        where STRING = 'Same base street names, different street words/directionals'

      else
        -- street word and/or directional present on one side but not other, or in different order in the string

        select @STREETNAMERESULT = [NUMBER]
        from @LOGICALRESULTMAPPING
        where STRING = 'Same base street names, missing or reordered street words/directionals'
    end

    else if @BASESTREETNAME1 like 'PO BOX %' and @BASESTREETNAME1 not like 'PO BOX % ' and
            @BASESTREETNAME2 like 'PO BOX %' and @BASESTREETNAME2 not like 'PO BOX % '
    begin
      -- PO BOX

      set @STREETNAMESCORE = dbo.UFN_FUZZYSTRINGCOMPARER_EDITDISTANCESCORE(
                              substring(@BASESTREETNAME1,8,len(@BASESTREETNAME1)-7),
                              substring(@BASESTREETNAME2,8,len(@BASESTREETNAME2)-7))*100

      -- use the streetnumber score results for PO Box numbers

      set @STREETNAMERESULT = dbo.UFN_CONSTITUENTMATCHSCORERESULT(2,@STREETNAMESCORE)
    end

    else if dbo.UFN_REGULAREXPRESSIONMATCH('^[\d]+(?:ST|ND|RD|TH)*$',@BASESTREETNAME1) = 1 and
            dbo.UFN_REGULAREXPRESSIONMATCH('^[\d]+(?:ST|ND|RD|TH)*$',@BASESTREETNAME2) = 1
    begin
      -- numeric street name, use "short string comparison" like street number/post code

      set @BASESTREETNAME1 = replace(replace(replace(replace(@BASESTREETNAME1,'ST',''),'ND',''),'RD',''),'TH','')
      set @BASESTREETNAME2 = replace(replace(replace(replace(@BASESTREETNAME2,'ST',''),'ND',''),'RD',''),'TH','')

      if @BASESTREETNAME1 = @BASESTREETNAME2
      begin
        -- same numeric street name, n vs. nth

        if @STREETTOKEN1 <> @STREETTOKEN2 or
            @DIRECTIONAL1 <> @DIRECTIONAL2
          -- street words and/or directionals are different

          select @STREETNAMERESULT = [NUMBER]
          from @LOGICALRESULTMAPPING
          where STRING = 'Same base street names, different street words/directionals'

        else
          set @STREETNAMERESULT = 0
      end

      else if @STREETTOKEN1 <> @STREETTOKEN2 or
              @DIRECTIONAL1 <> @DIRECTIONAL2
        -- different numbers, and different street words or directionals - not a match

        set @STREETNAMERESULT = 3

      else
      begin
        set @STREETNAMESCORE = dbo.UFN_FUZZYSTRINGCOMPARER_EDITDISTANCESCORE(@BASESTREETNAME1,@BASESTREETNAME2)*100

        -- use the streetnumber score results for numeric street names

        set @STREETNAMERESULT = dbo.UFN_CONSTITUENTMATCHSCORERESULT(2,@STREETNAMESCORE)
      end
    end
    else
    begin
      -- fuzzy score

      set @STREETNAMESCORE = dbo.UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS(@TRANSLATEDSTREETNAME1,@TRANSLATEDSTREETNAME2)*100
      set @STREETNAMERESULT = dbo.UFN_CONSTITUENTMATCHSCORERESULT(3,@STREETNAMESCORE)
    end

    select @SCORE -= DEDUCTION
    from dbo.UFN_CONSTITUENTMATCH_STREETNAMESCOREDEDUCTION_BYRESULT(@STREETNAMERESULT)

    if @CANPERFORMBAIL = 1 and @SCORE < @BAILTHRESHOLD
        return;
  end
  ------------------------------------------------------------------------------


  update @RESULTS 
  set 
    TITLERESULT = @TITLERESULT,
    FIRSTNAMERESULT = @FIRSTNAMERESULT,
    MIDDLENAMERESULT =@MIDDLENAMERESULT,
    KEYNAMERESULT = @KEYNAMERESULT,
    SUFFIXRESULT = @SUFFIXRESULT,
    STREETNUMBERRESULT = @STREETNUMBERRESULT,
    STREETNAMERESULT = @STREETNAMERESULT,
    POSTCODERESULT = @POSTCODERESULT,
    FIRSTNAMESCORE = @FIRSTNAMESCORE,
    MIDDLENAMESCORE = @MIDDLENAMESCORE,
    KEYNAMESCORE = @KEYNAMESCORE,
    STREETNUMBERSCORE = @STREETNUMBERSCORE,
    STREETNAMESCORE = @STREETNAMESCORE,
    POSTCODESCORE = @POSTCODESCORE,
    HOUSEHOLDMATCH = @HOUSEHOLDMATCH,
    COMPOSITESCORE = @SCORE,
    BAILEDCOMPARISON = 0;

  return;
end