SELECT *
FROM
(
(SELECT distinct de.last_name AS Name, de.first_name , GN as Data,
CONCAT( "", "? 3140 ADHD or 78057 OSA", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM drugs dr, demographic de
WHERE dr.demographic_no = de.demographic_no
AND de.patient_status = 'AC'
AND ATC RLIKE 'N06B'
AND rx_date > ((CURDATE() + 0)- {num}0000)
AND de.provider_no = '{provider_no}'
AND de.demographic_no
NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('3140','78057','78051','78053','78603') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name , GN as Data,
CONCAT( "", "? 496 COPD or 493 Asthma", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM drugs dr, demographic de
WHERE dr.demographic_no = de.demographic_no
AND de.patient_status = 'AC'
AND ATC RLIKE 'R03BB'
AND rx_date > ((CURDATE() + 0)- {num}0000)
AND de.provider_no = '{provider_no}'
AND de.demographic_no
NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('496','491','492','494','497','498','493','4930','49301','49302','4931','49311','49312','4939','49391','49392') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name , GN as Data,
CONCAT( "", "? 428 CHF or 5719 CLD", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM drugs dr, demographic de
WHERE dr.demographic_no = de.demographic_no
AND de.patient_status = 'AC'
AND ATC RLIKE 'C03DA'
AND rx_date > ((CURDATE() + 0)- {num}0000)
AND de.provider_no = '{provider_no}'
AND de.demographic_no
NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('428','4280','4281','4289','571','5710','5711','5712','5713','5714','5715','5716','5718','5719') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name , GN as Data,
CONCAT( "", "? 42731 AFib or 4539 DVT", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM drugs dr, demographic de
WHERE dr.demographic_no = de.demographic_no
AND de.patient_status = 'AC'
AND ATC RLIKE 'B01AA|B01AE|B01AF'
AND rx_date > ((CURDATE() + 0)- {num}0000)
AND de.provider_no = '{provider_no}'
AND de.demographic_no
NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('42731','4273','453','4538','4539') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name , dataField as Data,
CONCAT( "", "042 HIV", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurementsExt me, measurements m
LEFT JOIN demographic de ON de.demographic_no = m.demographicNo
WHERE keyval = 'name'
AND val RLIKE 'HIV'
AND m.id = me.measurement_id
AND m.dataField NOT RLIKE 'No|Sent|Referred|TNP|Neg|See comment'
AND de.provider_no = '{provider_no}'
AND de.patient_status = 'AC'
AND de.demographic_no NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('042')
and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name , dataField as Data,
CONCAT( "", "5790 Celiac disease", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM measurementsExt me, measurements m
LEFT JOIN demographic de ON de.demographic_no = m.demographicNo
WHERE keyval = 'identifier'
AND val in ("31017-7")
AND m.id = me.measurement_id
AND m.dataField > 20
AND de.provider_no = '{provider_no}'
AND de.patient_status = 'AC'
AND de.demographic_no NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('5790')
and dx.demographic_no = de.demographic_no)
GROUP BY de.demographic_no
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name , GN as Data,
CONCAT( "", "? 401 HBP or 428 CHF or 414 IHD", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM drugs dr, demographic de
WHERE dr.demographic_no = de.demographic_no
AND de.patient_status = 'AC'
AND ATC RLIKE 'C09'
AND rx_date > ((CURDATE() + 0)- {num}0000)
AND de.provider_no = '{provider_no}'
AND de.demographic_no
NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('428','4280','4281','4289','401','4011','4019','414','4140','41400','41401','41402','41403','41404','41405') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name , GN as Data,
CONCAT( "", "? 401 HBP or 428 CHF or 414 IHD", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM drugs dr, demographic de
WHERE dr.demographic_no = de.demographic_no
AND de.patient_status = 'AC'
AND ATC RLIKE 'C07'
AND rx_date > ((CURDATE() + 0)- {num}0000)
AND de.provider_no = '{provider_no}'
AND de.demographic_no
NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('428','4280','4281','4289','401','4011','4019','414','4140','41400','41401','41402','41403','41404','41405') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name , GN as Data,
CONCAT( "", "? 428 CHF", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM drugs dr, demographic de
WHERE dr.demographic_no = de.demographic_no
AND de.patient_status = 'AC'
AND ATC RLIKE 'C03BA08'
AND rx_date > ((CURDATE() + 0)- {num}0000)
AND de.provider_no = '{provider_no}'
AND de.demographic_no
NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('428','4280','4281','4289') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name , GN as Data,
CONCAT( "", "? 428 CHF or 42731 AFib ", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM drugs dr, demographic de
WHERE dr.demographic_no = de.demographic_no
AND de.patient_status = 'AC'
AND ATC RLIKE 'C01AA'
AND rx_date > ((CURDATE() + 0)- {num}0000)
AND de.provider_no = '{provider_no}'
AND de.demographic_no
NOT IN
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code in ('428','4280','4281','4289','42731','4273') and dx.demographic_no = de.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;
SELECT "1" AS yr, '1 year' AS typeDisplayName
UNION
SELECT "2" AS yr, '2 years' AS typeDisplayName
UNION
SELECT "3" AS yr, '3 years' AS typeDisplayName
UNION
SELECT "4" AS yr, '4 years' AS typeDisplayName
UNION
SELECT "5" AS yr, '5 years' AS typeDisplayName
UNION
SELECT "6" AS yr, '6 years' AS typeDisplayName
UNION
SELECT "7" AS yr, '7 years' AS typeDisplayName
UNION
SELECT "8" AS yr, '8 years' AS typeDisplayName
UNION
SELECT "9" AS yr, '9 years' AS typeDisplayName
UNION
SELECT "10" AS yr, '10 years' AS typeDisplayName