Personal tools
You are here: Home / OSCAR Users / EMR and Case Management Resources / Query By Example - Database Searches

Query By Example - Database Searches

Query By Examples: Examples contributed by OSCAR users to query the database.
Select patients with HbA1C > 0.07
Search for patients based on the diagnostic code used at billing
Contributed by Jay Gallagher, Patti Rodger-Kirkpatrick
Find all registered children under a certain age
Contributed by Peter Everett
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
rejected claim patients V2
Update - I improved my SQL with a clickable link now directly to the billing claim itself --- Ian PUN MD ---As you know, correcting error code report (version codes rejection etc ) is quite tedious in OSCAR, The error report generated is not useful. You just get a bunch of OHIP numbers and you have to manually go to the patient's chart (retyping and searching OHIP numbers ) to correct the OHIP claim. I wrote this this SQL Query by Example query (Paste it in ADMIN -> Query by Example box and click QUERY). It will generate a clickable list of your rejected claim patients. Unfortunately, I can't spoof it to click to the BILLING CORRECTION screen.
Oscar Usage Query By Example
Provided by Ian Pun - Say your provider number OSCAR is 999998 (change it to your actual provider number) Copy and paste this SQL query into ADMIN->Query by Examples SELECT date(dateTime) as date , COUNT(1) AS count FROM log where provider_no = 999998 and action = "read" GROUP BY date , provider_no; will return a list of dates with number of charts read per day COPY and PASTE to Microsoft EXCEL to chart. Modify the sql to limit by date, action (log in, add , read, log out, failed) etc, . Maybe someone on the list can convert it to a report by template. Now you have evidence you are an OSCAR addict with hundreds of transactions a day. Ian PUN
Patient appointments in the last year
Created By Ian Pun - Who is the patient with the most appointments over the last year? Find out with my SQL QUERY! Copy and paste this SQL query into ADMIN->Query by Examples then click on QUERY
Commonest Allergies
Created By Peter HC. Searches allergy database and gives you totals.
Billings over the last year, discounted 15% for rostered patients
Created by Ian Pun - I made this SQL query to count the amount billed per patient over the last year and discounts it accordingly to 15% if patient is rostered. HOWEVER IT DOES NOT TAKE INTO ACCOUNT out of the basket billings!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Enjoy! Ian PUN MD
Query for Morphine Equivalents per Day
Created by Peter Hutten-Czapski - The following rather lengthy script covers codeine oxycodone hyrdomorphone and morphine Note this assumes that you are only giving one script for one type of narcotic at a time and you properly put in duration. If you don't total MED will be higher.
query by example to find patients whose most recent A1C is > 7%
From Ian Pun: I wrote this query by example to find patients whose most recent A1C is > 7% so I can bring them put them more in line with CDA guidelines of <7% (with more aggressive therapy in certain individuals and more lax with frail elderly or terminal patients) Ian

Document Actions


Download button



eForms button


 Customize button



Subscribe Button



Help button