I'm trying to compare some existing data that has some bad identifiers in our database to a new table of standardized data.
Here's a sample of the data and query I'm trying to join based on a couple different fields which is returning some bad data:
DECLARE @Existing TABLE (ID varchar(20),Name varchar(20))
DECLARE @New TABLE (ID varchar(20),Name varchar(20),Division varchar(20))
INSERT INTO @Existing
VALUES ('AB1234560','ACME'),('AB123450','Test Incorporated'),('DEF123456-00','Demo Co.')
INSERT INTO @New
VALUES ('AB1234560','ACME Inc.','Main'),('AB123456A','ACME Inc.','Child'),('AB123456AB','ACME Inc.','Child'),
('AB123450','Test Inc.','Main'),('AB12345A','Test Inc.','Child'),('AB12345AB','Test Inc.','Child'),
('DEF123456-00','Demo Corp.','Main'),('DEF123456-01','Demo Corp.','Child')
SELECT E.Name ExistName, E.ID ExistingID,
COALESCE(N1.Name, N2.Name, N3.Name) NewName,
COALESCE(N1.Division, N2.Division, N3.Division) NewDivision,
COALESCE(N1.ID, N2.ID, N3.ID) NewID
FROM
@Existing E LEFT OUTER JOIN
@New N1 ON LEFT(N1.ID,3) = 'DEF' AND LEFT(N1.ID,9) = LEFT(E.ID,9) LEFT OUTER JOIN
@New N2 ON LEFT(N2.ID,2) = 'AB' AND LEFT(N2.ID,8) = LEFT(E.ID,8) AND LEN(E.ID) = 9 LEFT OUTER JOIN
@New N3 ON LEFT(N3.ID,2) = 'AB' AND LEFT(N3.ID,7) = LEFT(E.ID,7) AND LEN(E.ID) = 8
GROUP BY E.Name, E.ID, N1.Name, N2.Name, N3.Name, N1.Division, N2.Division, N3.Division, N1.ID, N2.ID, N3.ID
This returns back good data for the Demo Corp and Acme, the one existing ID matches to new divisions and that's good, the problem is with the Test company, it matches to both Test and Acme.
The actual data has 4 types and it looks like 2 of those have the different ID lengths that I need to try to and account for so that will end up having 6 LEFT OUTER's with 6 COALESCE expressions per column.
Any better ideas? (Can't use the name in the join, it's a mess in our existing tables)
This is what I would like to have returned:
ACME Inc. AB1234560 ACME Inc. Child AB123456A
ACME Inc. AB1234560 ACME Inc. Child AB123456AB
ACME Inc. AB1234560 ACME Inc. Main AB1234560
Demo Corp. DEF123456-00 Demo Corp. Child DEF123456-01
Demo Corp. DEF123456-00 Demo Corp. Main DEF123456-00
Test Inc. AB123450 Test Inc. Child AB12345A
Test Inc. AB123450 Test Inc. Child AB12345AB
Test Inc. AB123450 Test Inc. Main AB123450