Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft SQL Server

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old August 25th, 2011, 11:31 AM
Gandeh Gandeh is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2011
Posts: 1 Gandeh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
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

MatterCode
ClientName
ClientAddress1
ClientPostcode
ClientNINumber
ClientVehicleReg
TPName
TPAddress1
TPPostcode
TPVehicleReg
AccidentDate


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.

Steven

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft SQL Server > Table comparison help needed (Thanks in advance)


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap