I'm writing a program to determine which children are compliant (yes to both) on their vaccines -- abbreviated here as "MENG" and "DTAP".
After gathering who has which claims, I'm able to code below with 0/1 who has each vaccine but then am having trouble with the best way to have one row per member that reads:
HEALTH_id last_name first_name Compliant
0001 Jones Sue NO
0002 Smith Brad YES
I know that I need to SUM my TDAP + MENG but as it is now, is the only way to do this a self-join of the table or a derived table?
The last steps I have in my code get me to here:
HEALTH_id last_name first_name TDAP MENG
0001 Jones Sue 0 1
0002 Smith Brad 1 1
Thank you in advance!
--DROP TABLE #VAX_COUNT
--SELECT * FROM #VAX_COUNT
, last_name, first_name
, SUM(CASE WHEN C.VAX = 'TDAP' THEN 1 ELSE 0 END) AS TDAP
, SUM(CASE WHEN C.VAX = 'MENG' THEN 1 ELSE 0 END) AS MENG --SUM TO SEE WHO COMPLIANT
FROM #VAX C
GROUP BY HEALTH_id, last_name, first_name, VAX
ORDER BY last_name, first_name