?
Solved

SQL HELP: Best method to determine vaccination compliance

Posted on 2012-08-21
4
Medium Priority
?
437 Views
Last Modified: 2012-08-21
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
0
Comment
Question by:britpopfan74
  • 2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 38316585
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
 

Author Comment

by:britpopfan74
ID: 38316701
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
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 2000 total points
ID: 38316816
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
 

Author Closing Comment

by:britpopfan74
ID: 38316863
Thank you so much!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question