Modifying a Stored Procedure

130 pts.
Tags:
Crystal Reports
SELECT statement
SQL
SQL Database
SQL stored procedures
Stored Procedures
The stored procedure below pulls suspected duplicate records. What I need it to do is return suspected duplicates only if it meets the criteria of having a member record type (GEN) & a non-member record type (WEB). But if the duplicates both have a non-member record type I don't want it showing up on the report.

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo]. AS BEGIN SELECT N.ID, N.LAST_NAME, N.FIRST_NAME, NA.ADDRESS_1, NA.ADDRESS_2, NA.CITY, NA.STATE_PROVINCE, NA.ZIP, NA.COUNTRY, N.MEMBER_TYPE FROM NAME N, NAME_ADDRESS NA, ( SELECT LAST_NAME, STATE_PROVINCE, COUNTRY, COUNT(1) COUNT1 FROM NAME WHERE LAST_NAME <> ” AND STATE_PROVINCE <> ” GROUP BY LAST_NAME, STATE_PROVINCE, COUNTRY HAVING COUNT(1) > 1) T WHERE T.LAST_NAME = N.LAST_NAME AND T.STATE_PROVINCE = NA.STATE_PROVINCE AND T.STATE_PROVINCE = N.STATE_PROVINCE AND T.COUNTRY = NA.COUNTRY AND N.ID = NA.ID AND N.MAIL_ADDRESS_NUM = NA.ADDRESS_NUM – AND T.LAST_NAME = ‘HARRISON’ ORDER BY N.LAST_NAME, N.STATE_PROVINCE, N.COUNTRY END

Thanks, DJ



Software/Hardware used:
SQL, Crystal Reports, iMIS 10.6
ASKED: June 9, 2011  2:31 PM
UPDATED: June 9, 2011  2:37 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following