Skip to content

Instantly share code, notes, and snippets.

@sudarshan-webonise

sudarshan-webonise/Get Student Allergy and Medical Information

Created May 9, 2016 12:36
Show Gist options
  • Save sudarshan-webonise/60844b982693b1b89c3e3dd74c543d9d to your computer and use it in GitHub Desktop.
Save sudarshan-webonise/60844b982693b1b89c3e3dd74c543d9d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Get Student Allergy and Medical Information
SELECT DISTINCT u.USR_ID AS 'MHPID',
u.VENDOR_KEY AS 'Vendor Key',
u.LAST_NAME AS 'Last Name',
u.FIRST_NAME AS 'First Name',
CONVERT(u.DOB, Date) AS 'Date of Birth',
t.trCTM_Name AS 'Tracker',
g.listing AS 'Groups',
a.name AS 'Allergy Name',
COALESCE(ans_alother.ANS_String, ans_alother.ANS_Text, ans_alother.ANS_Longtext) AS 'Allergy Details',
COALESCE(ans_alreact.ANS_String, ans_alreact.ANS_Text, ans_alreact.ANS_Longtext) AS 'Allergy Reaction',
ans_alsever.name AS 'Severity of Reaction',
COALESCE(ans_alnotes.ANS_String, ans_alnotes.ANS_Text, ans_alnotes.ANS_Longtext) AS 'Notes',
ans_epipen.name AS 'Has Epi Pen?',
ans_epiexp.ANS_Date AS 'Epi Pen Expiration',
ans_asthma.yesno AS 'Has Asthma?',
ans_asthma.expl AS 'Asthma (Explain)',
ans_diabetes.yesno AS 'Has Diabetes?',
ans_diabetes.expl AS 'Diabetes (Explain)',
ans_epilepsy.yesno AS 'Has Epilepsy?',
ans_epilepsy.expl AS 'Epilepsy (Explain)',
ans_seizures.yesno AS 'Has Seizures?',
ans_seizures.expl AS 'Seizures (Explain)'
FROM
(SELECT um.USR_ID AS USR_ID,
um.USR_Vendor_Key AS VENDOR_KEY,
i.IND_LNAME AS LAST_NAME,
i.IND_FName AS FIRST_NAME,
perinf.IND_DOB AS DOB
FROM USER_MEMBERSHIPS um
INNER JOIN INDIVIDUALS i ON (um.USR_ID = i.IND_ID)
INNER JOIN PERSONAL_INFO perinf ON (i.IND_ID = perinf.IND_ID)
WHERE um.GRP_ID = :grpid
AND um.trCTM_Archive = 0) u
INNER JOIN TRACKER_CUSTOM_DASHBOARD tcd ON (tcd.USR_ID = u.USR_ID
AND tcd.GRP_ID = :grpid)
INNER JOIN TRACKER_CUSTOM t ON (t.trCTM_MainID = tcd.trCTM_MainID)
LEFT JOIN
(SELECT ul.USR_ID AS USR_ID,
GROUP_CONCAT(DISTINCT lbl.LBL_Name SEPARATOR '; ') AS listing
FROM USER_LABELS ul
INNER JOIN LABELS lbl ON (ul.GRP_ID = lbl.GRP_ID
AND ul.LBL_ID = lbl.LBL_ID)
WHERE lbl.GRP_ID = :grpid
GROUP BY ul.USR_ID) g ON (g.USR_ID = u.USR_ID)
LEFT JOIN
(SELECT ans_allergy.ANS_ID AS ID,
ans_allergy.ANS_ROW AS ROW_ID,
ans_allergy.USR_ID AS USR_ID,
sa.SEL_ANS_Name AS name
FROM MULTI_ANSWERS ans_allergy
INNER JOIN SELECTION_ANSWERS sa ON (ans_allergy.ANS_Int = sa.SEL_ANS_ID)
WHERE ans_allergy.QUE_ID = 27) a ON (a.USR_ID = u.USR_ID)
LEFT JOIN MULTI_ANSWERS ans_alother ON (ans_alother.USR_ID = u.USR_ID
AND a.ROW_ID = ans_alother.ANS_ROW
AND ans_alother.QUE_ID = 31)
LEFT JOIN MULTI_ANSWERS ans_alreact ON (ans_alreact.USR_ID = u.USR_ID
AND a.ROW_ID = ans_alreact.ANS_ROW
AND ans_alreact.QUE_ID = 28)
LEFT JOIN
(SELECT ma.ANS_ID AS ID,
ma.USR_ID AS USR_ID,
ma.ANS_ROW AS ROW_ID,
sa.SEL_ANS_Name AS name
FROM MULTI_ANSWERS ma
INNER JOIN SELECTION_ANSWERS sa ON (ma.ANS_Int = sa.SEL_ANS_ID)
WHERE ma.QUE_ID = 29) ans_alsever ON (ans_alsever.USR_ID = u.USR_ID
AND a.ROW_ID = ans_alsever.ROW_ID)
LEFT JOIN MULTI_ANSWERS ans_alnotes ON (ans_alnotes.USR_ID = u.USR_ID
AND a.ROW_ID = ans_alnotes.ANS_ROW
AND ans_alnotes.QUE_ID = 32)
LEFT JOIN
(SELECT ma.ANS_ID AS ID,
ma.ANS_ROW AS ROW_ID,
ma.USR_ID AS USR_ID,
sa.SEL_ANS_Name AS name
FROM MULTI_ANSWERS ma
INNER JOIN SELECTION_ANSWERS sa ON (ma.ANS_Int = sa.SEL_ANS_ID)
WHERE ma.QUE_ID = 500) ans_epipen ON (ans_epipen.USR_ID = u.USR_ID
AND a.ROW_ID = ans_epipen.ROW_ID)
LEFT JOIN MULTI_ANSWERS ans_epiexp ON (ans_epiexp.USR_ID = u.USR_ID
AND ans_epiexp.QUE_ID = 550
AND a.ROW_ID = ans_epiexp.ANS_ROW)
LEFT JOIN
(SELECT saq.ANS_ID AS ID,
saq.USR_ID AS USR_ID,
sa.SEL_ANS_Name AS yesno,
saa.ANS_String AS expl
FROM SINGLE_ANSWERS saq
INNER JOIN SELECTION_ANSWERS sa ON (saq.ANS_Int = sa.SEL_ANS_ID)
LEFT JOIN SINGLE_ANSWERS saa ON (saq.USR_ID = saa.USR_ID
AND saa.QUE_ID = saq.QUE_ID+1)
WHERE saq.QUE_ID = 432) ans_asthma ON (ans_asthma.USR_ID = u.USR_ID)
LEFT JOIN
(SELECT saq.ANS_ID AS ID,
saq.USR_ID AS USR_ID,
sa.SEL_ANS_Name AS yesno,
saa.ANS_String AS expl
FROM SINGLE_ANSWERS saq
INNER JOIN SELECTION_ANSWERS sa ON (saq.ANS_Int = sa.SEL_ANS_ID)
LEFT JOIN SINGLE_ANSWERS saa ON (saq.USR_ID = saa.USR_ID
AND saa.QUE_ID = saq.QUE_ID+1)
WHERE saq.QUE_ID = 434) ans_diabetes ON (ans_diabetes.USR_ID = u.USR_ID)
LEFT JOIN
(SELECT saq.ANS_ID AS ID,
saq.USR_ID AS USR_ID,
sa.SEL_ANS_Name AS yesno,
saa.ANS_String AS expl
FROM SINGLE_ANSWERS saq
INNER JOIN SELECTION_ANSWERS sa ON (saq.ANS_Int = sa.SEL_ANS_ID)
LEFT JOIN SINGLE_ANSWERS saa ON (saq.USR_ID = saa.USR_ID
AND saa.QUE_ID = saq.QUE_ID+1)
WHERE saq.QUE_ID = 436) ans_epilepsy ON (ans_epilepsy.USR_ID = u.USR_ID)
LEFT JOIN
(SELECT saq.ANS_ID AS ID,
saq.USR_ID AS USR_ID,
sa.SEL_ANS_Name AS yesno,
saa.ANS_String AS expl
FROM SINGLE_ANSWERS saq
INNER JOIN SELECTION_ANSWERS sa ON (saq.ANS_Int = sa.SEL_ANS_ID)
LEFT JOIN SINGLE_ANSWERS saa ON (saq.USR_ID = saa.USR_ID
AND saa.QUE_ID = saq.QUE_ID+1)
WHERE saq.QUE_ID = 436) ans_seizures ON (ans_seizures.USR_ID = u.USR_ID)
WHERE 1=1
AND (0=1
OR a.ID IS NOT NULL
OR ans_asthma.yesno = 'Yes'
OR ans_diabetes.yesno = 'Yes'
OR ans_epilepsy.yesno = 'Yes'
OR ans_seizures.yesno = 'Yes')
ORDER BY u.LAST_NAME,
u.FIRST_NAME,
t.trCTM_MainID ASC,
a.ID,
u.VENDOR_KEY;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment