SELECT *
FROM (
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "42731 AFib", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Afib|Atrial fibrillation|A fibrillation|a fib'
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')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "4139 Angina", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Angina|ASHD'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('413','4139')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "493 Asthma", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Asthma'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('493','4930','49301','49302','4931','49311','49312','4939','49391','49392')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "6000 BPH", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'PROSTATISM|bph|benign prostate hypertrophy'
AND de.provider_no = '{provider_no}'
AND de.sex = 'M'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('600','6000','6002','6009')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "1749 Ca Breast", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'BreastCancer|breast cancer|breast Ca|Ca Breast|CaBreast'
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 e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "185 Ca Prostate", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'ProstateCancer|prostate cancer|prostate Ca|Ca Prostate|CaProstate'
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 e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "496 COPD", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'COPD|Emphysema|Chronic Bronchitis|Chronic Lung Disease|chronic obstructive lung disease'
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 e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "436 CVA", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'CVATIA|CVA|cerebrovascular accident|stroke|CV accident|transient ischaemic attack|cerebral atherosclerosis|cerebrovascular disease'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('433','434','435','436','437','438')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "2900 Dementia", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Dementia|Alzheimer'
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 e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "311 Depression", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Depression'
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')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "4539 DVT", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'DVT|Deep vein thrombosis'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('453','4538','4539')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "53011 GERD", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'GERD|gastroesophageal reflux|heartburn|Hiatus hernia'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('530','53010','53011','53081','5368')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "3659 Glaucoma", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'GLAUCOMA|Ocular hypertension|raised IOP|elevated IOP'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('3659')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "5718 Fatty liver", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Fatty liver|NASH|NAFLD|Fatty infiltration'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('5718')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "274 Gout", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Gout|hyperuricaemia|hyperuricemia'
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 e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "332 Parkinsons", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Parkinson'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('332','3321')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "7140 RA", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Rheumatoid arthritis'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('7140')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "6960 Psoriatic arthritis", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Psoriatic arthritis'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('6960')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "7200 Ankylosing Spondylitis", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Ankylosing Spondylitis'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('7200')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "7291 Fibromyalgia", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Fibromyalgia'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('7291')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "6961 Psoriasis", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Psoriasis'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('6961')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "295 Schizophrenia", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'schizophrenia'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('295','2950','29500','29501','29502','29503','29504','29505','2952','2953','2959','2955','2956','2957',
'2958','2959')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "29680 Bipolar disorder", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Bipolar'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('29680','2960','29600','29601','29602','29603','29604','29605','29606','2961','2964','2965','2966','2967',
'2968','2969')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "29570 Schizoaffective", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Schizoaffective'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('29570')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "1629 Ca Lung", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Ca lung|Lung cancer|Lung ca'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('1629','162','1620','1622','1623','1624','1625','1628')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "1539 Ca Colon", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Ca colon|colon ca|colon cancer'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('1539','153','1531','1532','1533','1534','1535','1536','1537','1538')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "1830 Ca Ovary", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Ca ovary|ovary ca|ovary cancer'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('1830')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "193 Ca Thyroid", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Ca thyroid|thyroid ca|thyroid cancer'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('193')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "1869 Ca Testicle", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Ca testicle|testicle ca|testicular cancer'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('1869','186')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "20280 Lymphoma", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Lymphoma'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('20280','2028','20281','20282','20283','20284','20285','20286','20287','20288')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "208 Leukemia", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Leukaemia|Leukemia'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('208','2080','2081','2082','2088','2089')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "7880 Renal colic", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Renal colic'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('7880')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "78039 Seizure disorder", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Seizure|Epilepsy'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('78039','3459')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "725 PMR", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'PMR|Polymyalgia|Temporal arteritis'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('725')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "5790 Celiac disease", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Celiac'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('5790')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "78057 OSA", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'OSA|Obstructive sleep|Sleep apnea|sleep apnoea'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('78057','78051','78053','78603')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "7159 Osteoarthritis", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'osteoarthritis'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('7159','715','7150','71500')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "3382 Chronic pain", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'chronic pain'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('3382','3384','3078','2938')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "V15 Frailty", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'frailty'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('V15')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "303 Alcohol dependence", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Alcohol dependence|Alcohol dependance'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('303')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "304 Drug dependence", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Drug dependence|Drug dependance'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('304')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "V667 Palliative", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Palliative'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('V667')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "2429 Hyperthyroid", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Hyperthyroid|Graves|Thyrotoxicosis|Grave'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('2429','242')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "7880 Renal colic", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Renal colic'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('7880')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "5569 Ulcerative Colitis", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Ulcerative colitis'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('5569','556')
AND e.demographicNo = de.demographic_no)
)
UNION ALL
(SELECT distinct de.last_name AS "Name", de.first_name AS "FName",
CONCAT( "", "5559 Crohns", "" ) AS "Dx Registry",
CONCAT( "", "eChart", "" ) AS "eChart"
FROM eChart e, demographic de
WHERE e.demographicNo = de.demographic_no
AND de.patient_status = 'AC'
AND e.{cpp} RLIKE 'Crohn'
AND de.provider_no = '{provider_no}'
AND de.demographic_no not in
(select dx.demographic_no from dxresearch dx
WHERE dx.dxresearch_code in ('5559','555')
AND e.demographicNo = de.demographic_no)
)
) a
ORDER BY Name,FName;
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 "medicalHistory" AS cpp, 'Medical History' AS typeDisplayName
UNION
SELECT "ongoingConcerns" AS cpp, 'Ongoing Concerns' AS typeDisplayName