app to match clients in the different databases

Question

Post reply

app to match clients in the different databases

onixsoft

Mr or Mrs. 500

Points: 544
More actions
October 1, 2014 at 7:46 am

#297052

Hello
We have multiple databases with tables with client's data, name/dob/ssn.
We are looking for app which will match all clients in all dbs/tables and build list of ids for each client in each table.
i.e. table1 have following clients:
1 / Alex / 1-1-1960 / 5665
2 / John / 1-2-1960 / 5465
table2 have following clients:
1 / John / 1-2-1960 / 5465
2 / Max / 1-3-1960 / 5454
So result of work of app should be table or csv file with following data:
Name / DOB / SSN / Table1 ID / Table2 ID
Alex / 1-1-1960 / 5665 / 1 / NULL
John / 1-2-1960 / 5465 / 2 / 1
Max / 1-3-1960 / 5454 / NULL / 2
Again, we are looking for 3rd party app which can be easy customize to match different dbs / tables / fields.
Anybody know that type of app?
Much thanks,
Alex.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you are overthinking this if you think you need an app for that.

no "app" is needed for this, as the data entry for any app that tried to do this

is the same effort required for deciding the mapping criteria for as the query

SELECT T1.ID AS Table1ID,

T1.Name AS Table1Name,

T1.DOB AS Table1DOB,

T1.SSN AS Table1SSN,

T2.ID AS Table2ID,

--,T2.Name As Table2Name

--,T2.DOB AS Table2DOB

T2.SSN AS Table2SSN

FROM DatabaseOne.dbo.Table1 T1

INNER JOIN DatabaseTwo.dbo.OtherTable T2

ON T1.Name = T2.Name

AND T1.DOB = T2.DOB

AND T1.SSN = T2.SSN

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

onixsoft Mr or Mrs. 500 Points: 544 More actions · Answer 2

Lowell (10/1/2014)
you are overthinking this if you think you need an app for that.
no "app" is needed for this, as the data entry for any app that tried to do this
is the same effort required for deciding the mapping criteria for as the query

Thanks for SQL query, but it will not work, since name can be spelled with errors, or in different ways, i.e. Alex, Aleks, Alexandr, Alexander.

For Alex vs Aleks we can use SOUNDEX, but not for Alex vs Alexander.

Lowell SSC Guru Points: 323518 More actions · Answer 3

onixsoft (10/1/2014)
Lowell (10/1/2014)
you are overthinking this if you think you need an app for that.
no "app" is needed for this, as the data entry for any app that tried to do this
is the same effort required for deciding the mapping criteria for as the query
Thanks for SQL query, but it will not work, since name can be spelled with errors, or in different ways, i.e. Alex, Aleks, Alexandr, Alexander.
For Alex vs Aleks we can use SOUNDEX, but not for Alex vs Alexander.

don't join on names. try SSN and date of birth, or just SSN, and ignore names.

if you have nay otehr columns that uniquely idnetify a person(email, drivers license number, etc) use that.

don't bother trying to join on names.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

onixsoft Mr or Mrs. 500 Points: 544 More actions · Answer 4

don't join on names. try SSN and date of birth, or just SSN, and ignore names.
if you have nay otehr columns that uniquely idnetify a person(email, drivers license number, etc) use that.
don't bother trying to join on names.

We can't avoid names, source databases are mess, some clients have only names, some have names and dob, ssn.

We need app like Redgate data comparer, which will show which records can be matched and allow us to make final decision are they the same clients or not. So if clients have same name , but very close DOBs, it can be the same client but with wrong DOBs, so app should display results and we will determine if they are the same client and which one have correct dob.