SELECT *
FROM (
(SELECT demo.last_name AS 'Name' ,demo.first_name, m.dataField as "Result",
CONCAT( "", "27800 Obesity", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurements m, demographic demo, provider p
WHERE m.type ='BMI'
AND m.dataField >= 30
AND m.dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-240)*100)+1)
AND demo.demographic_no = m.demographicNo
AND demo.patient_status = 'AC'
AND demo.provider_no = {provider_no}
AND p.provider_no = {provider_no}
AND demo.demographic_no not in
(SELECT dx.demographic_no
FROM dxresearch dx
WHERE dx.dxresearch_code in ('278','2780','27800','27801')
AND dx.demographic_no = demo.demographic_no)
GROUP BY demo.last_name,demo.first_name
)
UNION ALL
(SELECT demo.last_name AS 'Name' ,demo.first_name, m.dataField,
CONCAT( "", "311 Depression", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurements m, demographic demo, provider p
WHERE m.type ='PHQ9'
AND m.dataField >= 15
AND demo.demographic_no = m.demographicNo
AND demo.patient_status = 'AC'
AND demo.provider_no = {provider_no}
AND p.provider_no = {provider_no}
AND demo.demographic_no not in
(SELECT dx.demographic_no
FROM dxresearch dx
WHERE dx.dxresearch_code in ('311','2963','2962')
AND dx.demographic_no = demo.demographic_no)
GROUP BY demo.last_name,demo.first_name
)
UNION ALL
(SELECT demo.last_name AS 'Name',demo.first_name, m.dataField,
CONCAT( "", "3000 Anxiety", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurements m, demographic demo, provider p
WHERE m.type ='GAD7'
AND m.dataField >= 10
AND demo.demographic_no = m.demographicNo
AND demo.patient_status = 'AC'
AND demo.provider_no = {provider_no}
AND p.provider_no = {provider_no}
AND demo.demographic_no not in
(SELECT dx.demographic_no
FROM dxresearch dx
WHERE dx.dxresearch_code in ('300','3000','30001','30002')
AND dx.demographic_no = demo.demographic_no)
GROUP BY demo.last_name,demo.first_name
)
UNION ALL
(SELECT demo.last_name AS 'Name',demo.first_name, m.dataField,
CONCAT( "", "2900 Dementia", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurements m, demographic demo, provider p
WHERE m.type ='MOCA'
AND m.dataField < 21
AND demo.demographic_no = m.demographicNo
AND demo.patient_status = 'AC'
AND demo.provider_no = {provider_no}
AND p.provider_no = {provider_no}
AND demo.demographic_no not in
(SELECT dx.demographic_no
FROM dxresearch dx
WHERE dx.dxresearch_code in ('290','2900','2901','2902','2903','2909','29010','2904','2912','29282','2941','29410','29411')
AND dx.demographic_no = demo.demographic_no)
GROUP BY demo.last_name,demo.first_name
)
UNION ALL
(SELECT distinct demo.last_name as "Name",demo.first_name as "first_name" ,demo.sex as "Result",
CONCAT( "", "799 Complex", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM billing b, billingmaster bm, demographic demo
WHERE (b.provider_no = '{provider_no}'
AND b.billing_no = bm.billing_no AND demo.demographic_no = b.demographic_no AND bm.billing_code = '14033'
AND demo.patient_status = 'AC')
AND b.billing_date > (((EXTRACT(YEAR from CURDATE()))*10000)+101)
AND demo.demographic_no
NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('799')
and dx.demographic_no = demo.demographic_no
)
)
) a
ORDER BY Name;
SELECT provider_no, concat(last_name,',',first_name,'(',provider_no,')') FROM provider WHERE status='1' AND provider_type='doctor' AND ohip_no>1 ORDER BY provider_no;