( #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;