UFN_CONSTITUENTMATCHSCORE

CREATE function dbo.UFN_CONSTITUENTMATCHSCORE(
  @IDRESULT tinyint,
  @TITLERESULT tinyint,
  @FIRSTNAMERESULT tinyint,
  @MIDDLENAMERESULT tinyint,
  @KEYNAMERESULT tinyint,
  @SUFFIXRESULT tinyint,
  @STREETNUMBERRESULT tinyint,
  @STREETNAMERESULT tinyint,
  @POSTCODERESULT tinyint,
  @EMAILADDRESSRESULT tinyint,
  @PHONENUMBERRESULT tinyint,
  @ISORGANIZATION bit
)
returns tinyint
with execute as caller
as begin
  -- 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 @RESULT smallint;

  if @IDRESULT = 0
    set @RESULT = 100;

  else
  begin 
    set @RESULT = 100
                  - (select DEDUCTION from dbo.UFN_CONSTITUENTMATCH_TITLESCOREDEDUCTION_BYRESULT(@TITLERESULT))
                  - (select DEDUCTION from dbo.UFN_CONSTITUENTMATCH_FIRSTNAMESCOREDEDUCTION_BYRESULT(@FIRSTNAMERESULT))
                  - (select DEDUCTION from dbo.UFN_CONSTITUENTMATCH_MIDDLENAMESCOREDEDUCTION_BYRESULT(@MIDDLENAMERESULT, @FIRSTNAMERESULT))
                  - (select DEDUCTION from dbo.UFN_CONSTITUENTMATCH_KEYNAMESCOREDEDUCTION_BYRESULT(@KEYNAMERESULT, @ISORGANIZATION))
                  - (select DEDUCTION from dbo.UFN_CONSTITUENTMATCH_SUFFIXSCOREDEDUCTION_BYRESULT(@SUFFIXRESULT))
                  - (select DEDUCTION from dbo.UFN_CONSTITUENTMATCH_STREETNUMBERSCOREDEDUCTION_BYRESULT(@STREETNUMBERRESULT))
                  - (select DEDUCTION from dbo.UFN_CONSTITUENTMATCH_STREETNAMESCOREDEDUCTION_BYRESULT(@STREETNAMERESULT))
                  - (select DEDUCTION from dbo.UFN_CONSTITUENTMATCH_POSTCODESCOREDEDUCTION_BYRESULT(@POSTCODERESULT));

    -- Adjust score for matched email or phone.  Don't let a score below 100 go up to 100.

    -- These are small adjustments relative to the importance of email and phone,

    -- but here we're really just trying to make the score different than when you don't have email or phone.

    -- This will push a small group of people from manual review to auto-match, and from non-match to manual review.

    -- Otherwise it has no functional impact.

    if @EMAILADDRESSRESULT = 0 and @RESULT < 98
      set @RESULT = @RESULT + 2;

    if @PHONENUMBERRESULT = 0 and @RESULT < 98
      set @RESULT = @RESULT + 2;

    -- At least manual review an email or phone match.

    if @RESULT < 70 and (@EMAILADDRESSRESULT = 0 or @PHONENUMBERRESULT = 0)
      set @RESULT = 70;

    else if @RESULT < 0
      set @RESULT = 0;
  end

  return @RESULT;
end