----this is my modified code to target the selection of providers-----------------------------------------------------------------------------------
select provider_no, CONCAT( first_name, ' (', provider_no, ')') from provider where provider_no between 99 and 105 order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
Place query here
---------------------------------------------------------------------------------------------------------------------------------
select distinct CONCAT(d.last_name,', ', d.first_name)
as demographic,
(YEAR(CURRENT_DATE)-YEAR(DATE_FORMAT(CONCAT((d.year_of_birth),'-',(d.month_of_birth),'-',(d.date_of_birth)),'%Y-%m-%d')))-(RIGHT(CURRENT_DATE,5)
---------------------------------------------------------------------------------------------------------------------------------
select distinct CONCAT(d.last_name,', ', d.first_name)
as demographic,
(YEAR(CURRENT_DATE)-YEAR(DATE_FORMAT(CONCAT((d.year_of_birth),'-',(d.month_of_birth),'-',(d.date_of_birth)),'%Y-%m-%d')))-(RIGHT(CURRENT_DATE,5)
---------------------------------------------------------------------------------------------------------------------------------
select distinct CONCAT(d.last_name,', ', d.first_name)
as Patient,
(YEAR(CURRENT_DATE)-YEAR(DATE_FORMAT(CONCAT((d.year_of_birth),'-',(d.month_of_birth),'-',(d.date_of_birth)),'%Y-%m-%d')))-(RIGHT(CURRENT_DATE,5)
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct CONCAT(d.last_name,', ', d.first_name)
as demographic,
(YEAR(CURRENT_DATE)-YEAR(DATE_FORMAT(CONCAT((d.year_of_birth),'-',(d.month_of_birth),'-',(d.date_of_birth)),'%Y-%m-%d')))-(RIGHT(CURRENT_DATE,5)
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct CONCAT(d.last_name,', ', d.first_name)
as demographic,
(YEAR(CURRENT_DATE)-YEAR(DATE_FORMAT(CONCAT((d.year_of_birth),'-',(d.month_of_birth),'-',(d.date_of_birth)),'%Y-%m-%d')))-(RIGHT(CURRENT_DATE,5)
---------------------------------------------------------------------------------------------------------------------------------
select distinct de.last_name "Medication", de.first_name "{drugname}" from drugs dr, demographic de where dr.demographic_no = de.demographic_no and de.patient_status = 'AC' and ( BN like '%{drugname}%') and rx_date > '{rx_date}' order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name,demo.first_name, type,dataField,dateObserved, demo.provider_no from measurements, demographic demo where type ="eGFR" and dataField <> '> 120' and dataField <> 'Not report' and dataField < {gfr} and dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-{mon})*100)+1) and demo.patient_status = 'AC' and demographicNo =demo.demographic_no order by demo.last_name,demo.first_name and dateObserved ;
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name,demo.first_name,dataField ,dateObserved from measurements, demographic demo where type ="A1C" and dataField > {ac} and demo.patient_status = 'AC' and dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-{mon})*100)+1) and demographicNo =demo.demographic_no order by demo.last_name,demo.first_name ;
---------------------------------------------------------------------------------------------------------------------------------
select last_name,first_name,concat(date_of_birth,'-',month_of_birth,'-',year_of_birth) as DOB, dataField from measurements,demographic where measurements.demographicNo = demographic.demographic_no and type = '{type}' and dataField like '{keyvals}%';
select distinct type,typeDisplayName from measurementType where validation =7;
Yes
No
Nonset
---------------------------------------------------------------------------------------------------------------------------------
select distinct CONCAT(d.last_name,', ', d.first_name)
as demographic,
(YEAR(CURRENT_DATE)-YEAR(DATE_FORMAT(CONCAT((d.year_of_birth),'-',(d.month_of_birth),'-',(d.date_of_birth)),'%Y-%m-%d')))-(RIGHT(CURRENT_DATE,5)
---------------------------------------------------------------------------------------------------------------------------------
select de.last_name"Surname",de.first_name"First name", (Year(CURDATE())- de.year_of_birth)"Age", count(drugid)"Number of medications" from drugs dr, demographic de where dr.demographic_no = de.demographic_no and de.patient_status = 'AC' and dr.end_date > CURDATE() and de.year_of_birth < (Year(CURDATE())-69) group by de.last_name, de.first_name;
---------------------------------------------------------------------------------------------------------------------------------
select de.last_name, de.first_name, d.docdesc, d.observationdate from document d, ctl_document cd, demographic de where d.document_no = cd.document_no and de.demographic_no = cd.module_id and de.provider_no = "{provider_no}" and de.year_of_birth {age} and de.sex like "{sex}%" and de.patient_status = "AC" and d.docdesc like "%{document}%" order by de.last_name;
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct CONCAT(d.last_name,', ', d.first_name)
as demographic,
(YEAR(CURRENT_DATE)-YEAR(DATE_FORMAT(CONCAT((d.year_of_birth),'-',(d.month_of_birth),'-',(d.date_of_birth)),'%Y-%m-%d')))-(RIGHT(CURRENT_DATE,5)
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct CONCAT(d.last_name,', ', d.first_name)
as demographic,
(YEAR(CURRENT_DATE)-YEAR(DATE_FORMAT(CONCAT((d.year_of_birth),'-',(d.month_of_birth),'-',(d.date_of_birth)),'%Y-%m-%d')))-(RIGHT(CURRENT_DATE,5)
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name "ICD 9 ", demo.first_name "{code}" from billingmaster bm,demographic demo where bm.dx_code1 = '{code}' and
demo.patient_status = 'AC' and
demo.demographic_no = bm.demographic_no
order by demo.last_name ;
---------------------------------------------------------------------------------------------------------------------------------
select @row := @row + 1 as No, a.notes "Notes",demo.last_name"Surname", demo.first_name "First name", (Year(CURDATE())- demo.year_of_birth)"Age", a.appointment_date "Appointment date", a.reason "Reason" from appointment a, (SELECT @row := 0) r, demographic demo where a.appointment_date >= '{start}' and a.appointment_date <= '{finish}' and a.demographic_no <> '0' and a.reason <> "" and (a.status<> "N" and a.status<> "NS" and a.status<> "C") and demo.patient_status = 'AC' and (a.provider_no = '{provider_no}') and a.demographic_no = demo.demographic_no
order by a.notes,a.appointment_date;
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select a.appointment_date "Date", count(a.appointment_no)"Number of patients" from appointment a, demographic demo where a.appointment_date >= '{start}' and a.appointment_date <= '{finish}' and a.demographic_no <> '0' and a.reason <> "" and (a.status<> "N" and a.status<> "NS" and a.status<> "C") and demo.patient_status = 'AC' and (a.provider_no = '{provider_no}') and a.demographic_no = demo.demographic_no
group by a.appointment_date order by a.appointment_date;
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name"DM", demo.first_name"BILLING" from
dxresearch dx, demographic demo, appointment a where
dx.dxresearch_code = '250' and
demo.patient_status ='AC' and demo.roster_status <> 'Private billings' and
dx.status = 'A' and
a.appointment_date > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1) and
demo.demographic_no = dx.demographic_no and
a.demographic_no = demo.demographic_no and
demo.demographic_no not in
(select distinct demo.demographic_no from billingmaster bm, demographic demo, billing b where
b.billing_no = bm.billing_no and b.demographic_no = demo.demographic_no and
( (bm.billing_code = '14050' or bm.billing_code = '14052') and bm.service_date > ((CURDATE() + 0)- 10000) ) );
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name"CHF", demo.first_name"BILLING" from
dxresearch dx, demographic demo, appointment a where
dx.dxresearch_code = '428' and
demo.patient_status ='AC' and demo.roster_status <> 'Private billings' and
dx.status = 'A' and
a.appointment_date >((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1) and
demo.demographic_no = dx.demographic_no and
demo.demographic_no = a.demographic_no and
demo.demographic_no not in
(select distinct demo.demographic_no from billingmaster bm, demographic demo, billing b where
b.billing_no = bm.billing_no and b.demographic_no = demo.demographic_no and
( (bm.billing_code = '14051' or bm.billing_code = '14052') and bm.service_date > ((CURDATE() + 0)- 10000) ) );
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name"HBP", demo.first_name"BILLING" from
dxresearch dx, demographic demo, appointment a where
dx.dxresearch_code = '401' and dx.start_date < ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1) and
demo.patient_status = 'AC' and demo.roster_status <> 'Private billings' and dx.status = 'A' and
a.appointment_date > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1) and
demo.demographic_no = dx.demographic_no and
demo.demographic_no = a.demographic_no and
dx.demographic_no not in
(select demographic_no from
dxresearch where dxresearch_code = '250' or dxresearch_code = '428' or
dxresearch_code = '7901') and
dx.demographic_no not in
(select distinct bm.demographic_no from billingmaster bm
where bm.billing_code = '14052' and
bm.service_date > ((curdate() + 0)- 10000) and
bm.demographic_no = dx.demographic_no)
order by demo.last_name ;
---------------------------------------------------------------------------------------------------------------------------------
select demo.last_name"From {start}" ,demo.first_name"To {finish}" ,a.appointment_date"Date", a.status"Status",a.provider_no,a.reason from appointment a, demographic demo where a.appointment_date >= '{start}' and a.appointment_date <= '{finish}' and a.demographic_no <>'0' and a.reason <> "" and a.provider_no not in ('405','410','400','910') and a.status not in ("B","BS","BV","C","N","CS","NS")
and demo.demographic_no =a.demographic_no and
a.appointment_no not in
(select a.appointment_no from appointment a, billingmaster bm where a.appointment_date >= '{start}' and a.appointment_date <= '{finish}'
and a.appointment_no = bm.appointment_no);
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name,demo.first_name from billingmaster bm,demographic demo,billing b where
bm.billing_code in ('00108','00128') and
b.billing_date >= "{start_date}" and b.billing_date <= "{finish_date}" and
demo.demographic_no = bm.demographic_no and b.demographic_no = bm.demographic_no
order by demo.last_name ;
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name "ICD 9 ", demo.first_name "{code}" from billingmaster bm,demographic demo where
demo.provider_no = '{provider_no}' and bm.dx_code1 = '{code}' and
demo.patient_status = 'AC' and
demo.demographic_no = bm.demographic_no and
demo.demographic_no not in
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code = '{code}' and dx.demographic_no = demo.demographic_no)
order by demo.last_name ;
select provider_no, CONCAT( last_name,first_name, ' (', provider_no, ')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name"Complex",demo.first_name"Care"
from billing b, billingmaster bm, demographic demo where (b.provider_no = '{provider_no}' and
b.billing_no = bm.billing_no and demo.demographic_no = b.demographic_no and bm.billing_code = '14033' and demo.patient_status = 'AC'and demo.roster_status <> 'Private billings') and b.demographic_no not in(select distinct bm.demographic_no
from billing b, billingmaster bm where b.provider_no = '{provider_no}' and
b.billing_no = bm.billing_no and bm.billing_code = '14033' and
b.billing_date > (((EXTRACT(YEAR from CURDATE()))*10000)+101))
order by b.demographic_name;
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select count(distinct b.demographic_name) "Number of risk assessments billed this year" from billing b, billingmaster bm where b.provider_no = '{provider_no}' and
b.billing_no = bm.billing_no and bm.billing_code = '14066' and
b.billing_date > (((EXTRACT(YEAR from CURDATE()))*10000)+101)
order by b.demographic_name;
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select count(distinct demo.last_name, demo.first_name)'Code{code}' from billingmaster bm,demographic demo where
demo.provider_no = '{provider_no}' and bm.billing_code = '{code}' and
demo.patient_status = 'AC' and
bm.service_date > (((EXTRACT(YEAR from CURDATE()))*10000)+101) and
demo.demographic_no = bm.demographic_no
order by demo.last_name ;
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
------------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name "ICD 9 ", demo.first_name "{code}" from billingmaster bm,demographic demo where
demo.provider_no = '{provider_no}' and bm.dx_code1 = '{code}' and
demo.patient_status = 'AC' and bm.service_date > ((CURDATE() + 0)- 10000) and
demo.demographic_no = bm.demographic_no and
demo.demographic_no not in
(select dx.demographic_no from dxresearch dx where
dx.dxresearch_code = '{code}' and dx.demographic_no = demo.demographic_no)
order by demo.last_name ;
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
--- SECURITY-----------------------------------------------------------------------------------------------------------------------------------
select user_name, b_ExpireSet as `Date Exp Enabled` , date_ExpireDate as `Expire Date`, b_LocalLockSet as `Needs PIN for local` , b_RemoteLockSet as `Needs PIN for remote`, max(log.dateTime) as `last login` from security, log where security.provider_no = log.provider_no and log.content = 'login' group by log.provider_no order by `last login` desc;
---------------------------------------------------------------------------------------------------------------------------------
select p.last_name as 'Viewer of {first} {last}' , p.provider_type as 'Role' , action, ip as 'IP Address' , dateTime from log l , demographic d, provider p where d.last_name = "{last}" and d.first_name like "{first}%" and l.demographic_no = d.demographic_no and p.provider_no = l.provider_no order by dateTime desc limit 0,{limit};
---------------------------------------------------------------------------------------------------------------------------------
select ip,user_name ,log.dateTime from security, log
where security.provider_no = log.provider_no and log.content = 'login' and user_name = '{name}'
and date(log.dateTime) >= '{start}' and date(log.dateTime) <= '{finish}'
order by log.dateTime desc
;
select distinct user_name, concat(user_name,'(',provider_no,')') from security ;
---------------------------------------------------------------------------------------------------------------------------------
select ip as 'IP Address' , action, dateTime ,d.first_name,d.last_name from log l , demographic d, provider p where l.provider_no = '{name}'
and l.demographic_no = d.demographic_no and p.provider_no = l.provider_no
and date(l.dateTime) >= '{start}' and date(l.dateTime) <= '{finish}'
order by dateTime desc;
select distinct provider_no, concat(last_name,'(',provider_no,')') from provider ;
---------------------------------------------------------------------------------------------------------------------------------
select date(log.dateTime) as "-----date-----",time(min(log.dateTime)) as "---start---", time(max(log.dateTime)) as "---finish ---",user_name as "---name---",((time(max(log.dateTime)))-(time(min(log.dateTime)))) as "hours" from security, log
where security.provider_no = log.provider_no and log.content = 'login' and user_name = '{name}'
and date(log.dateTime) >= '{start}' and date(log.dateTime) <= '{finish}'
group by date(log.dateTime)
;
select distinct user_name, concat(user_name,'(',provider_no,')') from security ;
--- FHA Data reports------------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name) "Diabetics" from demographic demo, dxresearch dx,provider p where dx.dxresearch_code in ('250') and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and demo.provider_no in ('100','101','102','103') and dx.status = 'A' and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by p.first_name order by p.provider_no ;
------------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name) "A1C 6M" from measurements, demographic demo,dxresearch dx,provider p where type ="A1C" and dataField > 1 and dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1) and dx.dxresearch_code = '250' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and demo.provider_no in ('100','101','102','103') and dx.status = 'A' and demographicNo =demo.demographic_no and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by p.first_name order by p.provider_no ;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name) "Diabetics with A1C < 7.0 in last 6 months" from measurements, demographic demo,dxresearch dx,provider p where type ="A1C" and dataField < 7 and dataField > 1 and dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1) and dx.dxresearch_code = '250' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and demo.provider_no in ('100','101','102','103') and dx.status = 'A' and demographicNo =demo.demographic_no and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by p.first_name order by p.provider_no ;;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name) "Diabetics with ACR measured in last year" from measurements, demographic demo,dxresearch dx, provider p where type ="ACR" and dataField > 0.1 and dateObserved > ((CURDATE() + 0)- 10000) and dx.dxresearch_code = '250' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and dx.status = 'A' and demo.provider_no in ('100','101','102','103') and demographicNo =demo.demographic_no and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by p.first_name order by p.provider_no ;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name) "Diabetics with ACR >= 2.0 and not on ACE/ARB in last year" from measurements, demographic demo,dxresearch dx, provider p where type ="ACR" and dataField >= 2.0 and dateObserved > ((CURDATE() + 0)- 10000) and dx.dxresearch_code = '250' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and dx.status = 'A' and demo.provider_no in ('100','101','102','103') and demographicNo =demo.demographic_no and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no
and demo.demographic_no not in
(select distinct d.demographic_no from drugs d where
(d.BN like "%pril%" or d.BN like "%prinivil%" or d.BN like "%coversyl%" or d.BN like "%accuretic%" or d.BN like "%altace%" or d.BN like "%cozaar%" or d.BN like "%hyzaar%" or d.BN like "%atacand%" or d.BN like "%avapro%" or d.BN like "%diovan%" or d.BN like "%micardis%")
and d.demographic_no = demo.demographic_no)
group by p.first_name order by p.provider_no ;
;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name) "Diabetics with lipids measured in last 12 months" from measurements, demographic demo,dxresearch dx,provider p where type ="LDL" and dataField > 0.1 and dateObserved > ((CURDATE() + 0)- 10000) and dx.dxresearch_code = '250' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and demo.provider_no in ('100','101','102','103') and dx.status = 'A' and demographicNo =demo.demographic_no and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by p.first_name order by p.provider_no ;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name) "Diabetics with LDL < 2.0 in last 12 months" from measurements, demographic demo,dxresearch dx,provider p where type ="LDL" and dataField > 0.1 and dataField < 2 and dateObserved > ((CURDATE() + 0)- 10000) and dx.dxresearch_code = '250' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and demo.provider_no in ('100','101','102','103') and dx.status = 'A' and demographicNo =demo.demographic_no and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by p.first_name order by p.provider_no ;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name)"Total number of hypertensive patients" from demographic demo, dxresearch dx,provider p where dx.dxresearch_code = '401' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and demo.provider_no in ('100','101','102','103') and dx.status = 'A' and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name) "Hypertensive patients with blood pressure measurement in last 6 months" from measurements, demographic demo,dxresearch dx,provider p where type ="BP" and dataField > 50/10 and dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1) and dx.dxresearch_code = '401' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and dx.status = 'A' and demo.provider_no in ('100','101','102','103') and demographicNo =demo.demographic_no and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name) "Hypertensive patients with blood pressure < 140/90 in last 6 months" from measurements, demographic demo,dxresearch dx,provider p where type ="BP"
and dataField < 141 and (dataField like '%/90' or dataField like '%/8%' or dataField like '%/7%'or dataField like '%/6%'or dataField like '%/5%' or dataField like '%/4%'or dataField like '%/3%'or dataField like '%/2%' or dataField like '%/1%'or dataField like '%/0%') and dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1) and dx.dxresearch_code = '401' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and dx.status = 'A' and demo.provider_no in ('100','101','102','103') and demographicNo =demo.demographic_no and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name) "Patients with CHF" from demographic demo, dxresearch dx,provider p where dx.dxresearch_code = '428' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and dx.status = 'A'
and demo.provider_no in ('100','101','102','103') and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name)"CHF patients on ACE or ARB" from drugs d, demographic demo, dxresearch dx,provider p where (d.BN like "%pril%" or d.BN like "%prinivil%" or d.BN like "%coversyl%" or d.BN like "%accuretic%" or d.BN like "%altace%" or d.BN like "%cozaar%" or d.BN like "%hyzaar%" or d.BN like "%atacand%" or d.BN like "%avapro%" or d.BN like "%diovan%" or d.BN like "%micardis%") and d.demographic_no = demo.demographic_no and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and dx.status = 'A' and demo.provider_no in ('100','101','102','103') and dx.dxresearch_code = '428' and d.end_date > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-1)*100)+1) group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name) "CHF patients on B Blockers" from drugs d, demographic demo, dxresearch dx,provider p where (d.BN like "%olol%" or d.BN like "%atenol%" or d.BN like "%sotalol%" or d.BN like "%lopressor%" or d.BN like "%tenoretic%") and d.demographic_no = demo.demographic_no and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and dx.status = 'A' and demo.provider_no in ('100','101','102','103') and dx.dxresearch_code = '428' and d.end_date > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-1)*100)+1) group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name)"CHF on B Blocker and ACE or ARB" from dxresearch
dx, demographic demo, drugs d, provider p where
(d.BN like "%pril%" or d.BN like "%prinivil%" or d.BN like "%coversyl%" or d.BN like
"%accuretic%" or d.BN like "%altace%" or d.BN like "%cozaar%" or d.BN
like "%hyzaar%" or d.BN like "%atacand%" or d.BN like "%avapro%" or d.BN like "%diovan%" or
d.BN like "%micardis%")
and dx.dxresearch_code = '428' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and demo.provider_no in ('100','101','102','103') and dx.status = 'A' and
d.end_date > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-1)*100)+1) and d.demographic_no = demo.demographic_no
and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no
and dx.demographic_no in
(select distinct d.demographic_no from drugs d, dxresearch dx where
(d.BN like "%olol%" or d.BN like "%lopressor%" or d.BN like "%sotalol%"
or d.BN like "%atenol%" or d.BN like "%tenoretic%")
and dx.dxresearch_code = '428' and d.end_date > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-1)*100)+1) and
d.demographic_no = dx.demographic_no )
group by p.first_name order by p.provider_no ;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name,demo.first_name)"CHF not on B Blocker or ACE or ARB" from
dxresearch dx, demographic demo,provider p where
dx.dxresearch_code = '428' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and dx.status = 'A' and demo.provider_no in ('100','101','102','103') and dx.demographic_no = demo.demographic_no and dx.demographic_no and p.provider_no = demo.provider_no
not in
(select d.demographic_no from drugs d, dxresearch dx where
(d.BN like "%olol%" or d.BN like "%atenol%" or d.BN like "%sotalol%" or d.BN like "%lopressor%" or d.BN like "%tenoretic%" or d.BN like "%pril%" or d.BN like "%prinivil%" or d.BN like "%coversyl%" or d.BN like "%accuretic%" or d.BN like "%altace%" or d.BN like "%cozaar%" or d.BN like "%hyzaar%" or d.BN like "%atacand%" or d.BN like "%avapro%" or d.BN like "%diovan%" or d.BN like "%micardis%") and
dx.dxresearch_code = '428' and d.end_date > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-1)*100)+1) and d.demographic_no = dx.demographic_no) group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name,demo.first_name)"Total number of patients with CKD" from measurements, demographic demo,provider p where type ="eGFR" and dataField <> '> 120' and dataField <> 'Not report' and dataField < 61 and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and demo.provider_no in ('100','101','102','103') and demographicNo =demo.demographic_no and p.provider_no = demo.provider_no group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name)"CKD patients on ACE or ARB" from drugs d, demographic demo, dxresearch dx, measurements m,provider p where (d.BN like "%pril%" or d.BN like "%prinivil%" or d.BN like "%coversyl%" or d.BN like "%accuretic%" or d.BN like "%altace%" or d.BN like "%cozaar%" or d.BN like "%hyzaar%" or d.BN like "%atacand%" or d.BN like "%avapro%" or d.BN like "%diovan%" or d.BN like "%micardis%") and d.demographic_no = demo.demographic_no and dx.demographic_no = demo.demographic_no and m.demographicNo =demo.demographic_no and p.provider_no = demo.provider_no and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and dx.status = 'A' and demo.provider_no in ('100','101','102','103') and type ="eGFR" and dataField <> '> 120' and dataField <> 'Not report' and dataField < 61 and d.end_date > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-1)*100)+1) group by p.first_name order by p.provider_no ;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name)"Complex Care" from
billingmaster bm, demographic demo,provider p where
demo.demographic_no = bm.demographic_no and p.provider_no = demo.provider_no and
bm.billing_code = '14033' and demo.patient_status = 'AC' and demo.roster_status not in ('MOVED','Private billings') and demo.provider_no in ('100','101','102','103') group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name)"Active patients" from demographic demo, eChart e, provider p where
demo.patient_status = 'AC' and e.encounter like "%igned%" and demo.provider_no in ('100','101','102','103','104') and
demo.demographic_no = e.demographicNo and p.provider_no = demo.provider_no
group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name, demo.first_name)"Distinct patients seen in 1 yr" from demographic demo, eChart e,provider p where
demo.patient_status = 'AC' and e.encounter like "%signed%" and e.timeStamp > ((CURDATE() + 0)- 10000)
and demo.provider_no in ('100','101','102','103') and
demo.demographic_no = e.demographicNo and p.provider_no = demo.provider_no
group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", sum(bm.billing_unit) "No of patient hospital days from {start} to {finish}" from billingmaster bm, demographic demo, provider p
where demo.demographic_no = bm.demographic_no and p.provider_no = demo.provider_no and demo.provider_no in ('100','101','102','103') and
bm.billingmaster_no in
(select distinct bm.billingmaster_no from billing b, billingmaster bm, demographic demo where bm.billing_code in ('00109','13008','13108','00108','00128','13028','00127','13127') and b.billing_no = bm.billing_no
and demo.demographic_no = bm.demographic_no and demo.patient_status in ('AC','DE')
and b.billing_date >= '{start}' and b.billing_date <= '{finish}')
group by p.first_name order by p.provider_no;
---------------------------------------------------------------------------------------------------------------------------------
select p.first_name"Provider", count(distinct demo.last_name,demo.first_name) "No of distinct patients admitted from {start} to {finish}" from billingmaster bm,demographic demo, provider p
where demo.demographic_no = bm.demographic_no and p.provider_no = demo.provider_no and demo.provider_no in ('100','101','102','103') and
bm.billingmaster_no in
(select distinct bm.billingmaster_no from billing b, billingmaster bm, demographic demo where bm.billing_code in ('00109','13008','13108','00108','00128','13028') and b.billing_no = bm.billing_no
and demo.demographic_no = bm.demographic_no and demo.patient_status in ('AC','DE')
and b.billing_date >= '{start}' and b.billing_date <= '{finish}')
group by p.first_name order by p.provider_no ;
---------------------------------------------------------------------------------------------------------------------------------
select distinct CONCAT(d.last_name,', ', d.first_name)
as demographic,
(YEAR(CURRENT_DATE)-YEAR(DATE_FORMAT(CONCAT((d.year_of_birth),'-',(d.month_of_birth),'-',(d.date_of_birth)),'%Y-%m-%d')))-(RIGHT(CURRENT_DATE,5)
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct CONCAT(d.last_name,', ', d.first_name)
as demographic,
(YEAR(CURRENT_DATE)-YEAR(DATE_FORMAT(CONCAT((d.year_of_birth),'-',(d.month_of_birth),'-',(d.date_of_birth)),'%Y-%m-%d')))-(RIGHT(CURRENT_DATE,5)
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select count(id)"Number of Pneumovax immunizations given from {start} to {finish}" from preventions where prevention_date >= '{start}' and prevention_date <= '{finish}' and prevention_type = 'Pneumovax';
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name,demo.first_name"Over 65yr not pneumovax immunized" from preventions p, demographic demo,
eChart e where demo.year_of_birth <= (Year(CURDATE())-65) and demo.patient_status = 'AC'
and e.encounter like "%signed%" and
demo.demographic_no = e.demographicNo and p.demographic_no= e.demographicNo
and demo.demographic_no = p.demographic_no
and demo.demographic_no not in
(select p.demographic_no from preventions p where p.prevention_type = 'Pneumovax')
order by demo.last_name;
--- End of Life Program tools-------------------------------------------------------------------------------------------------------------
select distinct CONCAT(d.last_name,', ', d.first_name)
as Patient,
(YEAR(CURRENT_DATE)-YEAR(DATE_FORMAT(CONCAT((d.year_of_birth),'-',(d.month_of_birth),'-',(d.date_of_birth)),'%Y-%m-%d')))-(RIGHT(CURRENT_DATE,5)
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name"Complex",demo.first_name"Care",demo.phone "Home Phone",demo.phone2"Alternate Phone",demo.email"Email"
from billing b, billingmaster bm, demographic demo where (b.provider_no = '{provider_no}' and
b.billing_no = bm.billing_no and demo.demographic_no = b.demographic_no and bm.billing_code = '14033' and b.billing_date >((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-17)*100)+1) and demo.patient_status = 'AC'and demo.roster_status <> 'Private billings')
order by demo.last_name;
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name"Last name",demo.first_name"First name",demo.address, demo.city,demo.postal
from billing b, billingmaster bm, demographic demo where (b.provider_no = '{provider_no}' and
b.billing_no = bm.billing_no and demo.demographic_no = b.demographic_no and bm.billing_code = '14033' and b.billing_date >((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-17)*100)+1) and demo.patient_status = 'AC'and demo.roster_status <> 'Private billings')
order by demo.last_name;
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------
select distinct demo.last_name"Complex",demo.first_name"Care",demo.phone "Home Phone",demo.phone2"Alternate Phone",demo.email"Email"
from billing b, billingmaster bm, demographic demo where (b.provider_no = '{provider_no}' and
b.billing_no = bm.billing_no and demo.demographic_no = b.demographic_no and bm.billing_code = '14053' and b.billing_date >((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-11)*100)+1) and demo.patient_status = 'AC'and demo.roster_status <> 'Private billings')
order by demo.last_name;
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
------Maternity tool------------------------------------------------------------------------------------------------------------
select distinct MONTHNAME(dataField)"MONTH",YEAR(dataField)"DUE------", demo.first_name "Patient", demo.last_name "Name", dataField "EDD" from measurements, demographic demo where type ="EDD" and dataField >(select subdate(curdate(),interval 14 day)) and demo.patient_status = 'AC' and demo.provider_no = {provider_no} and demographicNo =demo.demographic_no
and dateEntered in
(select max(dateEntered) from measurements, demographic demo where type ="EDD" and dataField >(select subdate(curdate(),interval 14 day)) and demo.patient_status = 'AC' and demo.provider_no = {provider_no} and demographicNo =demo.demographic_no group by demo.last_name,demo.first_name)
order by dataField
;
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider order by last_name;
---------------------------------------------------------------------------------------------------------------------------------