( select first_name, last_name, TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) as "Age", CONCAT( "/", demographic_no, "/" ) AS "DemoLink", (select MAX(a.appointment_date) from appointment a where a.demographic_no=demo.demographic_no and a.status LIKE '%B%') as "Last appointment", #(select IF((select MAX(a1. appointment_date) from appointment a1 where a1.demographic_no=demo.demographic_no and a1.status LIKE #'%B%')>DATE("2020-03-15"), "*", "")) as " ", (select MIN(a.appointment_date) from appointment a where a.demographic_no=demo.demographic_no and a.status NOT LIKE '%C%' and a.status NOT LIKE '%X%' AND a.appointment_date>NOW()) as "Next appointment", demo.phone as "Phone", (select value FROM demographicExt WHERE demographic_no=demo.demographic_no AND key_val="demo_cell" limit 1) as "Cell", demo.email as "Email" FROM demographic demo WHERE demo.patient_status='AC' AND ( ("{provider}" = "ALL") OR ("{provider}" = demo.provider_no) ) # age check: AND (case when '{query}' = 'q1' then ( # COVID – Pts not seen: #- Active #- Age 49 to 79 #- Without a visit/appointment since March 15, 2020 (select MAX(a1.appointment_date) from appointment a1 where a1.demographic_no=demo.demographic_no and a1.status LIKE '%B%')= 49 AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 79 ) when '{query}' = 'q2' then ( #COVID – Pts 90 and older: #- Active #- Age 90 and older #TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 90 demo.year_of_birth <= 1931 # MOH definition of 90 ) when '{query}' = 'q7' then ( #COVID – Pts 85-89: #- Active #- Age 85-89 #TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 85 #AND #TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 89 demo.year_of_birth <= 1936 and demo.year_of_birth > 1931 # MOH definition of 85-89 ) when '{query}' = 'q8' then ( #COVID – Pts 80-84 #- Active #- Age 80-84 #TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 80# #AND #TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 84 demo.year_of_birth <= 1941 and demo.year_of_birth > 1936 # MOH definition of 80-84 ) when '{query}' = 'q3' then ( #COVID – Chronic Disease Pts: #- Active #- Age 16-79 #- Any of the following diagnoses billed in the last 2 years: TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 16 AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 79 AND( (exists (select dxresearch_no from dxresearch where dxresearch.demographic_no = demo.demographic_no and dxresearch.status='A' and dxresearch.dxresearch_code in ("250","290","331","332","340","401","413","414","427","428","433","434","435","436","440","443","490","491","492","493","494","496","519","573","585","V15"))) OR (exists (select billingmaster_no from billingmaster where billingmaster.demographic_no = demo.demographic_no and billingmaster.billingstatus IN ("B", "S", "O") AND TIMESTAMPDIFF(YEAR, DATE(billingmaster.service_date), NOW()) <=2 AND billingmaster.dx_code1 in ("250","290","331","332","340","401","413","414","427","428","433","434","435","436","440","443","490","491","492","493","494","496","519","573","585","V15"))) ) ) when '{query}' = 'q4' then ( #COVID – Cancer Pts: #- Active #- Age 16-79 #- Any of the following diagnoses billed in the last 2 years: TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 16 AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 79 AND( (exists (select dxresearch_no from dxresearch where dxresearch.demographic_no = demo.demographic_no and dxresearch.status='A' and dxresearch.dxresearch_code in (150,153,154,155,157,170,172,174,179,180,183,185,188,191,193,199,202,203,204,205,207))) OR (exists (select billingmaster_no from billingmaster where billingmaster.demographic_no = demo.demographic_no and billingmaster.billingstatus IN ("B", "S", "O") AND TIMESTAMPDIFF(YEAR, DATE(billingmaster.service_date), NOW()) <=2 AND billingmaster.dx_code1 in (150,153,154,155,157,170,172,174,179,180,183,185,188,191,193,199,202,203,204,205,207))) ) ) when '{query}' = 'q5' then ( #COVID – Immunocompromised: #- Active #- Age 16-79 #- Any of the following diagnoses billed in the last 2 years: TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 16 AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 79 AND( (exists (select dxresearch_no from dxresearch where dxresearch.demographic_no = demo.demographic_no and dxresearch.status='A' and dxresearch.dxresearch_code in (695,696,710,714,725))) OR (exists (select billingmaster_no from billingmaster where billingmaster.demographic_no = demo.demographic_no and billingmaster.billingstatus IN ("B", "S", "O") AND TIMESTAMPDIFF(YEAR, DATE(billingmaster.service_date), NOW()) <=2 AND billingmaster.dx_code1 in (695,696,710,714,725))) ) ) when '{query}' = 'q6' then ( #COVID – Mental Health and Substance Use: #- Active #- Age 16-79 #- Any of the following diagnoses billed in the last 2 years: TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 16 AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 79 AND( (exists (select dxresearch_no from dxresearch where dxresearch.demographic_no = demo.demographic_no and dxresearch.status='A' and dxresearch.dxresearch_code in ("296","300","303","304","305","311","50B"))) OR (exists (select billingmaster_no from billingmaster where billingmaster.demographic_no = demo.demographic_no and billingmaster.billingstatus IN ("B", "S", "O") AND TIMESTAMPDIFF(YEAR, DATE(billingmaster.service_date), NOW()) <=2 AND billingmaster.dx_code1 in ("295","296","300","303","304","305","311","50B"))) ) ) end ) order by (case when '{sort}' = 'last_name' then demo.last_name end) asc, (case when '{sort}' = 'age' then DATE(CONCAT(demo.year_of_birth, "-", demo.month_of_birth, "-", demo.date_of_birth)) end) asc ) UNION (SELECT "ICD-9 codes checked:", "", "", "", "", "", "", "", (case when '{query}' = 'q1' then ("") when '{query}' = 'q2' then ("") when '{query}' = 'q3' then ("250,290,331,332,340,401,413,414,427,428,433,434,435,436,440,443,490,491,492,493,494,496,519,573,585,V15") when '{query}' = 'q4' then ("150,153,154,155,157,170,172,174,179,180,183,185,188,191,193,199,202,203,204,205,207") when '{query}' = 'q5' then ("695,696,710,714,725") when '{query}' = 'q6' then ("295,296,300,303,304,305,311,50B") end) ) ( SELECT provider_no, CONCAT(first_name,' ',last_name) AS name FROM provider where provider_type='doctor' and status=1 and ohip_no!='') UNION ( SELECT 'ALL', ' All providers' ) ORDER BY name ; Last Name Age Pts not seen (49-79yo, no appt since March 15, 2020) Pts 90 and older Pts 85-89 Pts 80-84 Chronic disease patients Cancer patients Immunocompromised Mental Health and Substance Use