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