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

Expected Output

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