llputney
asked on
Stored Procedure - Script Help for Duplicate Checks
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
No, BOTH same name, different address AND same name, same address.
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?
ASKER
Should I do a second check? How could I do that?
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?
ASKER
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.
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
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
ASKER
How can I do both?