troubleshooting Question

Best Approach to Comparing Address With No Matching Account Numbers Using Temp Tables

Avatar of TechIsaac
TechIsaacFlag for United States of America asked on
DatabasesSQL
8 Comments1 Solution96 ViewsLast Modified:
I am trying to do a comparison to see if the same customers are in 2 tables for different sales databases. In most cases, there is a matching account number in both tables, if the number has been entered properly or not null.

When there is no matching account number I need to look at the address for similarities.

Right now I am using CTE's but I am not getting the correct results to match by address similarity when there are no account number matches.

Would it be better to use temp tables and if so how?

Here is my code:

--first cte pulls in all not null account and reformat account numbers to match formatting of account numbers in 2nd table 

WITH Table1_Reformat_AccountNum_CTE
AS
(
  Select 
		  t1.account_number
		 ,t1.account_name
		 ,t1.address_1 
		 ,t1.address_2
         ,t1.city
		 ,t1.state
         ,t1.zip
		 ,CASE
			WHEN t1.account_number LIKE '%xyz-%' THEN LTRIM(RTRIM(RIGHT(t1.account_number, LEN(t1.account_number)-9)))
			WHEN t1.account_number LIKE 'qrs:% -lmo' THEN REPLACE(LTRIM(RTRIM(RIGHT(t1.account_number, LEN(t1.account_number)-5))), '-lmo', '')
			ELSE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RIGHT(t1.account_number, LEN(t1.account_number)-5), '-', ''), ' ', ''), '.', ''), ',', ''), 'out', ''), '#', ''), '0ut', ''), '''', ''), '_', ''), '*', ''),'<',''),'>',''),'^','')
			END AS account_num_formatted
  From [database_1].[dbo].[sales_table_1] t1
  WHERE t1.account_number IS NOT NULL
       
         
)
-- 2nd cte matched reformatted account numbers to account numbers in table 2
,Table2_AccountNum_Matches_CTE
AS
(
SELECT 
          t1.account_num_formatted 
         ,t2.account_number
		 ,t2.account_name
		 ,t2.address_1 
		 ,t2.address_2
         ,t2.city
		 ,t2.state
         ,t2.zip
     
FROM Table1_Reformat_AccountNum_CTE ref
INNER JOIN [database_2].[dbo].[sales_table_2] t2
ON t2.account_number = t1.account_num_formatted 
)
-- 3rd cte finds all left over accounts in table 1 that have no match in table 2

,Table1_Non_Matches_Cte
AS
(
SELECT 
		  t1.account_num_formatted
		 ,t1.account_name
		 ,t1.address_1 
		 ,t1.address_2
         ,t1.city
		 ,t1.state
         ,t1.zip 
	FROM Table1_Reformat_AccountNum_CTE ref
	WHERE t1.account_num_formatted NOT IN (SELECT acm.account_num_formatted FROM Table2_AccountNum_Matches_CTE acm)
)
-- 3rd cte finds all left over accounts in table 2 that have no match in table 1

,Table2_Non_Matches_Cte
AS
(
SELECT  
          t2.account_number
		 ,t2.account_name
		 ,t2.address_1 
		 ,t2.address_2
         ,t2.city
		 ,t2.state
         ,t2.zip
	FROM [database_2].[dbo].[sales_table_2] t2
    WHERE t2.account_number NOT IN (SELECT acm.account_num_formatted FROM Table2_AccountNum_Matches_CTE acm)
)

-- attemtps to find all accounts in table 2 that have no match in account number in table 1 but match on address 
SELECT 
          t1.account_num_formatted
		 ,t1.account_name
		 ,t1.address_1 
		 ,t1.address_2
         ,t1.city
		 ,t1.state
         ,t1.zip 
	     ,t2.account_number
		 ,t2.account_name
		 ,t2.address_1 
		 ,t2.address_2
         ,t2.city
		 ,t2.state
         ,t2.zip
    FROM Table1_Reformat_AccountNum_CTE ref
	FULL OUTER JOIN [database_2].[dbo].[sales_table_2] t2
	ON t1.account_num_formatted = t2.account_number
	WHERE t2.account_number IN (SELECT  non.account_number FROM Table2_Non_Matches_Cte non )
	AND t1.address_1  LIKE '%'+ t2.address_1 +'%'
	AND t1.city  LIKE '%'+ t2.city+'%'
	AND t2.state LIKE '%'+ t2.state+'%'
	AND t2.zip  = '%'+t2.zip+'%'

 Sample Data:

Table 1
——————
Acc | Name | Add 1| Add 2 | City | State | Zip
————————————————————-
1       store1   113 st lane    mulberry tx   12344
Null   store2   114 st lane    mulberry tx   34254
6       store3   113 st lane    mulberry tx   98764
7       store4   115 st lane    mulberry tx   74631
8.1    store5   116 st lane    mulberry tx   12347

Table 2 

——————
Acc | Name | Add 1| Add 2 | City | State | Zip
————————————————————-
1       store1   113 st lane   mulberry tx   12344
Null   store2   114 st lane   mulberry tx   34254
9       store8   213 ha ave   juniper    ct   46738
5      store9   217 wa ave  juniper    ct  98376
8     store5   116 st lane    mulberry    tx   12347

Expected Results:
Acc | Name | Add 1| Add 2 | City | State | Zip
————————————————————-
1        store1   113 st lane    mulberry tx   12344
Null   store2   114 st lane    mulberry tx   34254
8       store5   116 st lane   mulberry tx   12347
ASKER CERTIFIED SOLUTION
D B
Database Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros