Check for changes in all other columns based on si

Question

FirstName     LastName     SSN            Phone          EncounterID
Justin        Kelley      555-55-5555     517-555-1212    123456789
Justin        Kelly       555-55-5555     517-555-1212    123456789
James         Smith       444-44-4444     312-555-3434    99944444
James         Smith       444-44-4444     312-555-3434    99944444

I have a table like the one above with millions of EncounterIDs. I need to know the number of times there is a difference (defect) in EACH column. My example output would be:

First Name - 2/2
Last Name - 1/2
SSN - 2/2
Phone - 2/2

Any help here?

Answer 1

The data that you basically want is the number of entities that have more than one value in a column.

This is most easily calculated on a column basis:

select sum(case when NumFirstNames <> 1 then 1 else 0 end) as DifferentFirstNames,
       sum(case when NumLastNames <> 1 then 1 else 0 end) as DifferentLastNames,
       sum(case when NumSSN <> 1 then 1 else 0 end) as DifferentSSN,
       sum(case when NumPhone <> 1 then 1 else 0 end) as DifferentPhone       
from (select EncounterId, count(*) as Num,
             count(distinct FirstName) as NumFirstNames,
             count(distinct LastName) as NumLastNames,
             count(distinct SSN) as NumSSN,
             count(distinct Phone) as NumPhone
      from table t
      group by EncounterId
     ) e;

You can format the results however you like.