troubleshooting Question

SQL Selection with bad data

Avatar of SimonPrice3376
SimonPrice3376 asked on
SQL
4 Comments1 Solution61 ViewsLast Modified:
Hi,

I have been asked to work with a query that gets a series of results which works quite well other than one table has has records with the same branch code, but a different branch text information.

for instance
                 
65510000        5550000       Dave               Wildes      273      
65510000        5550000       David              Wildes      273      
65510000        5550001       Dave               Wildes      273      

The above section of results the first two records are actually the same, just someone has managed to enter the information in twice with a different way of providing the text, and the third result is correct because of the second column.

What I am after is how to get just two records back but am a little stumped at the moment so any and all help would be appreciated.

The SQL I am using to generate this result is

WITH CTE AS (
SELECT DISTINCT
  
  AF.DLR_CD				AS [Branch]				,
  AF.DLR_NM				AS [BranchText]			,		
  AF.DLR_CITY			AS [BranchTown]			,
  AF.DLR_POST_CD		AS [BranchPostalCode]
FROM tableName_af  AF
LEFT JOIN tableName_bm  BM ON BM.BRANCH_ACC_CD = SUBSTRING(AF.DLR_CD, 2, LEN(AF.DLR_CD)) -- JOINING ON A SUBSTRING DUE TO BM AND BN TABLES DO NOT HAVE THE PREFIXED NUMBER 
LEFT JOIN tableName_bn BN ON BM.BRANCH_ACC_CD = BN.BRANCH_ACC_CD AND 
							 BM.PRNCPL_ACC_CD = BN.PRNCPL_ACC_CD
WHERE 
BM.PRNCPL_ACC_CD = @MainDealer),

I have disguised the actual data, but this is an accurate representation of the data.

If i remove branch text from this query it looks fine, however i need branch text as part of the results.

Any and all help appreciated
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 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 4 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