UFN_CONSTITUENT_AUTOMATCH_BYSEARCHSETTINGSCRITERIA
Returns a table of constituents matching the constituent's raw criteria information.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEARCHSETTINGSID | uniqueidentifier | IN | |
| @MAXROWS | int | IN | |
| @EMAILADDRESS | UDT_EMAILADDRESS | IN | |
| @KEYNAME | nvarchar(100) | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @MAIDENNAME | nvarchar(100) | IN | |
| @MIDDLENAME | nvarchar(50) | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(100) | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @STATEID | uniqueidentifier | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @PHONENUMBER | nvarchar(100) | IN | |
| @BIRTHDATE | date | IN | |
| @GENDERCODE | tinyint | IN | |
| @LOOKUPID | nvarchar(20) | IN | |
| @TITLECODEID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_CONSTITUENT_AUTOMATCH_BYSEARCHSETTINGSCRITERIA(
@SEARCHSETTINGSID uniqueidentifier = null,
@MAXROWS integer = 2,
@EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@KEYNAME nvarchar(100) = '',
@FIRSTNAME nvarchar(50) = '',
@MAIDENNAME nvarchar(100) = '',
@MIDDLENAME nvarchar(50) = '',
@ADDRESSBLOCK nvarchar(150) = '',
@CITY nvarchar(100) = '',
@COUNTRYID uniqueidentifier= null,
@STATEID uniqueidentifier= null,
@POSTCODE nvarchar(12) = '',
@PHONENUMBER nvarchar(100) = '',
@BIRTHDATE date = null,
@GENDERCODE tinyint = null,
@LOOKUPID nvarchar(20) = '',
@TITLECODEID uniqueidentifier = null
)
-- Currently, this only returns a list of individuals (not groups or organizations)
-- If we include these other types, they should probably be optional through the constituent duplicate search settings record
-- Including these types will probably mean some consideration for how to handle first, middle, and maiden name criteria scoring (since these constituent types don't have theses)
returns @CONSTITUENTS table (
[CONSTITUENTID] uniqueidentifier,
[FIRSTNAME] nvarchar(100),
[KEYNAME] nvarchar(100),
[MATCHPERCENTAGESCORE] decimal(20,2)
)
as
begin
--Global configuration parameters
declare
@LEFTPOSTCODECOUNT int,
@OVERALLMATCHTHRESHOLD numeric(5, 2),
@CRITERIACOUNT integer = 0,
@CHECKEMAIL bit = 0,
@CHECKKEYNAME bit = 0,
@CHECKFIRSTNAME bit = 0,
@CHECKMAIDENNAME bit = 0,
@CHECKMIDDLENAME bit = 0,
@CHECKADDRESSBLOCK bit = 0,
@CHECKCITY bit = 0,
@CHECKCOUNTRY bit = 0,
@CHECKSTATE bit = 0,
@CHECKPOSTCODE bit = 0,
@CHECKPHONE bit = 0,
@CHECKBIRTHDATE bit = 0,
@CHECKGENDER bit = 0,
@CHECKLOOKUPID bit = 0,
@CHECKTITLE bit = 0,
@ALLOWINACTIVE bit = 0,
@ALLOWDECEASED bit = 0,
@INCLUDENICKNAME bit = 0,
@INCLUDEALIAS bit = 0,
@CHECKOPTIONALCRITERIA bit = 0,
@NORMALIZEADDRESS bit = 0
-- Get global configuration parameters
select
@LEFTPOSTCODECOUNT = [LEFTPOSTCODECOUNT],
@OVERALLMATCHTHRESHOLD = [OVERALLMATCHTHRESHOLD],
@CHECKEMAIL = [EMAIL],
@CHECKKEYNAME = [LASTNAMEORORG],
@CHECKFIRSTNAME = [FIRSTNAME],
@CHECKMAIDENNAME = [MAIDENNAME],
@CHECKMIDDLENAME = [MIDDLENAME],
@CHECKADDRESSBLOCK = [ADDRESS],
@CHECKCITY = [CITY],
@CHECKCOUNTRY = [COUNTRY],
@CHECKSTATE = [STATE],
@CHECKPOSTCODE = [ZIP],
@CHECKPHONE = [ANYPHONE],
@CHECKBIRTHDATE = [BIRTHDATE],
@CHECKGENDER = [GENDER],
@CHECKLOOKUPID = [LOOKUP_ID],
@CHECKTITLE = [TITLE],
@ALLOWINACTIVE = [INACTIVE],
@ALLOWDECEASED = [DECEASED],
@INCLUDENICKNAME = [INCLUDENICKNAME],
@INCLUDEALIAS = [INCLUDEALIAS],
@CHECKOPTIONALCRITERIA = [CHECKOPTIONALCRITERIA],
@NORMALIZEADDRESS = [NORMALIZEADDRESS]
from dbo.[CONSTITUENTDUPLICATESEARCHSETTINGS] with (nolock)
where [ID] = @SEARCHSETTINGSID;
--If we are checking optionally provided data, let's set variables now to clean up querying below
declare
@OPTIONALLYCHECKEMAIL bit = 0,
@OPTIONALLYCHECKKEYNAME bit = 0,
@OPTIONALLYCHECKFIRSTNAME bit = 0,
@OPTIONALLYCHECKMAIDENNAME bit = 0,
@OPTIONALLYCHECKMIDDLENAME bit = 0,
@OPTIONALLYCHECKADDRESSBLOCK bit = 0,
@OPTIONALLYCHECKCITY bit = 0,
@OPTIONALLYCHECKCOUNTRY bit = 0,
@OPTIONALLYCHECKSTATE bit = 0,
@OPTIONALLYCHECKPOSTCODE bit = 0,
@OPTIONALLYCHECKPHONE bit = 0,
@OPTIONALLYCHECKBIRTHDATE bit = 0,
@OPTIONALLYCHECKGENDER bit = 0,
@OPTIONALLYCHECKLOOKUPID bit = 0,
@OPTIONALLYCHECKTITLE bit = 0
if @CHECKOPTIONALCRITERIA = 1
begin
select
@OPTIONALLYCHECKEMAIL = case when @CHECKEMAIL = 0 and len(@EMAILADDRESS) > 0 then 1 else 0 end,
@OPTIONALLYCHECKKEYNAME = case when @CHECKKEYNAME = 0 and len(@KEYNAME) > 0 then 1 else 0 end,
@OPTIONALLYCHECKFIRSTNAME = case when @CHECKFIRSTNAME = 0 and len(@FIRSTNAME) > 0 then 1 else 0 end,
@OPTIONALLYCHECKMAIDENNAME = case when @CHECKMAIDENNAME = 0 and len(@MAIDENNAME) > 0 then 1 else 0 end,
@OPTIONALLYCHECKMIDDLENAME = case when @CHECKMIDDLENAME = 0 and len(@MIDDLENAME) > 0 then 1 else 0 end,
@OPTIONALLYCHECKADDRESSBLOCK = case when @CHECKADDRESSBLOCK = 0 and len(@ADDRESSBLOCK) > 0 then 1 else 0 end,
@OPTIONALLYCHECKCITY = case when @CHECKCITY = 0 and len(@CITY) > 0 then 1 else 0 end,
@OPTIONALLYCHECKCOUNTRY = case when @CHECKCOUNTRY = 0 and @COUNTRYID is not null and @COUNTRYID <> '00000000-0000-0000-0000-000000000000' then 1 else 0 end,
@OPTIONALLYCHECKSTATE = case when @CHECKSTATE = 0and @STATEID is not null and @STATEID <> '00000000-0000-0000-0000-000000000000' then 1 else 0 end,
@OPTIONALLYCHECKPOSTCODE = case when @CHECKPOSTCODE = 0 and len(@POSTCODE) > 0 then 1 else 0 end,
@OPTIONALLYCHECKPHONE = case when @CHECKPHONE = 0 and len(@PHONENUMBER) > 0 then 1 else 0 end,
@OPTIONALLYCHECKBIRTHDATE = case when @CHECKBIRTHDATE = 0 and @BIRTHDATE is not null then 1 else 0 end,
@OPTIONALLYCHECKGENDER = case when @CHECKGENDER = 0 and @GENDERCODE is not null and @GENDERCODE <> 0 then 1 else 0 end,
@OPTIONALLYCHECKLOOKUPID = case when @CHECKLOOKUPID = 0 and len(@LOOKUPID) > 0 then 1 else 0 end,
@OPTIONALLYCHECKTITLE = case when @CHECKTITLE = 0 and @TITLECODEID is not null and @TITLECODEID <> '00000000-0000-0000-0000-000000000000' then 1 else 0 end
end
if len(@POSTCODE) > 0
select @POSTCODE = left(@POSTCODE, @LEFTPOSTCODECOUNT)+'%';
select @PHONENUMBER = dbo.UFN_PHONE_REMOVEFORMATTING(@PHONENUMBER)
declare @ADDRESSSUBSTITUTIONS dbo.UDT_STRINGPAIR_100
if (@CHECKADDRESSBLOCK = 1 or @OPTIONALLYCHECKADDRESSBLOCK = 1) and @NORMALIZEADDRESS = 1
begin
insert into @ADDRESSSUBSTITUTIONS
select
[DESIGNATION],
[ABBREVIATION]
from dbo.UFN_ADDRESS_ADDRESSBLOCK_STANDARDUSABBREVIATIONS_LOWERCASE()
end
--Scrub for case sensitivity ("Robert" and "robert" should be an exact match)
select
@EMAILADDRESS = lower(@EMAILADDRESS),
@KEYNAME = lower(@KEYNAME),
@FIRSTNAME = lower(@FIRSTNAME),
@MAIDENNAME = lower(@MAIDENNAME),
@MIDDLENAME = lower(@MIDDLENAME),
@ADDRESSBLOCK = case
when (@CHECKADDRESSBLOCK = 1 or @OPTIONALLYCHECKADDRESSBLOCK = 1) and @NORMALIZEADDRESS = 1
then dbo.UFN_ADDRESS_ADDRESSBLOCK_NORMALIZE_LOWERCASE(@ADDRESSBLOCK, @ADDRESSSUBSTITUTIONS)
else lower(@ADDRESSBLOCK)
end,
@CITY = lower(@CITY);
--Calculate criteria count
set @CRITERIACOUNT = (
cast(@CHECKKEYNAME as tinyint) +
cast(@CHECKFIRSTNAME as tinyint) +
cast(@CHECKMAIDENNAME as tinyint) +
cast(@CHECKMIDDLENAME as tinyint) +
cast(@CHECKADDRESSBLOCK as tinyint) +
cast(@CHECKCITY as tinyint) +
cast(@CHECKLOOKUPID as tinyint)
);
if @CRITERIACOUNT=0
return;
declare @CONSTITUENT table (
[ID] uniqueidentifier,
[KEYNAME] nvarchar(100),
[FIRSTNAME] nvarchar(50),
[NICKNAME] nvarchar(50),
[MAIDENNAME] nvarchar(100),
[MIDDLENAME] nvarchar(50),
[TITLECODEID] uniqueidentifier,
[TITLE2CODEID] uniqueidentifier,
[LOOKUPID] varchar(20),
[ADDRESSBLOCK] nvarchar(150),
[CITY] nvarchar(50)
);
with [CTE_CONSTITUENT] as (
select
[CONSTITUENT].[ID],
[CONSTITUENT].[KEYNAME],
[CONSTITUENT].[FIRSTNAME],
[CONSTITUENT].[NICKNAME],
[CONSTITUENT].[MAIDENNAME],
[CONSTITUENT].[MIDDLENAME],
[CONSTITUENT].[TITLECODEID],
[CONSTITUENT].[TITLE2CODEID],
[CONSTITUENT].[LOOKUPID]
from dbo.[CONSTITUENT] with (nolock)
left join [dbo].[DECEASEDCONSTITUENT] with (nolock)
on [CONSTITUENT].[ID] = [DECEASEDCONSTITUENT].[ID]
cross apply (
select case
when @CHECKBIRTHDATE = 1 or @OPTIONALLYCHECKBIRTHDATE = 1
then convert(date, dbo.UFN_DATE_FROMFUZZYDATE([CONSTITUENT].[BIRTHDATE]))
end as [DATE]
) as [BIRTH]
where
([CONSTITUENT].[ISINACTIVE] = 0 or @ALLOWINACTIVE = 1) and
([DECEASEDCONSTITUENT].[ID] is null or @ALLOWDECEASED = 1) and
([CONSTITUENT].[ISGROUP] = 0 and [CONSTITUENT].[ISORGANIZATION] = 0) and
(
(@CHECKLOOKUPID = 0 and @OPTIONALLYCHECKLOOKUPID = 0) or
@LOOKUPID = [CONSTITUENT].[LOOKUPID]
) and
(
(@CHECKBIRTHDATE = 0 and @OPTIONALLYCHECKBIRTHDATE = 0) or
(@OPTIONALLYCHECKBIRTHDATE = 1 and [BIRTH].[DATE] is null) or
[BIRTH].[DATE] = @BIRTHDATE
) and
(
--If we're doing an exact match, we can avoid doing the fuzzy string comparisons
@OVERALLMATCHTHRESHOLD <> 100 or
(
(
(@CHECKMAIDENNAME = 0 and @OPTIONALLYCHECKMAIDENNAME = 0) or
(@OPTIONALLYCHECKMAIDENNAME = 1 and [CONSTITUENT].[MAIDENNAME] = '') or
@MAIDENNAME = lower([CONSTITUENT].[MAIDENNAME])
) and
(
(@CHECKMIDDLENAME = 0 and @OPTIONALLYCHECKMIDDLENAME = 0) or
(@OPTIONALLYCHECKMIDDLENAME = 1 and [CONSTITUENT].[MIDDLENAME] = '') or
@MIDDLENAME = lower([CONSTITUENT].[MIDDLENAME])
)
)
)
)
insert into @CONSTITUENT
--Limit potential constituents as much as possible before fuzzy comparison
select
[CONSTITUENT].[ID],
[CONSTITUENT].[KEYNAME],
[CONSTITUENT].[FIRSTNAME],
[CONSTITUENT].[NICKNAME],
[CONSTITUENT].[MAIDENNAME],
[CONSTITUENT].[MIDDLENAME],
[CONSTITUENT].[TITLECODEID],
[CONSTITUENT].[TITLE2CODEID],
[CONSTITUENT].[LOOKUPID],
case
when (@CHECKADDRESSBLOCK = 1 or @OPTIONALLYCHECKADDRESSBLOCK = 1) and @NORMALIZEADDRESS = 1
then dbo.UFN_ADDRESS_ADDRESSBLOCK_NORMALIZE_LOWERCASE([ADDRESS].[ADDRESSBLOCK], @ADDRESSSUBSTITUTIONS)
else lower([ADDRESS].[ADDRESSBLOCK])
end as [ADDRESSBLOCK],
[ADDRESS].[CITY]
from [CTE_CONSTITUENT] as [CONSTITUENT]
left join dbo.[ADDRESS] with (nolock)
on [CONSTITUENT].[ID] = [ADDRESS].[CONSTITUENTID]
left join dbo.[EMAILADDRESS] with (nolock)
on [CONSTITUENT].[ID] = [EMAILADDRESS].[CONSTITUENTID]
left join dbo.[PHONE] with (nolock)
on [CONSTITUENT].[ID] = [PHONE].[CONSTITUENTID]
where
(
(@CHECKEMAIL = 0 and @OPTIONALLYCHECKEMAIL = 0) or
(@OPTIONALLYCHECKEMAIL = 1 and ([EMAILADDRESS].[ID] is null or [EMAILADDRESS].[EMAILADDRESS] = '')) or
lower([EMAILADDRESS].[EMAILADDRESS]) = @EMAILADDRESS
) and
(
--See if we should be checking this criterion
(@CHECKSTATE = 0 and @OPTIONALLYCHECKSTATE = 0) or
(@OPTIONALLYCHECKSTATE = 1 and [ADDRESS].[STATEID] is null) or --Don't penalize constituent for not having optional criteria
[ADDRESS].[STATEID] = @STATEID
) and
(
(@CHECKCOUNTRY = 0 and @OPTIONALLYCHECKCOUNTRY = 0) or
(@OPTIONALLYCHECKCOUNTRY = 1 and [ADDRESS].[COUNTRYID] is null) or
[ADDRESS].[COUNTRYID] = @COUNTRYID
) and
(
(@CHECKPOSTCODE = 0 and @OPTIONALLYCHECKPOSTCODE = 0) or
(@OPTIONALLYCHECKPOSTCODE = 1 and ([ADDRESS].[ID] is null or [ADDRESS].[POSTCODE] = '')) or
[ADDRESS].[POSTCODE] like @POSTCODE
) and
(
(@CHECKPHONE = 0 and @OPTIONALLYCHECKPHONE = 0) or
(@OPTIONALLYCHECKPHONE = 1 and [PHONE].[ID] is null) or
@PHONENUMBER = [PHONE].[NUMBERNOFORMAT]
) and
(
--If we're doing an exact match, we can avoid doing the fuzzy string comparisons
@OVERALLMATCHTHRESHOLD <> 100 or
(
(
(@CHECKMAIDENNAME = 0 and @OPTIONALLYCHECKMAIDENNAME = 0) or
(@OPTIONALLYCHECKMAIDENNAME = 1 and [CONSTITUENT].[MAIDENNAME] = '') or
@MAIDENNAME = lower([CONSTITUENT].[MAIDENNAME])
) and
(
(@CHECKMIDDLENAME = 0 and @OPTIONALLYCHECKMIDDLENAME = 0) or
(@OPTIONALLYCHECKMIDDLENAME = 1 and [CONSTITUENT].[MIDDLENAME] = '') or
@MIDDLENAME = lower([CONSTITUENT].[MIDDLENAME])
) and
(
(@CHECKCITY = 0 and @OPTIONALLYCHECKCITY = 0) or
(@OPTIONALLYCHECKCITY = 1 and ([ADDRESS].[ID] is null or [ADDRESS].[CITY] = '')) or
@CITY = lower([ADDRESS].[CITY])
) and
(
(@CHECKLOOKUPID = 0 and @OPTIONALLYCHECKLOOKUPID = 0) or
@LOOKUPID = [CONSTITUENT].[LOOKUPID]
)
)
);
with [CTE_ALIASANDNICKNAME] as (
select
[CONSTITUENT].[ID] as [CONSTITUENTID],
[ALIAS].[KEYNAME] as [KEYNAME],
[CONSTITUENT].[KEYNAME] as [CONSTITUENTKEYNAME],
[ALIAS].[FIRSTNAME] as [FIRSTNAME],
[CONSTITUENT].[FIRSTNAME] as [CONSTITUENTFIRSTNAME],
[CONSTITUENT].[MAIDENNAME],
[ALIAS].[MIDDLENAME],
[ALIAS].[TITLECODEID],
[ALIAS].[TITLE2CODEID],
[CONSTITUENT].[LOOKUPID],
[CONSTITUENT].[ADDRESSBLOCK],
[CONSTITUENT].[CITY]
from @CONSTITUENT as [CONSTITUENT]
inner join dbo.[ALIAS] with (nolock)
on [CONSTITUENT].[ID] = [ALIAS].[CONSTITUENTID]
where @INCLUDEALIAS = 1
union all
select
[CONSTITUENT].[ID] as [CONSTITUENTID],
[CONSTITUENT].[KEYNAME] as [KEYNAME],
[CONSTITUENT].[KEYNAME] as [CONSTITUENTKEYNAME],
[CONSTITUENT].[NICKNAME] as [FIRSTNAME],
[CONSTITUENT].[FIRSTNAME] as [CONSTITUENTFIRSTNAME],
[CONSTITUENT].[MAIDENNAME],
[CONSTITUENT].[MIDDLENAME],
[CONSTITUENT].[TITLECODEID],
[CONSTITUENT].[TITLE2CODEID],
[CONSTITUENT].[LOOKUPID],
[CONSTITUENT].[ADDRESSBLOCK],
[CONSTITUENT].[CITY]
from @CONSTITUENT as [CONSTITUENT]
where @INCLUDENICKNAME = 1
),
[CTE_CONSITUENTS_SCORES] as (
select
[CONSTITUENT].[ID],
[CONSTITUENT].[FIRSTNAME],
[CONSTITUENT].[KEYNAME],
case
when @OVERALLMATCHTHRESHOLD = 100 then 0 --If exact match: We only allow exact matches to pass, so we don't need to calculate this
else (
case when @CHECKKEYNAME = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@KEYNAME, lower([CONSTITUENT].[COMPAREKEYNAME])) else 0 end +
case when @CHECKFIRSTNAME = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@FIRSTNAME, lower([CONSTITUENT].[COMPAREFIRSTNAME])) else 0 end +
case when @CHECKMAIDENNAME = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@MAIDENNAME, lower([CONSTITUENT].[MAIDENNAME])) else 0 end +
case when @CHECKMIDDLENAME = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@MIDDLENAME, lower([CONSTITUENT].[MIDDLENAME])) else 0 end +
case when @CHECKCITY = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@CITY, lower([CONSTITUENT].[CITY])) else 0 end +
case when @CHECKADDRESSBLOCK = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@ADDRESSBLOCK, lower([CONSTITUENT].[ADDRESSBLOCK])) else 0 end +
case when @CHECKLOOKUPID = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@LOOKUPID, [CONSTITUENT].[LOOKUPID]) else 0 end
)
end as [CHECKCRITERIASCORE],
case
when @OVERALLMATCHTHRESHOLD = 100 then 0
else (
case when @OPTIONALLYCHECKKEYNAME = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@KEYNAME, lower([CONSTITUENT].[COMPAREKEYNAME])) else 0 end +
case when @OPTIONALLYCHECKFIRSTNAME = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@FIRSTNAME, lower([CONSTITUENT].[COMPAREFIRSTNAME])) else 0 end +
case when @OPTIONALLYCHECKMAIDENNAME = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@MAIDENNAME, lower([CONSTITUENT].[MAIDENNAME])) else 0 end +
case when @OPTIONALLYCHECKMIDDLENAME = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@MIDDLENAME, lower([CONSTITUENT].[MIDDLENAME])) else 0 end +
case when @OPTIONALLYCHECKCITY = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@CITY, lower([CONSTITUENT].[CITY])) else 0 end +
case when @OPTIONALLYCHECKADDRESSBLOCK = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@ADDRESSBLOCK, lower([CONSTITUENT].[MAIDENNAME])) else 0 end +
case when @OPTIONALLYCHECKLOOKUPID = 1 then [dbo].[UFN_FUZZYSTRINGCOMPARER_COMPARESTRINGS](@LOOKUPID, [CONSTITUENT].[LOOKUPID]) else 0 end
)
end as [OPTIONALLYCHECKCRITERIASCORE],
case
when @OVERALLMATCHTHRESHOLD = 100 then 0
else (
case when @OPTIONALLYCHECKKEYNAME = 1 and len([CONSTITUENT].[KEYNAME]) > 0 then 1 else 0 end +
case when @OPTIONALLYCHECKFIRSTNAME = 1 and len([CONSTITUENT].[FIRSTNAME]) > 0 then 1 else 0 end +
case when @OPTIONALLYCHECKMAIDENNAME = 1 and len([CONSTITUENT].[MAIDENNAME]) > 0 then 1 else 0 end +
case when @OPTIONALLYCHECKMIDDLENAME = 1 and len([CONSTITUENT].[MIDDLENAME]) > 0 then 1 else 0 end +
case when @OPTIONALLYCHECKCITY = 1 and len([CONSTITUENT].[CITY]) > 0 then 1 else 0 end +
case when @OPTIONALLYCHECKADDRESSBLOCK = 1 and len([CONSTITUENT].[ADDRESSBLOCK]) > 0 then 1 else 0 end +
case when @OPTIONALLYCHECKLOOKUPID = 1 and len([CONSTITUENT].[LOOKUPID]) > 0 then 1 else 0 end
)
end as [CONSTITUENTOPTIONALCRITERIACOUNT]
from (
select
[CONSTITUENTS].[ID],
[CONSTITUENTS].[FIRSTNAME],
[CONSTITUENTS].[KEYNAME],
[CONSTITUENTS].[FIRSTNAME] as [COMPAREFIRSTNAME],
[CONSTITUENTS].[KEYNAME] as [COMPAREKEYNAME],
[CONSTITUENTS].[MIDDLENAME],
[CONSTITUENTS].[MAIDENNAME],
[CONSTITUENTS].[TITLECODEID],
[CONSTITUENTS].[TITLE2CODEID],
[CONSTITUENTS].[LOOKUPID],
[CONSTITUENTS].[CITY],
[CONSTITUENTS].[ADDRESSBLOCK]
from @CONSTITUENT as [CONSTITUENTS]
union all
select
[ALIASES].[CONSTITUENTID] as [ID],
[ALIASES].[CONSTITUENTFIRSTNAME] as [FIRSTNAME],
[ALIASES].[CONSTITUENTKEYNAME] as [KEYNAME],
[ALIASES].[FIRSTNAME] as [COMPAREFIRSTNAME],
[ALIASES].[KEYNAME] as [COMPAREKEYNAME],
[ALIASES].[MIDDLENAME],
[ALIASES].[MAIDENNAME],
[ALIASES].[TITLECODEID],
[ALIASES].[TITLE2CODEID],
[ALIASES].[LOOKUPID],
[ALIASES].[CITY],
[ALIASES].[ADDRESSBLOCK]
from [CTE_ALIASANDNICKNAME] as [ALIASES]
) as [CONSTITUENT]
where
(
(@CHECKTITLE = 0 and @OPTIONALLYCHECKTITLE = 0) or
([CONSTITUENT].[TITLECODEID] = @TITLECODEID or [CONSTITUENT].[TITLE2CODEID] = @TITLECODEID) or
([CONSTITUENT].[TITLECODEID] is null and [CONSTITUENT].[TITLE2CODEID] is null and @OPTIONALLYCHECKTITLE = 1)
) and
(
--For exact match; Doing first name filtering here since aliases and nicknames needed to be added first
@OVERALLMATCHTHRESHOLD <> 100 or
(
(
(@CHECKFIRSTNAME = 0 and @OPTIONALLYCHECKFIRSTNAME = 0) or
(@OPTIONALLYCHECKFIRSTNAME = 1 and len([CONSTITUENT].[COMPAREFIRSTNAME]) = 0) or
@FIRSTNAME = lower([CONSTITUENT].[COMPAREFIRSTNAME])
) and
(
(@CHECKKEYNAME = 0 and @OPTIONALLYCHECKKEYNAME = 0) or
(@OPTIONALLYCHECKKEYNAME = 1 and len([CONSTITUENT].[COMPAREKEYNAME]) = 0) or
@KEYNAME = lower([CONSTITUENT].[COMPAREKEYNAME])
) and
(
(@CHECKADDRESSBLOCK = 0 and @OPTIONALLYCHECKADDRESSBLOCK = 0) or
(@OPTIONALLYCHECKADDRESSBLOCK = 1 and ([CONSTITUENT].[ADDRESSBLOCK] is null or [CONSTITUENT].[ADDRESSBLOCK] = '')) or
@ADDRESSBLOCK = [CONSTITUENT].[ADDRESSBLOCK]
)
)
)
),
-- CTE with computed match percentage
[CTE_LOOKUP_RESULT] as (
select
[ID] AS [CONSTITUENTID],
[FIRSTNAME],
[KEYNAME],
case
when @OVERALLMATCHTHRESHOLD = 100 then 100
else (
([CHECKCRITERIASCORE] + [OPTIONALLYCHECKCRITERIASCORE])/
(@CRITERIACOUNT + [CONSTITUENTOPTIONALCRITERIACOUNT])
) * 100
end as [MATCHPERCENTAGESCORE]
from [CTE_CONSITUENTS_SCORES]
)
insert into @CONSTITUENTS
(
[CONSTITUENTID],
[FIRSTNAME],
[KEYNAME],
[MATCHPERCENTAGESCORE]
)
select distinct top(@MAXROWS)
[DISTINCTCONSTITUENTS].[CONSTITUENTID],
[DISTINCTCONSTITUENTS].[FIRSTNAME],
[DISTINCTCONSTITUENTS].[KEYNAME],
[BESTSCORE].[MATCHPERCENTAGESCORE]
from (
select distinct
[CONSTITUENTID],
[FIRSTNAME],
[KEYNAME]
from [CTE_LOOKUP_RESULT]
) as [DISTINCTCONSTITUENTS]
cross apply (
select max([MATCHPERCENTAGESCORE]) as [MATCHPERCENTAGESCORE]
from [CTE_LOOKUP_RESULT]
where [CONSTITUENTID] = [DISTINCTCONSTITUENTS].[CONSTITUENTID]
) as [BESTSCORE]
where [MATCHPERCENTAGESCORE] >= @OVERALLMATCHTHRESHOLD
order by [MATCHPERCENTAGESCORE] desc;
return;
end