quote:

---

Originally posted by nosepicker

So in the case of the patient 'ZZYZZ, SPLEEN W. MCCOY', what output do you want? Do you want:

33 SSC ZZYZZ, SPLEEN W. MCCOY 428.0 250.00

because of the multiple maladies, or do you want:

33 SSS ZZYZZ, SPLEEN W. MCCOY 428.0

because of the 'SSS' payor, or do you want:

33 SSS ZZYZZ, SPLEEN W. MCCOY 428.0 250.00

because you want both the 'SSS' payor and the multiple maladies?

---

SELECT C.memnum, C.payor, C.memname, B.MaxDoctor, B.MaxDiag1, B.MaxDiag2 
FROM 
(SELECT A.memnum, CASE A.payor WHEN 1 THEN 'SSS' WHEN 2 THEN 'SSC' WHEN 3 THEN 'XPLAN' END as payor, A.memname 
FROM	(SELECT 
	memnum, 
	MIN(CASE payor WHEN 'SSS' THEN 1 WHEN 'SSC' THEN 2 WHEN 'XPLAN' THEN 3 END) as payor, 
	memname 
	FROM #patient 
	GROUP BY memnum, memname) AS A
) AS C 

JOIN 

(SELECT 
memname, 
MAX(doctor) as MaxDoctor, 
MAX(diag1) as MaxDiag1, 
MAX(CASE WHEN diag2 IS NULL THEN 0 ELSE diag2 END) as MaxDiag2
FROM #patient 
GROUP BY memname) AS B 

ON C.memname = B.memname

SELECT C.memnum, C.payor, C.memname, B.MaxDoctor, B.MaxDiag1, B.MaxDiag2 
FROM 
(SELECT A.memnum, CASE A.payor WHEN 1 THEN 'SSS' WHEN 2 THEN 'SSC' WHEN 3 THEN 'XPLAN' END as payor, A.memname 
FROM	(SELECT 
	memnum, 
	MIN(CASE payor WHEN 'SSS' THEN 1 WHEN 'SSC' THEN 2 WHEN 'XPLAN' THEN 3 END) as payor, 
	memname 
	FROM #patient 
	GROUP BY memnum, memname) AS A
) AS C 

JOIN 

(SELECT 
memnum, 
MAX(doctor) as MaxDoctor, 
MAX(diag1) as MaxDiag1, 
MAX(CASE WHEN diag2 IS NULL THEN 0 ELSE diag2 END) as MaxDiag2
FROM #patient 
GROUP BY memnum) AS B 

ON C.memnum= B.memnum