[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedure - Script Help for Duplicate Checks

Posted on 2006-07-10
9
Medium Priority
?
276 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

868 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