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
Patients with Multiple Drug Allergies MDA - count number
Search for Patients with Multiple Allergies and count the number of allergies listed.
Patients with Multiple Drug Allergies MDA IN ADDITION TO PENICILLIN QBE
Search for Patients with Multiple Drug Allergies MDA IN ADDITION TO PENICILLIN Query By Example
Search for patients who have been presribed PENICILLIN that have a PENICILLIN allergy listed
Search By Description for Penicillin prescription and match to patients with a penicillin allergy listed - Query By Example. As Peter HC says, probably best to confirm with the patient that they took the prescription prior to archiving the allergy.
Sign off forgotten notes Query By Example
Signing old forgotten notes with the default provider

Document Actions


Download button



eForms button


 Customize button



Subscribe Button



Help button