Solved

Stored Procedure - Script Help for Duplicate Checks

Posted on 2006-07-10
9
270 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 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

689 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