Build a fuzzy matching logic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Build a fuzzy matching logic

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-09-08 : 13:57:18

I am trying to create a script that will take care of matching (fuzzy) logic..

I have two tables that I am trying to match each other.

Table A:


 SSN            LastName          FirstName         DOB
111111111    Chai		John		01/01/75
111111112    Richard		Smith	        06/08/71
111111113    David		Rick		08/30/68
111111114    Susan              Becker          05/06/81
111111115    James              Patrick	        12/23/65

Table B:


 SSN            LastName          FirstName         DOB
111111111    Chai		John		01/01/75
111111112    Richard		Smith	        06/08/72
111111113    Dave	        Rick		08/30/68
111111118    Susan              Becker          05/06/81

I am trying to match table A to B

A LEFT JOIN B

and Here is my matching logic:

100% - SSN , Lastname, Firstname, DOB are matching
90% - SSN, Name
80% - SSN, DOB
70% - Name, DOB

SO, the final output should look like:

Table A: 1 = matching 0 = no mathcing


 SSN            LastName   FirstName         DOB   Matching    %
111111111    Chai	 John		01/01/75      1      100%
111111112    Richard	 Smith	        06/08/70      1       90% 
111111113    David	 Rick		08/30/68      1       80%
111111118    Susan       Becker         05/06/81      1       70%
111111115    James       Patrick	12/23/65      0        0%

How can I build this matching process in one script? May be using a cursor??

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-08 : 14:06:59

case when...


Select A.*,
	Matching case
		When A.SSN = B.SSN or (A.LastName = B.LastName and A.FirstName = B.FirstName) or A.DOB = B.DOB then 1
		else 0 end
	Rate = case
		When A.SSN = B.SSN and A.LastName = B.LastName and A.FirstName = B.FirstName and A.DOB = B.DOB then 1
		When A.SSN = B.SSN and A.LastName = B.LastName and A.FirstName = B.FirstName then .9
		When A.SSN = B.SSN and A.DOB = B.DOB then .8
		When A.LastName = B.LastName and A.FirstName = B.FirstName and A.DOB = B.DOB then .7
		else null end
From A
Left Join B
On A.SSN = B.SSN 
or (A.LastName = B.LastName and A.FirstName = B.FirstName)
or A.DOB = B.DOB

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources