SQL HELP: Best method to determine vaccination compliance

Hi Experts,

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

SELECT HEALTH_id
, 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
INTO #VAX_COUNT
FROM #VAX C
GROUP BY HEALTH_id, last_name, first_name, VAX
ORDER BY last_name, first_name
britpopfan74Asked:
Who is Participating?
 
UnifiedISCommented:
Instead of a Sum, use COUNT


SELECT A.HEALTH_id,
      A.last_name,
      A.first_name,
      Compliant = CASE WHEN ISNULL(TDAP.VaxCount, 0) > 0 AND ISNULL(MENG.VaxCount, 0) > 0 THEN 'Yes'
                              ELSE 'No'
                        END
FROM (SELECT DISTINCT HEALTH_id, last_name, first_name FROM #VAX) PEOPLE
LEFT OUTER JOIN (
      SELECT COUNT(*) AS VaxCount,
            HEALTH_id
      FROM #VAX
      WHERE VAX = 'TDAP'
      GROUP BY HEALTH_id) TDAP
      ON TDAP.HEALTH_id = PEOPLE.HEALTH_id
LEFT OUTER JOIN (
      SELECT COUNT(*) AS VaxCount,
            HEALTH_id
      FROM #VAX
      WHERE VAX = 'MENG'
      GROUP BY HEALTH_id) MENG
      ON MENG.HEALTH_id = PEOPLE.HEALTH_id
0
 
UnifiedISCommented:
You can wrap another select around your existing one to evaluate your sum results

SELECT A.HEALTH_id,
A.last_name,
A.first_name,
Compliant = CASE WHEN TDAP = 1 AND MENG = 1 THEN 'Yes'
ELSE 'No'
END
FROM (
SELECT HEALTH_id
, 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
INTO #VAX_COUNT
FROM #VAX C
GROUP BY HEALTH_id, last_name, first_name, VAX
ORDER BY last_name, first_name
) A
0
 
britpopfan74Author Commented:
This is great if I can get this derived table working but this is the problem I had before -- all members are showing up as below as "NO":

HEALTH_id      last_name      first_name      Compliant
001                      Bo                      Diddly                          No

This is why I think we have to (TDAP + MENG) in possibly another derived table with a CASE statement to see if it is = 2, YES else it is NO.

Thoughts, suggestions?
0
 
britpopfan74Author Commented:
Thank you so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.