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) http://guidelines.diabetes.ca/bloodglucoselowering/a1ctarget 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
