[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Stored Procedure - Script Help for Duplicate Checks

Posted on 2006-07-10
9
Medium Priority
?
273 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
[Video] Oticon Case Study

Open office environments can create the dynamics for innovation, but they also bring some challenges. With over 1,000 employees in an open office, Oticon needed a solution that would preserve the environment while mitigating disruptive background noises.

Watch how they did it.

 

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

656 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