SELECT *
FROM (
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "2449 Thyroid", "" ) 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 'H03A'
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 ('244','2440','244','2442','2443','2444','2448','2449') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "2900 Dementia", "" ) 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 'N06D'
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 ('290','2900','2901','2902','2903','2909','29010','2904','2912','29282','2941','29410',
'29411') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "311 Depression", "" ) 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 'N06AB|N06AX16|N06AX23'
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 ('311','2963','2962','300','3000','30001','30002') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "3000 Anxiety", "" ) 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 'N05BE01'
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 ('300','3000','30001','30002') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "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 'C01DA02'
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 ('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 ,
CONCAT( "", "73300 Osteoporosis", "" ) 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 'M05B'
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 ('7330','73300','73301','73302','73303','73309') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "274 Gout", "" ) 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 'M04AA'
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 ('274','2740','2748','27489','2749') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "346 Migraine", "" ) 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 'N02CC'
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 ('346','3460','3461','3462','3468','3469') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "1749 Ca Breast", "" ) 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 'L02BA01|L02BG'
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 ('1749','174','1740','1741','1742','1743','1744','1745','1746','1747','1748') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "185 Ca Prostate", "" ) 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 'L02AE'
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 ('185') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "00845 C Diff", "" ) 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 'J01XA01'
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 ('00845') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "250 Diabetes", "" ) 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 'A10A|A10BB|A10BG|A10BH|A10BJ|A10BK'
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 ('250','2500','2504','2505','2506','2507','2508','2509') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "59651 OAB", "" ) 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 'G04BD'
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 ('59651','5965') and dx.demographic_no = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS Name, de.first_name ,
CONCAT( "", "496 COPD", "" ) 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 'R03BB04'
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') 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