Sometimes I'm not sure if my questions should be here or StackOverflow, so I appologize if this is the wrong place.
I have a SQL (MS SQL 2016) stored procedure that is evaluating data looking for duplicates. Since not all data is present missing fields are blank ('') not NULL. I compare the data and give a confidence score.
It works for the most part, however, it doesn't process any of the entries with a blank Company. Ideally I think I'd want the blank company to be processed just like the other companies. I'm not entirely confident that it is calculating correctly to catch everything.
SELECT ranking AS score, RankMapping.description AS code, 'CUST' AS tablename, Duplicate.uid AS tableID, originalCustNo FROM ( SELECT FirstCompany.uid AS originalCustNo, Duplicate.*, CASE WHEN FirstCompany.uid = Duplicate.uid THEN 1 ELSE 0 END + CASE WHEN FirstCompany.company = Duplicate.company THEN 1 ELSE 0 END + CASE WHEN FirstCompany.fname = Duplicate.fname AND FirstCompany.lname = Duplicate.lname THEN 1 ELSE 0 END + CASE WHEN FirstCompany.add1 = Duplicate.add1 AND FirstCompany.city = Duplicate.city THEN 1 ELSE 0 END + CASE WHEN FirstCompany.phone1 = Duplicate.phone1 THEN 1 ELSE 0 END + CASE WHEN FirstCompany.email = Duplicate.email THEN 1 ELSE 0 END AS ranking FROM ( SELECT uid, company, fname, lname, add1, city, phone1, email FROM ( SELECT uid, company, fname, lname, add1, city, phone1, email, ROW_NUMBER() OVER (PARTITION BY company ORDER BY uid) AS ordering FROM #tmpCoreData ) FC WHERE ordering = 1 ) AS FirstCompany JOIN #tmpCoreData Duplicate ON Duplicate.uid >= FirstCompany.uid AND Duplicate.company = FirstCompany.company) Duplicate JOIN (VALUES (6, 'Original'), (5, 'Duplicate'), (4, 'Duplicate'), (3, 'Likely Dupe'), (2, 'Possible Dupe'), (1, 'Not Likely Dupe'), (0, 'Individual') ) RankMapping(score, description) ON RankMapping.score = Duplicate.ranking ORDER BY Duplicate.originalCustNo, Duplicate.ranking DESC
I put my data table and code into SQL Fiddle I'd appreciate some fresh eyes and suggestions to improve.