We are currently updating the OP Help Center content for the release of OP 14.19 or "OP 19". OP 19 is a member of the certified OP 14 family of products (official version is 14.19.1), which you may see in your software (such as in Help > About) and in the Help Center tabs labeled 14.19. You may also notice that the version number in content and videos may not match the version of your software, and some procedural content may not match the workflow in your software. We appreciate your patience and understanding as we make these enhancements.

Clip-and-Save SQL Library

Overview

The Clip-and-Save SQL Library below provides a list of SQL report samples that you can copy and save/run in your OP Database Viewer. Any SQL reports that are not included in this library may be considered customized SQL reports.  Customized SQL reports may need to be created/altered to meet specific needs, which could then include an additional charge.  For questions on customized SQL reports, please contact Support.

Note: There is only one level of security in Database Viewer. That level of security is permission access. A staff member who has access to the Database Viewer can theoretically run any report that contains any data. This includes data concerning medications, diagnoses, lab results, and other sensitive areas of a patient's chart. Therefore, your practice should limit Database Viewer/SQL access only to individuals at your practice who would normally have access to this information. You will need to have the proper access to utilize your OP Database Viewer in order to utilize the SQL codes in this library. See Providing Access in OP Database Viewer Basics for more information.


Warning: Please use all the features of the OP software before applying any of the clip-and-save SQLs in your OP Database Viewer. If you are not using all the features in the OP software, the resulting reports may not accurate or function as intended. For example, if you have not gone live with all the elements in the OP software or use a different scheduling or practice management system, the reports created by the clip-and-save SQLs may not function as intended after they are run in the OP Database Viewer.

For brief instructions and an animated image on how to quickly clip and save SQL code, click here.

Note: The Database Viewer accesses the same database as the OP software.  If you run a query that filters a lot of data, your database could become sluggish and affect your users.  It is recommended to run queries during hours when users are not running the OP software. 

When applying an SQL Query from the Clip-and-Save SQL Library, you may need a basic understanding on how to create an SQL query.  To learn how to create an SQL query, see our Clip-and-Save SQL: Quickstart! 

Note: The Database Viewer is a powerful tool for aggregating data into a single file, which can prove to be a double-edged sword if placed in the wrong hands. This is even more relevant if your practice does not follow other best practices. For example, a practice may store unencrypted credit card numbers in the Patient Register. That is an ill-advised risk. The reason it is a risk is because the Database Viewer makes it very easy to download all of those credit card numbers into a single Excel file if using the proper SQL code. So, please only grant individuals at your practice the use of this powerful tool if they would normally have access to that sensitive information.


Note: If you are looking for Meaningful Use (MU) related SQLs for determining Medicaid volume, click here.

Clip-and-Save SQL Library

To quickly find the form you want in this table:

  • Filter: Enter specific key words (or a character string) in the filter bar.  As you enter characters in the filter bar, the rows not containing those characters will become hidden.  The visible rows display based upon the characters in the row that match the characters in the filter bar.  The more characters you enter, the more the table will be filtered.  Please only use key words and correct spelling.  You can use letters and numbers. Do not use commas to separate keywords.
  • Sort: You can sort A - Z and Z - A by clicking on a column header. 
Note: Java must be enabled in order to use the search and sort capability.
SQL TItle
Description
Category
This report lists all patients seen between the two dates that you specify. It also lists their age on the date of service.
Chart
This report indicates how many patient visits, if any, active patients have had since their last checkup.
Chart
This report shows vaccines that were administered but were not billed between the two dates that you specify.
Chart
This report shows patient chart number, patient first and last name, contact first and last name, and contact home and work email for the first two contacts in the patient’s chart.
Chart
The sql locates all Contacts in your system and the patient chart(s) they're associated with.  This can be useful for evaluating whether you might have duplicate Contacts that need to be merged using the Merge Contacts feature.
Chart
This report shows active patients, their default sick and well appointment types, and all future scheduled appointments.
Chart
This report lists all billed diagnoses of patients seen between two dates you specify, along with the rendering provider’s name.
Chart
This SQL will show all active patients of your practice who have had at least one elevated BP in the past year (or time period you specify), as well as how many normal BPs you've measured on them in that same time period. If you have one elevated BP and 4 normals, you're probably OK. If you have 4 elevated ones and no normals, and no future follow up scheduled, you might want to do something about that.
Chart
This SQL is used to show active patients who have never been seen or who have not been seen since the reference date you specify.
Chart
This report shows notes that were finalized between two dates that you specify.
Chart
This sql performs a search of all Contacts in your system for the first name and last name you specify. It then lists the patient chart(s) where that individual is associated.
Chart
This SQL finds active insurance plans that are neither ranked as primary, secondary, or tertiary on the patient’s chart.
Chart
This report lists all your patients favorite pharmacies.
Chart
This report shows all active patients aged 14 - 17 months old along with the number of checkups they had prior to being 15 months old.
Chart
This report will give you total Referrals, Tracking, and Response entries created within a date range. The report will list the date created, type of entry, and the refer MD.
Chart
This reports shows the number patient sick and well notes written for a date range, grouped by date of service.
Chart
This report will show patients who have accessed the portal. The list is displayed based on patient and displays the patient name, parent name, patient number. If one parent has multiple children, the report will show the parent multiple times.
Chart
This report will show patients who have accessed the portal. The list is displayed based on parent and displays the patient name, parent name, patient number, and email address. If one parent has multiple children, the report will show one line for the family based on parent name.
Chart
This report shows which notes have been finalized and cosigned for dates of service in a date range you specify.  This can be filtered or modified to show what percent of nurse practitioner/PA notes have been finalized and cosigned. You will be able to deduce trends in the elapsed time periods between the notes being finalized and being cosigned.
Chart
This report shows all non-Completed and non-Canceled tasks from all departments for patients with some status other than ACTIVE.
Chart
This report fulfills the requirements of the Merck HPV “Health in Practice” (HIP) reporting program and shows commercially-insured 13 year olds seen in the past 24 months along with their HPV vaccine completion status.
Chart
This report shows all active patients’ Miscellaneous Notes and Billing Notes.
Chart
This report identifies pairs of charts where the first name, last name, and date of birth are the same.
Chart
This report shows all surveys and status and results by date.
Chart
This SQL creates a report that helps you identify patients who were a certain age range during a period you specify. It also indicates patient status, primary, and secondary insurance. After the report is run, you can use the dropdown filters to see only the patients who meet specified criteria.
Chart
Shows all active patients, their PCPs, and who's done the most checkups on them beginning from a reference date you specify.
Chart
This report shows all phrases in your Phrase Construction Notebooks and where those phrases are located.
Chart
This report shows the following information: Patient chart number, First name, Last name, Location, Referral date, Provider to whom patient was referred, Referral category/type, Dx code(s), Referring provider, Status of referral.
Chart
This report shows possible chart errors for active patients in terms of their assigned VFC status (1= Medicaid, 2=self pay, 3=AIAN, 4=underinsured).
Chart
This report shows all sick and well notes that have not yet been finalized.
Chart
This determines the time elapsed when the appointment was booked and when the appointment took place. For a given day you specify, it shows the date-time stamp of when the appointment was booked, the actual appointment date/time, and how much time elapsed.  It also shows the patient number (for crosschecks), the type of appointment, who they were booked with on the schedule, the initials of the staff member who made the appointment, and final diagnosis.
Schedule
This report shows all active patients with active-non medication allergies, along with detail about the allergy.
Schedule
This reports indicates the elapsed time spent in all statuses for all appointments between two dates that you specify.
Schedule
Appointments Scheduled by Staff Member for Period.
Schedule
This report shows patient name, DOB, patient phone number and schedule information (scheduled provider, date/time, and location) for patients who have more than one future appointment scheduled.
Schedule
This report displays all completed visits on the schedule for infants 30 days old or less on the day of the appointment, for an appointment date range you specify.
Schedule
This report looks for active patients under the age of 60 days old (at the time the report is run) who do not have a future appointment (of any kind) scheduled. It also lists if patients have had a recent visit in the office. This can be used as a transition of care tracking report for PCMH.
Schedule
This report shows all appointments with visit status “No Show,” along with confirmation status & date, location, appointment date, chart number, and type of visit for two schedule dates you specify.
Schedule
For a date range you specify, shows all patients seen for sick and well visits along with the rendering provider for that visit, along with the patient’s assigned PCP in the REGISTER.
Schedule
This report shows the patient name, DOB, patient phone number, and schedule information (scheduled provider, date/time, and location) for cancelled appointments in a date range that you specify.
Schedule
This report shows how frequently different rooms/resources were used between two schedule dates you specify.
Schedule
This report shows location, scheduled provider, appt date & time, patient name, chart number, DOB, appt type, primary phone number, visit status for the date range that you specify.
Schedule
This report shows list of patients who no-showed today, along with their no-show list prior to today.
Schedule
This report lists active patients with second hand smoke exposure in risk assessment, and whether or not they have the second hand smoke SNOMED in their RISK ASSESSMENT as a problem (any status).
Medical
This report shows all billed diagnoses for active patients between the two dates you specify.
Medical
This report shows all instances of a specific billed diagnosis for active patients between two dates you specify. The diagnosis code is entered when you enter the the date range.
Medical
This report shows chart number, first name, and last name of all active patients who have “Synagis indicated” in their birth history.
Medical
This SQL will be used for North Carolina’s Lead Registry reporting. The report will run and produce all lead tests into a CSV file that can be uploaded to NCDHHS.
Medical
This report looks for active patients who are (on the run date of the report) between the ages of 1.8 years (that is 21.6 months) and not yet 2-years to see whether they pass or fail the HEDIS CIS-2 metric.
Medical

This report shows all the active problems for all your active patients. It indicates the ICD and SNOMED (if any) codes that are attached to the problems. Finally, it lists the creation date/last updated date for each of the problems on the list.

Medical
This report shows all active patients who have ever had a BMI computed. It lists the most recent date of BMI computation, age at computation, BMI, BMI percentile (for under 18 years old), and appropriate ICD-10 Z code for that BMI.
Medical
This report identifies active patients with a coded diagnosis of persistent asthma on their problem list (either active or inactive problem status).
Medical
This report shows all patients 18 and under who have received one or more immunizations of any type during a specified time period and classifies them into one of three age groups: <1 year old, 1-6 years old, and 7-18 years old.  It also classifies them by VFC category number (1 = Medicaid, 2 = self pay, 3 = AIAN, 4 = underinsured, 5 = not VFC eligible). Many states require this in their annual VFC re-enrollments.
Medical
This report looks for active patients who are (on the run date of the report) between the ages of 1.8 years (that is 21.6 months) and not yet 2-years and looks to see whether they pass or fail the HEDIS CIS-3 metric.
Medical
This tells you, for active patients who have Nexplanon listed as an ongoing/chronic med, the date you entered it in their chart. 
Medical
This SQL was used to create the data for the Extreme Practice Makeover presentation at OPUC 2019.
Medical
HPV Due VaccinesThis SQL is used to indicate who is due for HPV: active patients between 11-21 who haven't completed the series (Either 2 or 3 doses) who are slightly past the minimum spacing requirements.
Medical
This report shows list of active patients ages 16-21 and the total number of meningitis B vaccines that they have received.
Medical
This report shows all active patients ages 16 and up and lists the number of meningococcal vaccines they have had administered (either by the practice or legacy vaccines).
Medical
Reach Out and Read ProgramThis SQL creates a report that tells you how many checkups you did between dates you specify that fell in one of several age "bins" between 6 months and 5 years old.
Medical
This report shows all active patients that are 13-18 years old along with whether they have completed (“pass”) or not completed (“fail") the HPV series.
Medical
This shows active patients who (during the reference date you specify) are 13-18 years old and whether they have completed the HPV series (either 2 or 3 dose series).
Medical
This SQL shows all active patients with BMI greater than the 95th percentile for age.
Medical
How many of your patients with asthma in their problem list have had an asthma action plan written/updated in the last _______ months/years? This SQL is intended to answer that question. The date in "most_recent_AAP" will be the date the last asthma action plan was written/updated, and blank if the patient has never had one.
Medical
This report shows the number of active patients by medical home location, VFC status, and age “bucket”.  It can be used as an aid in allocating vaccine inventory among multi-site practices.
Medical
This report shows patient name, prescription information, prescriber, and pharmacy (for e-prescriptions) for prescriptions written between two dates you specify.
Medical
This report fulfills the Merck program requirement for “Number of active patients ages 19-26 seen in the past 24 months who have received three total doses of HPV (“pass”) or not (“fail”).
Medical
This report looks at all patients who have open (PENDING) referrals but do not have an ACTIVE patient status within the practice.
Medical
This SQL shows the percentage of sick-visit patient's that came in for a flu-related visit.
Medical
This report shows the total number of surveys with a certain name, done before a certain age, for all active patients between a certain age range.
Medical
This SQL shows all kids that are 1 - 3 years old who had a checkup on the date they were seen in a selected date range.  If they had a fluoride done (LOINC code "XFLUOR") sometime in that period (not necessarily the same day as the checkup), then it puts the date in "FLUORIDE DATE" column.
Medical
This report shows all active patients and the number of vaccines they have had recorded in their chart.  Blank means 0. You can change the dropdown filter to show only blanks.
Medical
This report shows the CURRENT aging for a set of PAST date of service transactions that you specify.  Similar to “Accounts Receivable for Dates of Service” but breaks out by insurance.
Financial
This report shows all encounter notes with nothing billed on those dates of service for a date of service range you specify.
Financial
This report shows the CURRENT aging for a set of PAST date of service transactions that you specify.
Financial
A well note will show as “unbilled” if no CPT codes of any kind were billed on that date of service.
Financial
This report shows all CPTs and charges, payments and adjustments, claim notes and adjustment codes, diagnoses, and age of patient at the time of service for a date of service range that you specify.
Financial
This SQL shows all patients with nonzero patient or insurance credits. You can apply a filter in the dropdown to exclude whatever it is you don't want to see.
Financial
This SQL shows all patients with nonzero patient or insurance credits. You can apply a filter in the dropdown to exclude whatever it is you don't want to see. It also includes patient name columns.
Financial
This report shows all encounter notes reflecting dates of Saturday or Sunday where neither 99050 or 99051 was billed for a date of service range that you specify.
Financial
This report shows the frequency and total units billed for each CPT by insurance.
Financial
This report lists the average payment for well visit by insurance carrier based on the date range you specify.
Financial
This report looks for all patients with balances who also have an active credit card on file (stored in the CC_ON_FILE table).
Financial
This report shows patient chart number, first & last name, date of service, DOB, age, and billed CPT code.
Financial
This report shows list of patients with either (or both) a nonzero credit (either insurance or patient-side) and a nonzero balance, along with next scheduled appointment (if any).
Financial
This report shows all open claims (either insurance-side or patient-side) with a date of service between the dates you specify.  It also assigns an aging bucket of <30, 31-60, 61-90, 91-120, and 121+.
Financial
This report lets you see all claim statuses that took effect in a given date range you specify.
Financial
This report shows entire CPT code record for all CPT codes, including all fee schedules for all CPTs entered into OP.
Financial
This report shows all patients with balances, aged from patient responsibility date, grouped by guarantors.  It also gives a family total balance, and the date and amount (if any) of the last patient payment on the account.
Financial
This report shows how to get the numbers 99201 to 99205 and 99211 to 99215 that each provider saw at each location (along with totals) for the specified date range.
Financial
This report shows the chart number, DOB, claim (invoice) number, date of service, CPT code, insurance code, charge, place of service, diagnosis code(s), payment, and adjustment amounts for the date range you specify.
Financial
This report shows payments and adjustments received during a user-defined period, for dates of service performed during another user-defined period.
Financial
This report shows all patients with a balance, aged into 0-30, 31-60, 61-90, 91-120, and 121+ day buckets.  This report also shows guarantor ID and name, patient status PCP, and the next appointment scheduled (if applicable).
Financial
This SQL lets you pick a date range (say, this week) and takes all your CPT codes for charges rendered, matches them to your contracted allowables, and tells you the expected amount you'll receive, based on your insurance contracts.
Financial
The report identifies total payments for the sick visit code of a sick-and-well visit and compares it to the contract amount for the sick visit.  This can be used as a way to identify carriers who reduce payment for a sick visit when combined with a well visit.
Financial
This report looks at all the completed appointments on the schedule, finds the associated billed insurance, and counts the total number of encounters.
Financial
This report shows the practice-entered cost of a CPT code (e.g labs or vaccines) and total payment for that CPT code for a date range you specify.
Financial
This report shows the entire charge and payment activity on a particular chart/account for a specified date range.
Financial
This report is similar to  HEDIS Immunization Metric for Adolescents (IMA). It takes all currently-active patients who were 13 years old as of a reference date you specify, and determines whether they have had 2 or 3 HPV, at least one dose of Td/Tdap, and at least one dose of meningitis ACWY vaccine.  If all three of these metrics are ‘pass,’ then the child also gets a ‘pass’ for the entire IMA measure.
Financial
This report is similar to HEDIS W34. It takes all currently-active patients who were 3-6 years old as of a reference date you specify, and determines whether they had a checkup in the previous 365 days of the reference date.
Financial
This report provides data for an average to “time to paid claim” in days by insurance. This report is based on the date range of claims that you specify.
Financial
This report shows patient number, date of service, rendering provider, appointment type, CPT, total insurance paid and total insurance adjusted, for a date of service range you specify and a payment date range you specify.
Financial
This report looks at all adjustments for a period and totals them by adjustment code.
Financial

This report looks at the number of claims per calendar month (for a date range you specify) that were completely written off  on the insurance-side. This is a good general monitor for performance around timely filing and appeals for your billing department.

Financial
The SQL is used to see what was charged for a certain time period and the payments that came in (at ANY subsequent time) for that work. The report breaks down the data by billing provider.
Financial
This report looks at all patient charts who do not have a guarantor assigned. It also lists if they have a balance and the status of the account.
Financial
This SQL finds individual line items that are over/underpaid when the total claim is in balance. A practice whose accounts are correct (at least in this way) will receive no results when this report is run.
Financial
This report indicates instances of when a well visit (9938x, 9939x) was billed on the same day as a sick visit (99201-5, 99212-5). It also indicates the patient and what rendering provider.

Financial
This report shows the vaccines given in the office between two dates you specify. It also shows the VFC status, payments, and adjustments for each vaccine.
Financial
The SQL is used to see what was charged for a certain time period and the payments that came in (at ANY subsequent time) for that work.
Financial
The SQL is used to see what was charged for a certain time period and the payments that came in (at ANY subsequent time) for that work. The report breaks down the data by rendering provider.
Financial
Identifies total insurance adjustments by type between two dates you specify.
Financial
Shows number of each type of sick visit, sum of new and established sick visits, and grand total of sick visits for a period you specify, grouped by location and rendering provider.
Financial

Shows number of each type of well visit, sum of new and established well visits, and grand total of well visits for a period you specify, grouped by location and rendering provider.

Financial
This report shows claims which are “out” to a secondary insurance, along with primary and secondary insurance responsibility dates and aging days (i.e. the elapsed time from the secondary insurance responsibility date to today).
Financial
This report looks at what you were paid for each vaccine serum that has been billed and completely adjudicated.  It compares what you were paid to the cost of the vaccine, assuming you have put the cost per dose in FEE4 in the CPT code tables.
Financial
This report shows patients who were billed J0561 (long acting injectable penicillin) with something other than 6 or 12 units between two dates that you specify.
Financial
This report shows vaccines which were administered by your practice but do not have the corresponding expected CPT code in claims for the same date of service.
Financial
This report looks for all active patients who have an active insurance policy that contains a specified prefix. The SQL code consists of the prefix ‘ABC’, but it can be altered to find the insurance policy prefix you desire.
Financial
This report lists the patient chart number, patient balance, and how many statements there are for the unpaid claim with the most statements.
Financial
This report shows posted credits (where money came into the office) of method CC (credit card), CA (cash), or CK (check) on a date you specify, along with the staff member who posted the credit in OP.
Front Desk
This report displays all active entries in your address book.
Front Desk
This report shows active entries in the Address Book with specialty name.
Front Desk
This report is intended to provide you with a list of assigned staff permissions. You will be able to see at a glance which permissions are assigned to which staff members. Further, you will be able to tell if the user was assigned the permission as an individual, group, or both.
Practice Management
This SQL shows, grouped by month, how many nurse, sick, well, and sick-and-well visits your providers conducted.
Productivity
This report looks for the total number of sick and well E&M done by rendering providers per month based on a date range you specify.
Productivity
This report indicates which staff member (other than a provider) opened sick and well notes for a period. The report is based upon the staff ID number in the Entered by field on the visit's Visit Info tab for a Patient's Well or Encounter note. Data in this report is summed by staff member, according to the date range you select.
Productivity
This SQL is intended to indicate who the vaccinator was for all the shots given in your office during a selected date range.
Productivity
This SQL creates a report to show encounters by rendering provider along with the total payments received by rendering provider for a period between two dates that you specify.
Productivity
This report shows the count of nurse visits, sick/procedure visits, well visits, and well-with-sick/procedure visits by rendering providers for a period that you specify.
Productivity
This SQL report summarizes total visits and patients by insurance for a date range you specify.

Productivity