Hi Chris, I have some questions below. Any advice would be much appreciated. In your original post you said:
Try it out. There's always an element of tuning with this type of dedupe. The key things here are:
1. Get some kind of solid matching working as efficiently as possible. In this case, it's postcode and master_id.
The master_id is the unique identifier for each record so I'm not sure what you mean to be honest.
The merge script I'm trying to rebuild contains the following for Pass 1:
•left(a.Surname,5)=left(b.Surname,5)
•left(a.forename,1) = left(B.forename,1)
•a.postcode=b.postcode
•left(A.companynameny,8) = left(B.companyname,8)
•left(A.address1,8) = left(B.address1,8)
Your function and example works great. Maybe I'm just stupid but I just don't see how I can use this to replace the passes system used by the current merge script we have.
I'm not looking for someone to do the dirty work for me so please don't take this the wrong way! I just need some guidance so I know I'm on the right track.
Should I create a new table with match ratios for each field then do the filtering on the combination of ratios?
I'm really confused!
2. You're left with serious numbercrunching over a scan of the reference table and seeks to the target. Do as much work as possible as efficiently as you can. The function is set up as an iTVF so it can parallelise, and you can get it to compare multiple values in the same query.
But from the example select I don't see how I can compare more than one field at a time or am I missing something?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn