SELECT *
FROM (
(SELECT d.last_name AS Name,d.first_name AS FirstName, m.dataField AS Data,
CONCAT( "", "07054 Hep C", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurementsExt me, measurements m
LEFT JOIN demographic d ON d.demographic_no = m.demographicNo
WHERE keyval = 'name'
AND val RLIKE 'Hep C'
AND m.id = me.measurement_id
AND m.dataField NOT RLIKE 'No|Sent|Referred|TNP|Neg|See comment'
AND d.provider_no = {provider_no}
AND d.patient_status = 'AC'
AND d.demographic_no NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('07054')
and dx.demographic_no = d.demographic_no)
GROUP BY m.demographicNo
)
UNION ALL
(SELECT d.last_name AS Name,d.first_name AS FirstName, m.dataField AS Data,
CONCAT( "", "2809 Fe Def", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurementsExt me, measurements m
LEFT JOIN demographic d ON d.demographic_no = m.demographicNo
WHERE keyval = 'identifier'
AND val in ("2276-4")
AND m.id = me.measurement_id
AND m.dataField < 15
AND d.provider_no = {provider_no}
AND d.patient_status = 'AC'
AND m.dataField NOT RLIKE 'Not'
AND d.demographic_no NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('280','2809','2801','2808','2750')
and dx.demographic_no = d.demographic_no)
GROUP BY d.last_name,d.first_name
)
UNION ALL
(SELECT d.last_name AS Name,d.first_name AS FirstName, m.dataField AS Data,
CONCAT( "", "428 CHF", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurementsExt me, measurements m
LEFT JOIN demographic d ON d.demographic_no = m.demographicNo
WHERE keyval = 'identifier'
AND val in ("30934-4","33762-6")
AND m.id = me.measurement_id
AND m.dataField > 500
AND d.provider_no = {provider_no}
AND d.patient_status = 'AC'
AND m.dataField NOT RLIKE 'Not'
AND d.demographic_no NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('428','4280','4281','4289')
and dx.demographic_no = d.demographic_no)
GROUP BY d.last_name,d.first_name
)
UNION ALL
(SELECT Name, FirstName, Data,DxRegistry as "Dx Registry", eChart
FROM
(
SELECT d.last_name as "Name",d.first_name as "FirstName", m.dataField AS "Data" ,COUNT(d.demographic_no) AS Number,
CONCAT( "", "250 Diabetes", "" ) AS "DxRegistry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurementsExt me, measurements m
LEFT JOIN demographic d ON d.demographic_no = m.demographicNo
WHERE keyval = 'identifier'
AND val in ("4548-4")
AND m.id = me.measurement_id
AND m.dataField > 6.4
AND d.provider_no = {provider_no}
AND d.patient_status = 'AC'
AND m.dataField NOT RLIKE 'Not'
AND d.demographic_no NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('250','2500','2504','2505','2506','2507','2508','2509')
and dx.demographic_no = d.demographic_no)
GROUP BY d.last_name,d.first_name
) a
WHERE Number > 1
)
UNION ALL
(SELECT d.last_name AS Name,d.first_name AS FirstName, m.dataField AS Data,
CONCAT( "", "2449 Hypothyroid", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurementsExt me, measurements m
LEFT JOIN demographic d ON d.demographic_no = m.demographicNo
WHERE keyval = 'identifier'
AND val in ("3016-3")
AND m.id = me.measurement_id
AND m.dataField > 10
AND d.provider_no = {provider_no}
AND d.patient_status = 'AC'
AND m.dataField NOT RLIKE 'Not'
AND d.demographic_no NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('244','2440','2441','2442','2443','2444','2448','2449')
and dx.demographic_no = d.demographic_no)
GROUP BY d.last_name,d.first_name
)
UNION ALL
(SELECT Name, FirstName, Data,DxRegistry as "Dx Registry", eChart
FROM
(
SELECT d.last_name as "Name",d.first_name as "FirstName", m.dataField AS "Data" ,COUNT(d.demographic_no) AS Number,
CONCAT( "", "585 CKD", "" ) AS "DxRegistry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurementsExt me, measurements m
LEFT JOIN demographic d ON d.demographic_no = m.demographicNo
WHERE keyval = 'identifier'
AND val in ("33914-3","62238-1")
AND m.id = me.measurement_id
AND m.dataField < 60
AND d.provider_no = {provider_no}
AND d.patient_status = 'AC'
AND m.dataField NOT RLIKE 'Not|> 120|>120'
AND d.demographic_no NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('585')
and dx.demographic_no = d.demographic_no)
GROUP BY d.last_name,d.first_name
) a
WHERE Number > 1
)
) 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;