August 25th, 2011, 11:31 AM
Join Date: Aug 2011
Time spent in forums: 21 m 52 sec
Reputation Power: 0
Table comparison help needed (Thanks in advance)
Hello! Im very new to these forums (and quite new to SQL), and im basically looking for some advice on data comparison between tables.
Ill give you a background then the problem, im not looking for a solution, unless someone is very bored! just if what im looking to do is actually possible, and if anyone has an idea of how id go about it, what sort of time the script/process would take to run.
I am a Developer at a Law Firm in the north west, We run a case management system called Solcase which runs on a progress database, I want to make a conflict search script that will compare a new client against every existing client and defendant on our system to ensure they don’t have a case with us already, its basically a check against fraud.
So essentially ill have 2 tables, the first table (case data) of around 146k rows, and will have 11 columns of data (listed below) and a second table (Check data) with new cases of that day, could be around 100 rows, but with the same columns
I will want to compare multiple items across these columns, so for instance ill want to check the first row in the “Check Data” Table against every row in the first table, and id want to check for instance Clientname, against both Client name, and TP name. I anticipate roughly 18 tests (Listed below), and id like each test to be weighted, and only to return a result of it gets above that threshold. So for instance a match on TPpostcode against TPpostcode wouldn’t bring back a result but that + matching TP names would.
ClientName -> ClientName
ClientAddress1 -> ClientAddress1
ClientPostcode -> ClientPostcode
ClientNINumber -> ClientNINumber
ClientVehicleReg -> ClientVehicleReg
TPName -> TPName
TPAddress1 -> TPAddress1
TPPostcode -> TPPostcode
TPVehicleReg -> TPVehicleReg
AccidentDate -> AccidentDate
ClientName -> TPName
ClientAddress1 -> TPAddress1
ClientPostcode -> TPPostcode
ClientVehicleReg -> TPVehicleReg
TPName -> ClientName
TPAddress1 -> ClientAddress1
TPPostcode -> ClientPostcode
TPVehicleReg -> ClientVehicleReg
Now, I was wondering if anyone has any idea how long this might take, and even if it is possible inside SQl, also how long it make take this script to run, and last but not least, if anyone has any idea of commands/syntax that I may find useful for writing something like this, as Ive said, im new to SQL but have some idea of what im doing, and I have access to SQL servers for both MS and MYSQL.
Thanks in advance.