UFN_COMPARECONSTITUENTS_3

CREATE function dbo.UFN_COMPARECONSTITUENTS_3 (
  @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
)
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
)
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 @LOGICALRESULTMAPPING dbo.UDT_GENERIC_NVARCHAR100_TINYINT;
  insert into @LOGICALRESULTMAPPING
  select CASENAME, RESULTCODE
  from dbo.CONSTITUENTMATCHLOGICALRESULTMAPPING

  insert into @RESULTS 
  select
    TITLERESULT,
    FIRSTNAMERESULT,
    MIDDLENAMERESULT,
    KEYNAMERESULT,
    SUFFIXRESULT,
    STREETNUMBERRESULT,
    STREETNAMERESULT,
    POSTCODERESULT,
    FIRSTNAMESCORE,
    MIDDLENAMESCORE,
    KEYNAMESCORE,
    STREETNUMBERSCORE,
    STREETNAMESCORE,
    POSTCODESCORE,
    HOUSEHOLDMATCH
  from dbo.UFN_COMPARECONSTITUENTS_4 (
    @TITLECODE1,
    @FIRSTNAME1,
    @MIDDLENAME1,
    @KEYNAME1,
    @SUFFIXCODE1,
    @STREETNUMBER1,
    @STREETNAME1,
    @POSTCODE1,
    @TITLECODE2,
    @FIRSTNAME2,
    @MIDDLENAME2,
    @KEYNAME2,
    @SUFFIXCODE2,
    @STREETNUMBER2,
    @STREETNAME2,
    @POSTCODE2,
    @COUNTRYID,
    @ADDRESSONLY,
    0, --@ISORGANIZATION: Used in keyname score used in 'bail' logic. We aren't bailing, so this won't matter

    @LOGICALRESULTMAPPING,
    0 --@BAILTHRESHOLD: Set so we don't bail in '_4' and maintain '_3's behavior

  )

  return;
end