( #14050 select DISTINCT "14050" as "CDM Due", (select MAX(update_date) from dxresearch where demo.demographic_no=dxresearch.demographic_no and dxresearch_code='250' and status='A') as "Dx Date", CONCAT( "", demo.demographic_no, "" ) AS "DemoLink" , CONCAT( "", demo.last_name, ', ', demo.first_name, "" ) AS "eChart" , CONCAT( " invoices" ) AS "Invoice List" , demo.phone AS 'PATIENT_Phone', (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="BP" order by m.dateObserved desc limit 1) as "Last BP", (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="A1C" order by m.dateObserved desc limit 1) as "Last A1C", (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="EGFR" order by m.dateObserved desc limit 1) as "Last eGFR", (select COUNT(*) from appointment a where a.demographic_no=demo.demographic_no and a.status LIKE '%B%' and a.appointment_date > ((CURDATE() + 0) - 10000) and a.appointment_date < (CURDATE() + 0)) as "Visits in last year", (select DATE_FORMAT(MAX(service_date), "%e %b %Y") from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14050, 14052)) as "CDM last billed date", (select a.appointment_date from appointment a where a.demographic_no=demo.demographic_no and a.appointment_date > (CURDATE() + 0) and a.status not in ('C', 'N') order by a.appointment_date desc limit 1) as "Upcoming Appt" from demographic demo where demo.demographic_no IN (select demographic_no from dxresearch where dxresearch_code='250' and status='A' #and start_date < (NOW() - INTERVAL 1 YEAR) ) and # pt registered as having CHF demo.provider_no in ({provider}) and demo.patient_status ='AC' and demo.roster_status <> 'Private billings' and (select COUNT(*) from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14050, 14052) and bm.service_date > ((CURDATE() + 0) - 10000)) < 1 # bonus not billed last year and (select COUNT(*) from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14063)) < 1 # not palliative and (select COUNT(*) from appointment a where a.demographic_no=demo.demographic_no and a.appointment_date > ((CURDATE() + 0) - 10000)) > 1 # two appointments in the last year and (select COUNT(*) from billingmaster bm1, provider p1 where bm1.demographic_no=demo.demographic_no and bm1.payee_no=p1.billing_no and p1.provider_no=demo.provider_no and bm1.service_date < ((CURDATE() + 0) - 10000)) > 0 # billed more than once in the last year order by demo.last_name ) #14050 UNION ( #14051 select DISTINCT "14051" as "CDM Due", (select MAX(update_date) from dxresearch where demo.demographic_no=dxresearch.demographic_no and dxresearch_code='428' and status='A') as "Dx Date", CONCAT( "", demo.demographic_no, "" ) AS "DemoLink" , CONCAT( "", demo.last_name, ', ', demo.first_name, "" ) AS "eChart" , CONCAT( " invoices" ) AS "Invoice List" , demo.phone AS 'PATIENT_Phone', (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="BP" order by m.dateObserved desc limit 1) as "Last BP", (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="A1C" order by m.dateObserved desc limit 1) as "Last A1C", (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="EGFR" order by m.dateObserved desc limit 1) as "Last eGFR", (select COUNT(*) from appointment a where a.demographic_no=demo.demographic_no and a.status LIKE '%B%' and a.appointment_date > ((CURDATE() + 0) - 10000) and a.appointment_date < (CURDATE() + 0)) as "Visits in last year", (select DATE_FORMAT(MAX(service_date), "%e %b %Y") from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14051, 14052)) as "CDM last billed date", (select a.appointment_date from appointment a where a.demographic_no=demo.demographic_no and a.appointment_date > (CURDATE() + 0) and a.status not in ('C', 'N') order by a.appointment_date desc limit 1) as "Upcoming Appt" from demographic demo where demo.demographic_no IN (select demographic_no from dxresearch where dxresearch_code='428' and status='A') and # pt registered as having CHF demo.provider_no in ({provider}) and demo.patient_status ='AC' and demo.roster_status <> 'Private billings' and (select COUNT(*) from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14051,14052) and bm.service_date > ((CURDATE() + 0) - 10000)) < 1 # bonus not billed last year and (select COUNT(*) from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14063)) < 1 # not palliative and (select COUNT(*) from appointment a where a.demographic_no=demo.demographic_no and a.appointment_date > ((CURDATE() + 0) - 10000)) > 1 # two appointments in the last year and (select COUNT(*) from billingmaster bm1, provider p1 where bm1.demographic_no=demo.demographic_no and bm1.payee_no=p1.billing_no and p1.provider_no=demo.provider_no and bm1.service_date < ((CURDATE() + 0) - 10000)) > 0 # billed more than once in the last year order by demo.last_name ) #14051 UNION ( #14052 select DISTINCT "14052" as "CDM Due", (select MAX(update_date) from dxresearch where demo.demographic_no=dxresearch.demographic_no and dxresearch_code='401' and status='A') as "Dx Date", CONCAT( "", demo.demographic_no, "" ) AS "DemoLink" , CONCAT( "", demo.last_name, ', ', demo.first_name, "" ) AS "eChart" , CONCAT( " invoices" ) AS "Invoice List" , demo.phone AS 'PATIENT_Phone', (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="BP" order by m.dateObserved desc limit 1) as "Last BP", (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="A1C" order by m.dateObserved desc limit 1) as "Last A1C", (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="EGFR" order by m.dateObserved desc limit 1) as "Last eGFR", (select COUNT(*) from appointment a where a.demographic_no=demo.demographic_no and a.status LIKE '%B%' and a.appointment_date > ((CURDATE() + 0) - 10000) and a.appointment_date < (CURDATE() + 0)) as "Visits in last year", (select DATE_FORMAT(MAX(service_date), "%e %b %Y") from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in (14052)) as "CDM last billed date", (select a.appointment_date from appointment a where a.demographic_no=demo.demographic_no and a.appointment_date > (CURDATE() + 0) and a.status not in ('C', 'N') order by a.appointment_date desc limit 1) as "Upcoming Appt" from demographic demo where demo.demographic_no IN (select demographic_no from dxresearch where dxresearch_code='401' and status='A') and # pt registered as having HTN demo.demographic_no NOT IN (select demographic_no from dxresearch where dxresearch_code IN (250,428) and status='A') and # pt not registered as having DM2, CHF demo.provider_no in ({provider}) and demo.patient_status ='AC' and demo.roster_status <> 'Private billings' and (select COUNT(*) from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14052) and bm.service_date > ((CURDATE() + 0) - 10000)) < 1 # bonus not billed last year and (select COUNT(*) from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14063)) < 1 # not palliative and (select COUNT(*) from appointment a where a.demographic_no=demo.demographic_no and a.appointment_date > ((CURDATE() + 0) - 10000)) > 1 # two appointments in the last year and (select COUNT(*) from billingmaster bm1, provider p1 where bm1.demographic_no=demo.demographic_no and bm1.payee_no=p1.billing_no and p1.provider_no IN ({provider}) and bm1.service_date < ((CURDATE() + 0) - 10000)) > 0 # billed more than once in the last year order by demo.last_name ) #14052 UNION ( # 14053 select DISTINCT "14053" as "CDM Due", (select MAX(update_date) from dxresearch where demo.demographic_no=dxresearch.demographic_no and dxresearch_code='496' or dxresearch_code='491' or dxresearch_code='492' or dxresearch_code='494' and status='A') as "Dx Date", CONCAT( "", demo.demographic_no, "" ) AS "DemoLink" , CONCAT( "", demo.last_name, ', ', demo.first_name, "" ) AS "eChart" , CONCAT( " invoices" ) AS "Invoice List" , demo.phone AS 'PATIENT_Phone', (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="BP" order by m.dateObserved desc limit 1) as "Last BP", (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="A1C" order by m.dateObserved desc limit 1) as "Last A1C", (select CONCAT(dataField, " (", date_format(m.dateObserved, "%Y %b %e"),")") from measurements m where m.demographicNo=demo.demographic_no and m.type="EGFR" order by m.dateObserved desc limit 1) as "Last eGFR", (select COUNT(*) from appointment a where a.demographic_no=demo.demographic_no and a.status LIKE '%B%' and a.appointment_date > ((CURDATE() + 0) - 10000) and a.appointment_date < (CURDATE() + 0)) as "Visits in last year", (select DATE_FORMAT(MAX(service_date), "%e %b %Y") from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14053)) as "CDM last billed date", (select a.appointment_date from appointment a where a.demographic_no=demo.demographic_no and a.appointment_date > (CURDATE() + 0) and a.status not in ('C', 'N') order by a.appointment_date desc limit 1) as "Upcoming Appt" from demographic demo where demo.demographic_no IN (select demographic_no from dxresearch where dxresearch_code='496' or dxresearch_code='491' or dxresearch_code='492' or dxresearch_code='494' and status='A') and # pt registered as having CHF demo.provider_no in ({provider}) and demo.patient_status ='AC' and demo.roster_status <> 'Private billings' and (select COUNT(*) from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14053) and bm.service_date > ((CURDATE() + 0) - 10000)) < 1 # bonus not billed last year and (select COUNT(*) from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14063)) < 1 # not palliative and (select COUNT(*) from appointment a where a.demographic_no=demo.demographic_no and a.appointment_date > ((CURDATE() + 0) - 10000)) > 1 # two appointments in the last year and (select COUNT(*) from billingmaster bm1, provider p1 where bm1.demographic_no=demo.demographic_no and bm1.payee_no=p1.billing_no and p1.provider_no=demo.provider_no and bm1.service_date < ((CURDATE() + 0) - 10000)) > 0 # billed more than once in the last year order by demo.last_name ) #14053 select provider_no, CONCAT(last_name) from provider WHERE status='1' AND provider_type='doctor' AND ohip_no>1 ORDER BY last_name;