Solved

Stored Procedure - Script Help for Duplicate Checks

Posted on 2006-07-10
9
262 Views
Last Modified: 2012-08-13
The previous DBA here had set up the stored procedure to check for duplicates, but the logic wasn't done correctly.  Currently, the script looks for duplicates with same names and different addresses.  

What we are trying to do is have the script to look up same last name and same first X number of characters at beginning of address field.

This is the stored procedure we have now:


CREATE PROCEDURE xp_DupCheck (
      @first_name      nvarchar(64),
      @last_name      nvarchar(64),
      @company      nvarchar(128),
      @city            nvarchar(32),
      @state            nvarchar(32),
      @zipcode      nvarchar(16),
      @user_name      nvarchar(32)
) AS
SELECT tA.id AS Address_id,tA.*,
      tPV.phonenumber AS "VoicePhone", tPT.phonenumber AS "TTYPhone"
FROM tblAddress tA
LEFT JOIN tblPhone tPV ON (tPV.addressid = tA.id AND tPV.typeid = 4)
LEFT JOIN tblPhone tPT ON (tPT.addressid = tA.id AND tPV.typeid = 6)
WHERE      ((first_name = @first_name AND last_name = @last_name AND last_name != '')
            OR (last_name = '' AND company != '' AND  company = @company))
      AND ((city != '' AND city = @city AND state = @state)
            OR (@zipcode != '' AND zipcode = @zipcode))
      AND tA.active=1
ORDER BY last_name, first_name, company, state, city
GO


Can someone please help me with this stored procedure?  Need to be able to query results with same last name and same address field (at least the first couple of characters in address field)

Thanks in advance!
0
Comment
Question by:llputney
  • 5
  • 4
9 Comments
 
LVL 25

Accepted Solution

by:
jrb1 earned 500 total points
ID: 17075643
CREATE PROCEDURE xp_DupCheck2 (
     @first_name     nvarchar(64),
     @address     nvarchar(64),
) AS
SELECT tA.id AS Address_id,tA.*,
     tPV.phonenumber AS "VoicePhone", tPT.phonenumber AS "TTYPhone"
FROM tblAddress tA
LEFT JOIN tblPhone tPV ON (tPV.addressid = tA.id AND tPV.typeid = 4)
LEFT JOIN tblPhone tPT ON (tPT.addressid = tA.id AND tPV.typeid = 6)
WHERE   first_name = @first_name
     AND   left(@address,3) = left(tA.address,3)
     AND   tA.active=1
ORDER BY last_name, first_name, company, state, city
GO
0
 

Author Comment

by:llputney
ID: 17076511
Ok, that works!  However, I think I changed my mind.  I would like to catch both same name, different address and same name, same address.

How can I do both?
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17076582
Just same name then?

CREATE PROCEDURE xp_DupCheck3 (
     @first_name     nvarchar(64),
     @last_name     nvarchar(64),
) AS
SELECT tA.id AS Address_id,tA.*,
     tPV.phonenumber AS "VoicePhone", tPT.phonenumber AS "TTYPhone"
FROM tblAddress tA
LEFT JOIN tblPhone tPV ON (tPV.addressid = tA.id AND tPV.typeid = 4)
LEFT JOIN tblPhone tPT ON (tPT.addressid = tA.id AND tPV.typeid = 6)
WHERE  first_name = @first_name and last_name = @last_name
     AND   tA.active=1
ORDER BY last_name, first_name, company, state, city
GO
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:llputney
ID: 17076590
No, BOTH same name, different address AND same name, same address.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17076603
hmmm....if you pull back all with the same name and same address, and the same name and different address, then you'll always pull back everyone with the same name, regardless of the address.  Do you want 2 different checks then?  One for each?
0
 

Author Comment

by:llputney
ID: 17076761
Should I do a second check? How could I do that?
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17076839
You'd have to call 2 procedures, instead of just 1.  I'm just trying to figure out what you're trying to do with "BOTH same name, different address AND same name, same address.".  Do you want a list of all same names, and indicate whether the address matched or not?
0
 

Author Comment

by:llputney
ID: 17077128
It would be nice to be able to see same first name and last name even with a different address because it might tell us that it is the same person that moved to a different address.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17077349
OK, how about:

CREATE PROCEDURE xp_DupCheck3 (
     @first_name     nvarchar(64),
     @last_name     nvarchar(64),
     @address        nvarchar(64)
) AS
SELECT tA.id AS Address_id,tA.*, @address AS "NewAddress"
     tPV.phonenumber AS "VoicePhone", tPT.phonenumber AS "TTYPhone"
FROM tblAddress tA
LEFT JOIN tblPhone tPV ON (tPV.addressid = tA.id AND tPV.typeid = 4)
LEFT JOIN tblPhone tPT ON (tPT.addressid = tA.id AND tPV.typeid = 6)
WHERE  first_name = @first_name and last_name = @last_name
     AND   tA.active=1
ORDER BY last_name, first_name, company, state, city
GO
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL: launch actions one before the other 10 23
Loop to go backward 90 days 2 18
SQL Server 2012 - Merge Replication Issue 1 20
TSQL - How to declare table name 26 29
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question