Duplicate accounts checking not working. 1

[Chuck712]

We have users creating duplicate customer accounts. To help cut down on this, I have a stored procedure that uses soundex to warn the users before saving the record. However it is not evaluating like I want. I was able to add customers 3 times without the address line being taken into account.

ALTER   PROCEDURE [dbo].[co_individual_dup_check_NEW] 

	@ind_first_name nvarchar(255),
	@ind_last_name nvarchar(255),
	@adr_line1 nvarchar (255), 
	@adr_state nvarchar(255),
	@adr_city nvarchar(255),
	@ad2_adr_line1 nvarchar (255), 
	@ad2__adr_state nvarchar(255),
	@ad2__adr_city nvarchar(255),
	@ind_cst_key av_key

AS

set nocount on

select ind_cst_key,
 [Individual] = isnull(cxa.cxa_mailing_label_html, ind_full_name_cp)
from co_individual (nolock) 
 join co_customer (nolock) on cst_key = ind_cst_key 
 left join co_customer_x_address cxa (nolock) on cxa.cxa_key = cst_cxa_key 
 left join co_address adr (nolock) on adr.adr_key = cxa.cxa_adr_key 
where --soundex(ind_first_name)=soundex(@ind_first_name)and 
   soundex(ind_last_name)=soundex( @ind_last_name)
 and ( (adr.adr_line1 is null)--added to evaluate address line 1 3-27-08 CSH
 or  (adr.adr_line1 = @adr_line1)--added 3/27/08 CSH
 or  (adr.adr_line1 = @ad2_adr_line1) )--added 3-27-08 CSH
 and ( (adr.adr_state is null and adr.adr_city is null ) 
 or (adr.adr_state = @adr_state and adr.adr_city = @adr_city ) 
  or (adr.adr_state = @ad2__adr_state and adr.adr_city = @ad2__adr_city) )
 and (ind_cst_key is not null or ind_cst_key <> @ind_cst_key)
 and @ind_cst_key is null
 and cst_delete_flag=0

 

[gmmastros]

I think your problem is in the where clause.

When you use a left join but put where clause conditions, the left join will behave like an inner join. Instead, you should put those conditions in to the join clause.

Try this...

select 	ind_cst_key,
 		[Individual] = isnull(cxa.cxa_mailing_label_html, ind_full_name_cp)
from 	co_individual (nolock)
		join co_customer (nolock) on cst_key = ind_cst_key
		left join co_customer_x_address cxa (nolock) on cxa.cxa_key = cst_cxa_key
		left join co_address adr (nolock) 
          on  adr.adr_key = cxa.cxa_adr_key
          and ((adr.adr_line1 is null)--added to evaluate address line 1 3-27-08 CSH
              or (adr.adr_line1 = @adr_line1)--added 3/27/08 CSH
              or (adr.adr_line1 = @ad2_adr_line1))--added 3-27-08 CSH
          and ((adr.adr_state is null and adr.adr_city is null )
              or (adr.adr_state = @adr_state and adr.adr_city = @adr_city )
              or (adr.adr_state = @ad2__adr_state and adr.adr_city = @ad2__adr_city))
where --soundex(ind_first_name)=soundex(@ind_first_name)and
   		soundex(ind_last_name)=soundex(@ind_last_name)
		and (ind_cst_key is not null or ind_cst_key <> @ind_cst_key)
		and @ind_cst_key is null
		and cst_delete_flag=0

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chuck712]

Thanks George
That improved some of the results, but I am thinking that the soundex value is the real problem. When putting in the first name of "judy", last name "smythe" already knowing this will create a duplicate on judith smythe, it let it pass, but found another person in a completely different state. I am experimenting with using substring for the first name and soundex for the last name. I will let you know. You have been a great help in the past

 

[gmmastros]

I recommend you read these:

thread183-635534
thread183-1103044
thread183-1194707

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chuck712]

Thanks again. The first thread was the most interesting. So far for my problem, using the substring function on the first 3 letter of the first name is bringing back what our users need. But this might be a better implementation.