1
\$\begingroup\$

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.

Thanks!

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.