SQL Puzzle | The Duplicate diagnosis Puzzle

In the puzzle we have check if any DiagnosisId and/or Code is a duplicate for a unique PatientVisitId and
return a single row – basically a yes or no. So in coding terms – create a new column called DuplicateEntry, The
value of this column = if a duplicate Diagnosis code is found on the visit, then 0
else 1

Please check out the sample input values and sample expected output below.

Sample Input

DiagnosisId PatientVisitId Code ListOrder
39887 620558 M48.02 1
39887 620558 M48.02 2
109487 620558 M50.123 3
109487 620551 M50.123 3
109481 620556 M50.123 2
109487 620556 M50.123 9

Expected Output

PatientVisitId DuplicateEntry
620551 1
620556 1
620558 0

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABlE diago ( DiagnosisId INT ,PatientVisitId INT ,Code varchar(100) ,ListOrder INT ) GO INSERT INTO diago VALUES (39887 ,620558 ,'M48.02', 1 ), (39887 ,620558 ,'M48.02', 2 ), (109487 ,620558 ,'M50.123', 3 ), (109487 ,620551 ,'M50.123', 3 ), (109481 ,620556 ,'M50.123', 2 ), (109487 ,620556 ,'M50.123', 9 ) GO select * from diago -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

 ``` -- select PatientVisitId , CASE WHEN SUM(a) = SUM(DISTINCT b) THEN 1 ELSE 0 END DuplicateEntry FROM ( select *, COUNT(*) OVER (PARTITION BY PatientVisitId,DiagnosisId ) a ,COUNT(DiagnosisId) OVER (PARTITION BY PatientVisitId ) b from diago d )r GROUP BY PatientVisitId -- ```

Output

```
/*------------------------
select PatientVisitId , CASE WHEN SUM(a) = SUM(DISTINCT b) THEN 1 ELSE 0 END DuplicateEntry
FROM
(
select *, COUNT(*) OVER (PARTITION BY PatientVisitId,DiagnosisId ) a
,COUNT(DiagnosisId) OVER (PARTITION BY PatientVisitId ) b
from diago d
)r GROUP BY PatientVisitId
------------------------*/
PatientVisitId DuplicateEntry
-------------- --------------
620551         1
620556         1
620558         0

(3 row(s) affected)

--
```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com