Personal tools
You are here: Home / OSCAR Users / EMR and Case Management Resources / Query By Example - Database Searches / Some randomly useful SQL queries for ADMIN -> QUERY BY EXAMPLE
 

Some randomly useful SQL queries for ADMIN -> QUERY BY EXAMPLE

Here's a handful randomly useful SQL queries I wrote for my office to check for random things for research purposes - result can be exported to EXCEL etc.. Just copy and paste each query you need into : ADMIN -> QUERY BY EXAMPLE , and run. Ian PUN MD

Plain Text icon Query By Example August 30 2013.txt — Plain Text, 3 kB (4078 bytes)

File contents

Here's a handful randomly useful SQL queries I wrote for my office to check
for random things for research purposes - result can be exported to EXCEL
etc..  Just copy and paste each query you need into 

ADMIN -> QUERY BY EXAMPLE

and run

Ian PUN MD


/*   See the file upload   This one lists the lab HL7 files that have been
uploaded via Mule  */ 
SELECT id, date_time, filename, provider_no  FROM  fileUploadCheck ORDER BY
id DESC LIMIT 0,1000;


/* get the patients with over HbA1C over 0.06 */
select distinct d.demographic_no , d.last_name, d.first_name, l.type,
l.dataField  , l.dateObserved    from measurements l, demographic d   where
l.demographicNo = d.demographic_no and ( l.type like '%A1C%') and
l.dataField > 0.060 order  by l.dataField desc, l.dateObserved desc,
d.demographic_no desc ;


/* Admin --> Query by Example -- see last 10 echart entries*/
select distinct d.demographic_no , d.last_name, d.first_name, l.note_id,
l.note  from casemgmt_note l, demographic d   where l.demographic_no =
d.demographic_no  ORDER BY l.note_id DESC LIMIT 10;


/* appointment date  get OHN and ver  start_time*/
select  d.demographic_no, d.hin, d.ver,  d.last_name, d.first_name,
l.appointment_date  , l.start_time, l.reason from appointment  l,
demographic d   where l.demographic_no = d.demographic_no and (
l.appointment_date = "2013-08-27" )  
order  by l.start_time desc    
limit 13;

/* select appointment with a reason */
select * from appointment where reason like "%zostavax%";


/* List of Specialists */
select * from professionalSpecialists order by lName, fName


/* Admin --> Query by Example -- see last 10 echart entries*/
select distinct d.demographic_no , d.last_name, d.first_name, l.note_id,
l.note     from casemgmt_note l, demographic d   where l.demographic_no =
d.demographic_no  ORDER BY l.note_id DESC LIMIT 10;


/* ALT over 30 -- you can change to the measureement and value in l.type and
l.dataField */
select distinct d.demographic_no , d.last_name, d.first_name, l.type,
l.dataField  , l.dateObserved    from measurements l, demographic d   where
l.demographicNo = d.demographic_no and ( l.type like 'ALT') and l.dataField
> 1 order  by d.demographic_no desc  , l.dateObserved desc,  l.dataField
desc;



/ * need a measurement defined as Occult blood as type OCBL then set in
measurements then run query - you need to define it first as a measurement*/

update measurements, measurementsExt SET measurements.type = "OCBL" where
measurements.id = measurementsExt.measurement_id and measurementsExt.keyval
= "name" and  measurementsExt.val = "OCCULT BLOOD";
update measurements, measurementsExt SET measurements.type = "OCBL" where
measurements.id = measurementsExt.measurement_id and measurementsExt.keyval
= "name" and  measurementsExt.val = "OCCULT BLOOD #2";
update measurements, measurementsExt SET measurements.type = "OCBL" where
measurements.id = measurementsExt.measurement_id and measurementsExt.keyval
= "name" and  measurementsExt.val = "OCCULT BLOOD #3";
update measurements, measurementsExt SET measurements.type = "OCBL" where
measurements.id = measurementsExt.measurement_id and measurementsExt.keyval
= "name" and  measurementsExt.val = "CCC OCCULT BLOOD 1";
update measurements, measurementsExt SET measurements.type = "OCBL" where
measurements.id = measurementsExt.measurement_id and measurementsExt.keyval
= "name" and  measurementsExt.val = "CCC OCCULT BLOOD 2";
update measurements, measurementsExt SET measurements.type = "OCBL" where
measurements.id = measurementsExt.measurement_id and measurementsExt.keyval
= "name" and  measurementsExt.val = "CCC OCCULT BLOOD 3";

/* Get the OCCULT BLOOD POSITIVE   Patients      OCBL = POSITIVE  */
select distinct d.demographic_no , d.last_name, d.first_name, l.type,
l.dataField  , l.dateObserved    from measurements l, demographic d   where
l.demographicNo = d.demographic_no and ( l.type like 'OCBL') and l.dataField
= "POSITIVE" order  by  l.dateObserved desc,  l.dataField desc;

Document Actions

 

Download button

DOWNLOAD OSCAR FOR TESTING

 

eForms button

DOWNLOAD SHARED E-FORMS


 Customize button

FIND PLUG-INS AND TWEAKS
FOR YOUR OSCAR EMR

 

Subscribe Button

SUBSCRIBE TO DISCUSSION LIST 
(SEE ALL LISTS)

  

Help button

ACCESS THE ONLINE MANUALS
(PAID SUPPORT)