?
Solved

Stored Procedure - Script Help for Duplicate Checks

Posted on 2006-07-10
9
Medium Priority
?
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 25

Accepted Solution

by:
jrb1 earned 2000 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

801 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