USP_CONSTITUENT_FUZZY_LOOKUP
Perform constituent fuzzy lookup using global parameter settings
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @TRANSACTIONTYPE | nvarchar(512) | IN | |
| @EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | |
| @LASTNAME | nvarchar(100) | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @MAIDENNAME | nvarchar(100) | IN | |
| @MIDDLENAME | nvarchar(50) | IN | |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | |
| @ADDRESS_CITY | nvarchar(100) | IN | |
| @ADDRESS_COUNTRYID | uniqueidentifier | IN | |
| @ADDRESS_STATEID | uniqueidentifier | IN | |
| @ADDRESS_POSTCODE | nvarchar(12) | IN | |
| @PHONE_NUMBER | nvarchar(100) | IN | |
| @BIRTHDATE | UDT_FUZZYDATE | IN | |
| @GENDERCODE | tinyint | IN | |
| @LOOKUPID | nvarchar(20) | IN | |
| @INACTIVE | bit | IN | |
| @DECEASED | bit | IN | |
| @MAXROWS | int | IN | |
| @TITLECODEID | uniqueidentifier | IN | |
| @INCLUDENICKNAME | bit | IN | |
| @INCLUDEALIAS | bit | IN | |
| @ISORGANIZATION | bit | IN | |
| @EventID | uniqueidentifier | IN |
Definition
Copy
CREATE PROCEDURE [dbo].[USP_CONSTITUENT_FUZZY_LOOKUP]
(
@TRANSACTIONTYPE nvarchar(512) = 'FAF Registration'
,@EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = ''
,@LASTNAME nvarchar(100) = ''
,@FIRSTNAME nvarchar(50) = ''
,@MAIDENNAME nvarchar(100) = ''
,@MIDDLENAME nvarchar(50) = ''
,@ADDRESS_ADDRESSBLOCK nvarchar(150) = ''
,@ADDRESS_CITY nvarchar(100) = ''
,@ADDRESS_COUNTRYID uniqueidentifier= null
,@ADDRESS_STATEID uniqueidentifier= null
,@ADDRESS_POSTCODE nvarchar(12) = ''
,@PHONE_NUMBER nvarchar(100) = ''
,@BIRTHDATE AS dbo.UDT_FUZZYDATE = ''
,@GENDERCODE tinyint = null
,@LOOKUPID nvarchar(20) = ''
,@INACTIVE bit = 0
,@DECEASED bit = 0
,@MAXROWS integer = 100
,@TITLECODEID uniqueidentifier = null
,@INCLUDENICKNAME bit = 0
,@INCLUDEALIAS bit = 0
,@ISORGANIZATION bit = 0 -- Added to filter config parameters for organization
,@EventID uniqueidentifier = null
)
AS
BEGIN
SET NOCOUNT ON;
-- Declare global configuration parameters
DECLARE
@LEFTPOSTCODECOUNT AS int
,@OVERALLMATCHTHRESHOLD AS numeric(5, 2)
,@CRITERIACOUNT AS smallint
,@CONFIG_EMAIL AS bit = 0
,@CONFIG_LASTNAMEORORG AS bit = 0
,@CONFIG_FIRSTNAME AS bit = 0
,@CONFIG_MAIDENNAME AS bit = 0
,@CONFIG_MIDDLENAME AS bit = 0
,@CONFIG_ADDRESS AS bit = 0
,@CONFIG_CITY AS bit = 0
,@CONFIG_COUNTRY AS bit = 0
,@CONFIG_STATE AS bit = 0
,@CONFIG_ZIP AS bit = 0
,@CONFIG_ANYPHONE AS bit = 0
,@CONFIG_BIRTHDATE AS bit = 0
,@CONFIG_GENDER AS bit = 0
,@CONFIG_LOOKUP_ID AS bit = 0
,@CONFIG_INACTIVE AS bit = 0
,@CONFIG_DECEASED AS bit = 0
,@CONFIG_TITLE AS bit = 0
,@CONFIG_INCLUDENICKNAME AS bit = 0
,@CONFIG_INCLUDEALIAS AS bit = 0
,@TEMPID AS uniqueidentifier = newid();
-- Get global configuration parameters
-- Organization filter was added to exclude non related config parameters
SELECT
@LEFTPOSTCODECOUNT = LEFTPOSTCODECOUNT
,@OVERALLMATCHTHRESHOLD= OVERALLMATCHTHRESHOLD
,@CONFIG_EMAIL = [EMAIL]
,@CONFIG_LASTNAMEORORG = [LASTNAMEORORG]
,@CONFIG_FIRSTNAME = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [FIRSTNAME] END
,@CONFIG_MAIDENNAME = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [MAIDENNAME] END
,@CONFIG_MIDDLENAME = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [MIDDLENAME] END
,@CONFIG_ADDRESS = [ADDRESS]
,@CONFIG_CITY = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [CITY] END
,@CONFIG_COUNTRY = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [COUNTRY] END
,@CONFIG_STATE = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [STATE] END
,@CONFIG_ZIP = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [ZIP] END
,@CONFIG_ANYPHONE = [ANYPHONE]
,@CONFIG_BIRTHDATE = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [BIRTHDATE] END
,@CONFIG_GENDER = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [GENDER] END
,@CONFIG_LOOKUP_ID = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [LOOKUP_ID] END
,@CONFIG_INACTIVE = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [INACTIVE] END
,@CONFIG_DECEASED = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [DECEASED] END
,@CONFIG_TITLE = CASE WHEN @ISORGANIZATION=1 THEN 0 ELSE [TITLE] 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 (NOLOCK)
WHERE [TRANSACTIONTYPE] = @TRANSACTIONTYPE;
IF LEN(@ADDRESS_POSTCODE) >0
SELECT @ADDRESS_POSTCODE = LEFT(@ADDRESS_POSTCODE ,@LEFTPOSTCODECOUNT)+'%';
-- Calculate criteria count, criteria values are either 0 or 1
-- When config is set and the value is passed then 1 otherwise 0
SELECT @CRITERIACOUNT = (
CASE WHEN (@CONFIG_LASTNAMEORORG=1 AND LEN(@LASTNAME)>0) THEN 1 ELSE 0 END
+ CASE WHEN (@CONFIG_FIRSTNAME =1 AND LEN(@FIRSTNAME)>0) THEN 1 ELSE 0 END
+ CASE WHEN (@CONFIG_MAIDENNAME =1 AND LEN(@MAIDENNAME)>0) THEN 1 ELSE 0 END
+ CASE WHEN (@CONFIG_MIDDLENAME =1 AND LEN(@MIDDLENAME)>0) THEN 1 ELSE 0 END
+ CASE WHEN (@CONFIG_ADDRESS =1 AND LEN(@ADDRESS_ADDRESSBLOCK)>0) THEN 1 ELSE 0 END
+ CASE WHEN (@CONFIG_CITY =1 AND LEN(@ADDRESS_CITY)>0) THEN 1 ELSE 0 END
+ CASE WHEN (@CONFIG_ANYPHONE =1 AND LEN(@PHONE_NUMBER)>0) THEN 1 ELSE 0 END
+ CASE WHEN (@CONFIG_BIRTHDATE =1 AND LEN(@BIRTHDATE)>0) THEN 1 ELSE 0 END
+ CASE WHEN (@CONFIG_LOOKUP_ID =1 AND LEN(@LOOKUPID)>0) THEN 1 ELSE 0 END
+ CASE WHEN (@CONFIG_EMAIL =1 AND LEN(@EMAILADDRESS_EMAILADDRESS)>0) THEN 1 ELSE 0 END
);
IF @CRITERIACOUNT=0
RETURN;
DECLARE @AUTOMATCHSITEMODECODE TINYINT;
SELECT @AUTOMATCHSITEMODECODE = AUTOMATCHSITEMODECODE
FROM dbo.EVENTEXTENSION EX (nolock)
WHERE EX.EVENTID = @EventID;
WITH [CTE_ALL_CONSTITUENTS]
AS
(
SELECT DISTINCT
C.ID AS CONSTITUENTID
,A.ID AS ADDRESSID
,CASE WHEN DC.ID IS NULL THEN 0 ELSE 1 END as ISDECEASED
,C.KEYNAME
,C.NAME
,C.FIRSTNAME
,C.MAIDENNAME
,C.MIDDLENAME
,ISNULL(A.COUNTRYID,@TEMPID) AS [COUNTRYID]
,ISNULL(A.STATEID,@TEMPID) AS [STATEID]
,A.[ADDRESSBLOCK]
,A.[CITY]
,ISNULL(A.[POSTCODE],SPACE(0)) AS [POSTCODE] -- Constituent with no address case
,C.[BIRTHDATE]
,C.[GENDERCODE]
,P.[NUMBER] AS [PHONE_NUMBER]
,C.LOOKUPID
,C.ISINACTIVE
,C.NICKNAME
,ISNULL(C.TITLECODEID,@TEMPID) as [TITLECODEID]
,ISNULL(C.TITLE2CODEID,@TEMPID) as [TITLE2CODEID]
,EA.EMAILADDRESS
FROM [dbo].[CONSTITUENT] (NOLOCK) C
LEFT JOIN [dbo].[ADDRESS] (NOLOCK) A
ON A.CONSTITUENTID = C.ID
LEFT JOIN [dbo].[COUNTRY] (NOLOCK) CO
ON A.COUNTRYID = CO.ID
LEFT JOIN [dbo].[STATE] (NOLOCK) S
ON A.STATEID = S.ID
LEFT JOIN [dbo].[PHONE] (NOLOCK) P
ON C.ID = P.CONSTITUENTID
LEFT JOIN [dbo].[DECEASEDCONSTITUENT] (NOLOCK) DC
ON C.ID = DC.ID
LEFT JOIN [dbo].[EMAILADDRESS] EA (NOLOCK)
ON C.ID = EA.CONSTITUENTID
WHERE C.ISCONSTITUENT = 1 -- Constituents only
AND C.ISGROUP = 0 -- Exclude groups
AND C.ISORGANIZATION = 0 -- Exclude organization
-- Filter constituents by event auto-match site
AND (
(@AUTOMATCHSITEMODECODE = 0)
or
(
(@AUTOMATCHSITEMODECODE = 1 and exists
(select 1 from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(C.ID) CONTSITE
where CONTSITE.SITEID is null)
)
or
(@AUTOMATCHSITEMODECODE = 2 and exists
(select 1 from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(C.ID) CONTSITE
where CONTSITE.SITEID in (
select EVENTAUTOMATCHSITE.SITEID
from dbo.[EVENT]
left outer join dbo.EVENTAUTOMATCHSITE on EVENTAUTOMATCHSITE.EVENTID = [EVENT].ID
where [EVENT].ID = @EVENTID) )
)
)
)
)
-- Filter constituents by email or last name
,[CTE_CONSTITUENTS]
AS
(
select *
from [CTE_ALL_CONSTITUENTS]
where KEYNAME = @LASTNAME
AND ISNULL(@LASTNAME, '') <> ''
union
select *
from [CTE_ALL_CONSTITUENTS]
where EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS
and isnull(@EMAILADDRESS_EMAILADDRESS, '') <> ''
)
-- Constituents with exact matched emails
,[CTE_CONSTITUENTS_WITH_MATCH_EMAILS]
as
(
SELECT
[CONSTITUENTID]
,[FIRSTNAME]
,[KEYNAME] AS [LASTNAME]
,EMAILADDRESS
,100 AS [MATCHPERCENTAGE]
FROM [CTE_CONSTITUENTS]
where EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS
and (@CONFIG_EMAIL=1 AND LEN(@EMAILADDRESS_EMAILADDRESS)>0)
)
,[CTE_CONSTITUENTS_WITH_OTHER_MATCH]
as
(
SELECT
[CONSTITUENTID],
[NAME],
[KEYNAME],
[FIRSTNAME],
[MAIDENNAME],
[CITY],
[PHONE_NUMBER],
[BIRTHDATE],
[LOOKUPID],
[ADDRESSBLOCK],
[POSTCODE],
[COUNTRYID],
[STATEID],
[GENDERCODE],
[TITLECODEID],
[TITLE2CODEID],
[MIDDLENAME],
[ISDECEASED],
[NICKNAME]
FROM [CTE_CONSTITUENTS]
WHERE ISINACTIVE = CASE
WHEN @CONFIG_INACTIVE =1 AND @INACTIVE=1 THEN ISINACTIVE
ELSE 0
END
AND [ISDECEASED] = CASE WHEN (@CONFIG_DECEASED =1 AND @DECEASED=1) THEN [ISDECEASED]
ELSE 0
END
-- Postcode may be null or configured and used for auto match
AND [POSTCODE] LIKE CASE
WHEN LEN([POSTCODE])=0 THEN SPACE(0)
WHEN (@CONFIG_ZIP=1 AND LEN(@ADDRESS_POSTCODE)>0) THEN @ADDRESS_POSTCODE
ELSE [POSTCODE]
END
AND [COUNTRYID] = CASE
WHEN (@CONFIG_COUNTRY=1 AND @ADDRESS_COUNTRYID IS NOT NULL) THEN @ADDRESS_COUNTRYID
ELSE [COUNTRYID]
END
AND [STATEID] = CASE
WHEN (@CONFIG_STATE=1 AND @ADDRESS_STATEID IS NOT NULL) THEN @ADDRESS_STATEID
ELSE [STATEID]
END
AND [GENDERCODE] = CASE
WHEN (@CONFIG_GENDER=1 AND @GENDERCODE IS NOT NULL) THEN @GENDERCODE
ELSE [GENDERCODE]
END
AND (
@CONFIG_TITLE = 0 or
@TITLECODEID is null or
[TITLECODEID] = @TITLECODEID or
[TITLE2CODEID] = @TITLECODEID
)
)
,[CTE_CONSTITUENTS_WITH_OTHER_MATCH_FILTERED]
as (
select
*,
[KEYNAME] as [COMPAREKEYNAME],
[FIRSTNAME] as [COMPAREFIRSTNAME],
[MIDDLENAME] as [COMPAREMIDDLENAME]
from [CTE_CONSTITUENTS_WITH_OTHER_MATCH]
union all
select C.*,
[ALIAS].[KEYNAME] as [COMPAREKEYNAME],
[ALIAS].[FIRSTNAME] as [COMPAREFIRSTNAME],
[ALIAS].[MIDDLENAME] as [COMPAREMIDDLENAME]
from [CTE_CONSTITUENTS_WITH_OTHER_MATCH] C
INNER JOIN dbo.[ALIAS]
on C.[CONSTITUENTID] = [ALIAS].[CONSTITUENTID]
WHERE @CONFIG_INCLUDEALIAS = 1
union all
select *,
[KEYNAME] as [COMPAREKEYNAME],
[NICKNAME] as [COMPAREFIRSTNAME],
[MIDDLENAME] as [COMPAREMIDDLENAME]
from [CTE_CONSTITUENTS_WITH_OTHER_MATCH]
WHERE @CONFIG_INCLUDENICKNAME = 1
)
,[CTE_CONSITUENTS_SCORES]
AS
(
SELECT
*
,[dbo].[UFN_CONSITTUENT_FUZZY_LOOKUP_SCORE](@CONFIG_LASTNAMEORORG ,[COMPAREKEYNAME] ,@LASTNAME) AS [LASTNAME_SCORE]
,[dbo].[UFN_CONSITTUENT_FUZZY_LOOKUP_SCORE](@CONFIG_FIRSTNAME ,[COMPAREFIRSTNAME] ,@FIRSTNAME) AS [FIRSTNAME_SCORE]
,[dbo].[UFN_CONSITTUENT_FUZZY_LOOKUP_SCORE](@CONFIG_MAIDENNAME ,[MAIDENNAME] ,@MAIDENNAME) AS [MAIDENNAME_SCORE]
,[dbo].[UFN_CONSITTUENT_FUZZY_LOOKUP_SCORE](@CONFIG_MIDDLENAME ,[COMPAREMIDDLENAME] ,@MIDDLENAME) AS [MIDDLENAME_SCORE]
,[dbo].[UFN_CONSITTUENT_FUZZY_LOOKUP_SCORE](@CONFIG_CITY ,[CITY] ,@ADDRESS_CITY) AS [CITY_SCORE]
,[dbo].[UFN_CONSITTUENT_FUZZY_LOOKUP_SCORE](@CONFIG_ANYPHONE ,[PHONE_NUMBER] ,@PHONE_NUMBER) AS [PHONE_SCORE]
,[dbo].[UFN_CONSITTUENT_FUZZY_LOOKUP_SCORE](@CONFIG_BIRTHDATE ,[BIRTHDATE] ,@BIRTHDATE) AS [BIRTHDATE_SCORE]
,[dbo].[UFN_CONSITTUENT_FUZZY_LOOKUP_SCORE](@CONFIG_LOOKUP_ID ,[LOOKUPID] ,@LOOKUPID) AS [LOOKUPID_SCORE]
,[dbo].[UFN_CONSITTUENT_FUZZY_LOOKUP_SCORE](@CONFIG_ADDRESS ,[ADDRESSBLOCK] ,@ADDRESS_ADDRESSBLOCK) AS [ADDRESSBLOCK_SCORE]
FROM [CTE_CONSTITUENTS_WITH_OTHER_MATCH_FILTERED]
)
-- CTE with computed match percentage
,[CTE_LOOKUP_RESULT]
AS
(
SELECT
[CONSTITUENTID]
,[NAME]
,[FIRSTNAME]
,[KEYNAME] AS [LASTNAME]
,((([LASTNAME_SCORE]
+ [FIRSTNAME_SCORE]
+ [MAIDENNAME_SCORE]
+ [MIDDLENAME_SCORE]
+ [ADDRESSBLOCK_SCORE]
+ [CITY_SCORE]
+ [PHONE_SCORE]
+ [BIRTHDATE_SCORE]
)/@CRITERIACOUNT)*100) AS MATCHPERCENTAGE
FROM [CTE_CONSITUENTS_SCORES]
)
------ Final resultset
SELECT DISTINCT TOP(@MAXROWS) *
FROM
(
SELECT [CONSTITUENTID]
,[FIRSTNAME]
,[LASTNAME]
,[MATCHPERCENTAGE]
FROM [CTE_CONSTITUENTS_WITH_MATCH_EMAILS]
UNION
SELECT [CONSTITUENTID]
,[FIRSTNAME]
,[LASTNAME]
,[MATCHPERCENTAGE]
FROM [CTE_LOOKUP_RESULT]
WHERE [MATCHPERCENTAGE] >= @OVERALLMATCHTHRESHOLD
) FINALRESULT
ORDER BY [MATCHPERCENTAGE] DESC;
END