Welcome to the Open Dental Query Examples Page

These query examples were written for other practices, usually for a specific purpose. There may be additional changes needed to return the results you want.   
Queries are sorted chronologically, with queries written for older versions listed first and queries written for newer versions listed last.
We recommend looking at higher numbered queries first, because older queries may not work in new versions.

If you find a query you like, copy/paste the query into Open Dental to run it. See User Queries
If needed, change any required variables before running. Look at the query comments for variable descriptions.

If you need help finding a query, modifying a query, or would like a custom query, fill out and submit a Query Request Form

Limit query results by category. Up to 3 categories can be selected. Results will only include queries that match ALL selected categories.

        Return Queries About:     AND    AND

                   Dbms versions: 

Result Count:  1493                                                                                                                                                           

QueryIDQuery, with Title and Notes
All claims that are currently open that were sent on a given day - change DateSent value as needed
DBMS version(s): MySQL 5.x, MariaDB 10.5
All treatment planned procedures, ordered by patient - See also #50 & #56
DBMS version(s): MySQL 5.x, MariaDB 10.5
Birthday postcards for a one week range - In the example, from 10/06 to 10/13
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily patient payments organized by chart number and date entry. - In the example, for 7/29/2005
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily Insurance payments organized by chart number -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging report which includes date of last payment - The last payment date is only for payments entered on the guarantor of the family.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging report which includes chart numbers -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedures report which includes chart numbers -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Estimated balances for patients with an appointment on a specific day - Patient balance calculations are for versions 16.2 through 19.1. Results may be inaccurate using versions 19.2+
DBMS version(s): MySQL 5.x, MariaDB 10.5
All lab cases with a status of received, and appointment not complete -
DBMS version(s): MySQL 5.x, MariaDB 10.5
OBSOLETE Production by fee schedule for date range - OBSOLETE This is a useful report, but please note that the amounts are taken from claims you've sent. So, it will never include private pay, and if you billed with UCR fees showing, it will use those fees, which are higher. Also, secondary claims are incl
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures - It sorts by the cost of the procedure, which is usually what you want. You can increase the limit to get more rows in your results.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Public Health Raw Population Data. - SEE REPLICATION WARNING for versions before 14.3.1. This query will not work on versions 7.8.1 or higher of Open Dental as the school table was removed.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily Procedures: Grouped by Procedure Code, similar to internal report - This query mimics the standard Daily Procedures Report, with the Group By Procedure Code option selected. The standard report also allows filtering for Providers, Procedure Codes, and Clinics, which this query does not do.
DBMS version(s): MySQL 5.x, MariaDB 10.5
A list of all referrals you have received for a user specific date range - For Versions 17.1 and greater. Please update your version accordingly. Shows how many patients referred by each source in the date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
For public health clinics, the production by gradeschool. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
For public health clinics, the number of patients seen at each grade school for a date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients, grouped by billing type - Includes inactive and archived patients
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of new patients for a given date range - Does not confirm patient was actually seen
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referral sources with how many unique patients referred and gross production for the specified date range - Similar to internal Referral Analysis Report. For Versions 17.1 and greater. Please update your version accordingly. Production will not be accurate if a referrer is attached to the same patient more than once.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of distinct patients with a procedure completed in the specified date range - Patients will only be counted once for the date range, not once for every visit. This includes broken appointment codes.
DBMS version(s): MySQL 5.x, MariaDB 10.5
For Payment Plans, this lists guarantors of plans for which the date of the first payment will soon be due. - In the example, it lists all plans where the date of the first charge is in the month of March, 2006.
DBMS version(s): MySQL 5.x, MariaDB 10.5
For Payment Plans, this lists guarantors of plans for which the date of the last payment is approaching. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
For Payment Plans, lists all charges due for a date range and includes billing type. - Example is for the month of March.
DBMS version(s): MySQL 5.x, MariaDB 10.5
A list of all subscribers who have a particular carrier. In the example, it's for Delta, but you can substitute your own search string between the %'s. - Will show dropped plans. Returns subscribers, DOES NOT GIVE patients who have the plan, see #118
DBMS version(s): MySQL 5.x, MariaDB 10.5
A list of referrals during a specific date range. - For Versions 17.1 and greater. Please update your version accordingly. Best for exporting to a text file for use in a letter merge.
DBMS version(s): MySQL 5.x, MariaDB 10.5
An alternative way of finding procedures not billed to insurance. - Only works on small databases which were not converted from other programs. Might not be very useful.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient payments for one day - For instance, cash, check, or credit card.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient payments for one day grouped by type, so only the totals for each type show -
DBMS version(s): MySQL 5.x, MariaDB 10.5
A list of all outstanding preauthorizations -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of writeoffs for a daterange and for one provider - Leave out the line containing ProvNum to get for all providers
DBMS version(s): MySQL 5.x, MariaDB 10.5
Quarterly Production by billing type. - Does not include adjustments.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of families seen in the last three years - Useful for generating a list of patients for Christmas cards.
DBMS version(s): MySQL 5.x, MariaDB 10.5
A list of patients seen between two dates (based on procedures completed in date range). -
DBMS version(s): MySQL 5.x, MariaDB 10.5
A list of all guarantors of patients with an active status - This is another way of getting a Christmas card list without filtering out patients who have not been in for a while.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List and count of patients that were seen by you in a date range and their referrals - For Versions 17.1 and greater. Please update your version accordingly.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients and their addresses who have not been seen since a certain date - It will also give you the date of their last visit. Submitted by Jorge Bonilla, DMD, updated by Open Dental.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of subscribers for a given carrier and groupnum. - Note: This is a list of subscribers, not patients. See 118 also.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Sums of Aging report without having to print the entire report. - If you do not update aging first, results will not be current
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment history for one patient - Change the 581 to the appropriate PatNum before running
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing information for guarantors of active patients. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payments summarized by type -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients for time span who were actually seen - Excludes ones who made an appointment, but then no-showed.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient contact info for patients not seen since a certain date - Excludes broken/missed codes. Submitted by Jorge Bonilla, DMD.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures, that are not in a scheduled apt - For a date limitation, see #194
DBMS version(s): MySQL 5.x, MariaDB 10.5
Answers the question: during a given period, what is the production generated by different referral sources - For Versions 17.1 and greater. Please update your version accordingly. Includes net production for each source, limited to the date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Return list of statements sent by mail in last 30 days, or other interval - Change value in interval statement to a number other than -30 for a different interval, the 30 interval is for the due date and should not be changed, assumes monthly billing
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production for date range with pay splits and tooth and surface -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient payments by procedure for date range - Patient Payments by procedure only works if you split the payments by procedure when entering payments, uncommon but there are several offices that do this, especially if paying providers by income
DBMS version(s): MySQL 5.x, MariaDB 10.5
All insurance claimed procedures with UCR fee for date range, even if a different fee was used or sent , for a specified fee schedule. - Useful for comparisons, you can also add the column pl.ProcFee to see what was actually charged.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Returns all treatment planned procedures (summed by patient) for active patients without a scheduled OR planned apt, with phone nums - Useful for those transitioning to planned appointments
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance payments received after a certain date for the specified carrier, sorted by carrier - Chart Number used, can use patnum if needed.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Received Lab Cases with sent and received date with patient number -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient lifetime revenue and TP'd procedure totals - also includes PatNum and patient age, does NOT include insurance revenue
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment Planned Procedures with Referror and Insurance -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Query scheduled procedures for code with date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients payments by type with patient name (PatNum) for a day - Was useful for a few versions from about 5.4 to 5.6 where insurance checks were not being returned by patient name, this report is included now in standard reports, (daily payments with insurance checks by patient option selected)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of active patients of each age - Doesnot imply that a procedure has been completed on patient
DBMS version(s): MySQL 5.x, MariaDB 10.5
Shows all records with duplicate first and last name -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients list who have seen a hygienist within a date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Subscribers grouped by employer, gives # of active patients who are subscribers with insurance through each employer - This just gives count, you can get names in query 188
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients for date range with ref source and sum of first visit fees - For Versions 17.1 and greater. Please update your version accordingly. Will not return patients with no referral source listed (designed for lending bank reporting req)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients by Carrier with procedures completed in date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Income by insurance company for date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Carrier and patient list seen in date range - to include only active patients add WHERE...AND PatStatus=0
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of active patients seen in a date range grouped by billing type -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Received Preauths over last X months -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient payments in date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients in date range with address and ref source - Patients will be listed multiple times if they have multiple referrals. For Versions 17.1 and greater. Please update your version accordingly.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Returns guarantors of patients who have not received an email with a specific word in subject line within 45 days who have an appointment in a date range - It is too specific for general use, but gives an idea of how you can use a query to make sure you have sent registration information by email.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with no scheduled apt who have not been in for a time period with the date of their last scheduled apt. - (From @daysIntervalStart days ago to @daysIntervalEnd days ago, change the interval currently 365 days: 1 year) This is useful for making a patient list before archiving patients, to call and try one last time.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance claim procedures with UCR fee, InsEst and InsAmtPaid - Change date range as needed, does not distinguish between received claims, sent claims etc. Only default fees are shown, even if the UCR Fee Schedule is not a global fee schedule.
DBMS version(s): MySQL 5.x, MariaDB 10.5
All treatment planned and scheduled, all treatment planned total, returns these two dollar amounts, active patients - SEE REPLICATION WARNING for versions before 14.3.1. The second number includes the first, so the relationship beween the two amounts is the percentage of treatment planned work that is scheduled right now
DBMS version(s): MySQL 5.x, MariaDB 10.5
Date Range limited: treatment planned and scheduled, treatment planned total, returns these two dollar amounts for all patients - The date range of the first number is applied to the appointment scheduled date, the date range for the second amount limits by what date the TP procedures were made.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients from given city seen in date range - theoretically helpful for splitting practice
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with the specified code attached to a scheduled appointment with appointment date in the specified date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient and insurance payments by patient for date range for given provider - Patient and insurance payments by patient for date range for given provider
DBMS version(s): MySQL 5.x, MariaDB 10.5
Adjustments summed by type -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Sum of payments made by carrier for procedures in a date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance income for time period summed by guarantor and carrier -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging Report for Balance over 90 with no payment in last 30. - Uses patient table aging, run aging prior to this; versions 22.2+ run aging automatically through the Open Dental service.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Find an Insurance check by Check Number - Change check number between the '%%' symbols
DBMS version(s): MySQL 5.x, MariaDB 10.5
Export patient data to import into another program - The file created as a result of this query will be ON THE SERVER at c:\TEMP\patients.csv. One should ensure that the c:\TEMP folder exists on the server and does not contain a patients.csv file before running this query.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Find an Insurance check by Amount and Date -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Get Patient info based on subscriber id (or part of it) - Add more fields from patient table as needed
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures with notes for date range (completed procs) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Find insurance plans without assigned benefits - Returns subscribers of plans (vs patients using plan)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with saved Treatment Plans and date of plan -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient, provider, balance, like shown in patient payment window - SEE REPLICATION WARNING for versions before 14.3.1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
CommLog notes by Type -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients with Clinic and Referral source for date range - For Versions 17.1 and greater. Please update your version accordingly. Excludes patients who have never actually had a procedure completed.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients seen in the date range by patient's assigned clinic with total for date range. - Excludes patients that don't have a completed procedure in the date range and those that have completed procedures with no fee.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with a completed procedure today - Patients names, addresses & phone(s)
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patient count, insured and not insured over date range - Uses Date of First Visit. Also see #545 for breakdown by month over a long period.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referred procedures by date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with NO images - (nonindexed table, so will take a VERY long time to run, upward of one minute) , also not to useful post version 5.6 since images also include statement PDFs, and in any version, scanned images et, generally used for determining missing files
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients' image count - (only returns patients with images, runs fast), note that images include PDFs stored by Open Dental
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures with date and description where the sum fee paid is greater than the fee - Helpful for incorrect medicaid estimates. For Open Dental versions prior to 6.1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procs with date and description w/ sum fee paid > fee and patient acct credit - Same query as 110, but adds condition: Patient balances < 0 (account credits) helpful for incorrect medicaid estimates, uses last balance from updated aging claimproc status is received or supplemental. For Open Dental versions prior to 6.1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Returns procedures with estimated insurance greater than charged fee claim proc status is not received AND the proc is complete AND the status of the claim is sent -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims by carrier -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims for a particular carrier -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors of families where no payment has been made in 1 Month and they are over 90 days past due -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Anticipated and incurred Writeoffs - USE with production report to get real production if you want to count writeoffs as the claim is made, not when they come in (New report available in 5.7.0+ gives these PPO numbers without this query)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total Anticipated and incurred writeoffs for procedures completed in range - Like 116 but sum only
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing list of guarantors of patients with a particular carrier - Unlike some others, returns the guarantors of all patients with plan, not the subscriber, also if you drop the plan it goes off the list
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed procedures for date range showing tooth surface - Change dates to change range
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Guarantor's Fname and LName, Guarantor's Email address and the FName of those patient under that guarantor's account - excludes inactive patients
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients referred to you or from you in date range - For Versions 17.1 and greater. Please update your version accordingly.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of active patients with each insurance plan (not subscribers) - NOTE:will not sum to total patients as some patients may have no insurance some may have more than one
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen in time period with column indicating recall and non recall procs and if the apt was recall only - Useful for dentists trying to sell non recall procs
DBMS version(s): MySQL 5.x, MariaDB 10.5
Commlog entries in the specified date range, for the specified commlog type. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Hygiene Production For Time Period -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Hygiene Summed by Procedure for Date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients who have never had one of a user-defined list of procedures. Includes last visit date and total procs completed on patient. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of Patients seen by each provider in date range - A patient will count once for each provider seen in the date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantor (Mailing) info for New Patients with last seen Date of any dependent - SEE REPLICATION WARNING for versions before 14.3.1. for Pedo practice mailing letter to parents of new patients
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients not seen since a date plus proc count -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Subscribers with insurance of specific employer(s) -If you only want one employer, duplicate name in both fields - Replace the text between the % signs, you can use any part of employer name, not case specific
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients who have never had any procedure(s)completed - note that active means that this is the status of those patients. The idea being that you may wish to review these accounts to follow up and try and schedule work or deactivate them.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims by Date of Service not including Preauths -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Preauths received in last year but procedures not scheduled -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed procedures for date range, limited to specified procedures - Enter procedure codes separated by a comma. If left blank this will search for all ProcCodes
DBMS version(s): MySQL 5.x, MariaDB 10.5
Returns all procedure notes entered in given range, including group note - See example #98 if you want all iterations of each note and to limit to completed procedures
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patients in Date Range with Date of last visit and Procedure Count -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a given insurance fee schedule -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Check secondary claims received in date range for writeoff amounts -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing List for patients with TP procs without a scheduled apt -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Recall information with e-mail -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of complete appointments in date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures for patients with no scheduled apt with specified carrier - (see also #50 & #56)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment Planned procedures that were treatment planned in a specific date range and which provider treatment planned it -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient list of Sum of the Fees of all Treatment planned and all scheduled procedures - SEE REPLICATION WARNING for versions before 14.3.1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients who have had recall disabled -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment Planned procedures that match codes in list, for patients with no scheduled appts containing the specified codes -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedures call list - Runs for 'today'
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance estimates and paid amounts of claims that were created in Date Range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
End of day call back list for completed procedures -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients who have given carrier with date of last treatment - Limits to last treatment being after a given date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Addresses (with insurance) of active patients with tp procs with no sched apt -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Addresses of active patients (with no insurance) with tp procs with no sched apt -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment Plans Master - This will give you a summary of all of your save treatment plans... See comments in query
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims by Date of Service, secondary claims ONLY -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Balance and Fee for every procedure for a given patient - (useful when entering procedure split payments)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients that have Service Year instead of Calendar Year insurance benefit - with count of service year benefits
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count number of Active patients in each billing type. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income for a particular patient - (with adjustments, insurance income by inspay date and writeoffs)
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients with each insurance plan (not just subscribers) includes only matching carrier(s) - Carrier Names and group names are cutoff for space consideration Check RAW button to show both PatNum and Patient Name
DBMS version(s): MySQL 5.x, MariaDB 10.5
Returns all treatment planned procedures for active patients without a scheduled OR planned apt, who were last seen in a given date range - With phone numbers, useful for those transitioning to planned appointments (this differs FROM #56 largely in that it is date limited and lists out the treatment)
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients for a time span, defined more tightly. New patient is someone who comes in once for a exam with a specific code - (in this case D0150) and has completed procedures on subsequent visit, (you must use code only for new patients, e.g. D0150N, 6th digit will not go to ins)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Received insurance claims FROM a particular carrier showing estimated vs paid amounts -
DBMS version(s): MySQL 5.x, MariaDB 10.5
On Hold insurance claims -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen in date range who have had non-diagnostic work - SEE REPLICATION WARNING for versions before 14.3.1. (procedure codes not starting with D0) Mailing list limits to one person per guarantor and to those who have zip codes
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active Patients listed with Insurance Carrier -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Lists all patients with billing type indicated and how much they have paid total, ever -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance Payments received in a given period, summed by carrier -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance Payments entered and Checks received in a given period - not summed by carrier, useful for finding discrepancies*
DBMS version(s): MySQL 5.x, MariaDB 10.5
Last visit before given date for all active patients with phone numbers -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients of specified age range and gender -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients (in 'Patient' status) that have Service Year instead of Calander Year insurance benefits -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active Patients who have had a particular proc completed relative to Active patients who have had any proc completed within a given date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient count by zip code, with at least 1 completed procedure (excludes broken/missed codes) - limits to count(zipcodes)>3 as outliers, can be changed to 0 to include all
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient count by city, with at least 1 completed procedure -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with no entry in the recall table - Not for patients with a recall due, but no appointment.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors (heads of households) of patients with no ins - with address
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient contact information verification list for appointments on given date - does not list state as that is not an issue if the zip and address are correct abbreviates to first 15 chars of carrier to save space
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed Production in Appointments by Assistant -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing list of guarantors of patients with a particular insplan seen since a specified date - Similar to 118 but also requires group number, returns the guarantors of all patients with plan, not the subscriber, also if you drop the plan it goes off the list
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled treatment for date range with apt notes includes fee and patient portion -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patients with referral source, production diagnosed on first visit and total of payments for that day - For Versions 17.1 and greater. Please update your version accordingly. Uses DateFirstVisit to determine new patients. See https://opendental.com/manual/patienteditother.html for more information. Does not fit quite on one page, export as a file and print.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Subscribers ordered by employer - lists active patients who are subscribers with insurance through each employer like 67, but lists each subscriber instead of count
DBMS version(s): MySQL 5.x, MariaDB 10.5
Adjustments of non-zero amounts - change date range as needed
DBMS version(s): MySQL 5.x, MariaDB 10.5
EMail Addresses of Active Patients -
DBMS version(s): MySQL 5.x, MariaDB 10.5
"Patient" status patients with procedures completed in the specified date range and an email address entered. - Includes date last seen within the date range
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors of patients not seen since a given date -
DBMS version(s): MySQL 5.x, MariaDB 10.5
TP Procs with date of treatment plan of patients with no scheduled appointment with date of TP > given date - Like #50 with date limitation
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with appointment in date range who had no procedure completed in the given interval prior to the scheduled appointment - Possible useful to show patients who need an extra reminder in order to show up, not hugely practical (add address2 if you use that field)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referred out patients in a date range, with specialty and status - For Versions 17.1 and greater. Please update your version accordingly.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing Info Active patients who have no email address for either themselves or guarantor -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Phone Numbers for Active patients who have no email address for either themselves or guarantor - SEE REPLICATION WARNING for versions before 14.3.1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients of given appointment confirmation status having appointments of a certain date range that have been completed or are scheduled - replace date and set confirmed status to desired value, like @ConfirmStat='%confirmed%'
DBMS version(s): MySQL 5.x, MariaDB 10.5
Recall info for insured patients with a specific insurance carrier, and are due for recall in the specified date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen in date range by specified providers with age, zip and sum of fees for procs completed - Displays gross production.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging with Outstanding Insurance Claim info and Patient Balances -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging with Outstanding Insurance Claim info and Family Balances, Summed by Guarantor - SEE REPLICATION WARNING for versions before 14.3.1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing List of Guarantors of Patients seen in date range where sum of fees for procs completed (apx production, neglects writeoffs) for family exceeds some amount - say $400, (highly active patients)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Birthdate query for mailing including year -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referred out patients by doctor - For Versions 17.1 and greater. Please update your version accordingly. Editable user date range filter for referral date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient count by age, with at least 1 completed procedure -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging report which only includes families who do NOT have a balance >90 days AND who have a positive balance (owe) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Returns patients with unscheduled Treatment Planned procedures - Leaves out diagnostic and preventative (D0*, D1*) procedures
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referred out patients for a specific ref source - For Versions 17.1 and greater. Please update your version accordingly. Editable user date range filter for referral date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Day Summary, may also be used for date ranges - note that only payment associated with EACH DATE are returned and only if there was service on THAT DATE, does not return insurance payments as payments are not received on service date and this is intended for day sheet use
DBMS version(s): MySQL 5.x, MariaDB 10.5
Day Summary for Today - note that only payment associated with Today are returned and only if there was service Today, does not return insurance payments as payments are not received on service date and this is intended for day sheet use
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments in the past that are scheduled but not complete or broken or unscheduled -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients who need followup - Patients with a procedure in a given list completed in date range but no other procedures on another date, apts are counted by procedures summed by date, so it works even if you do not use appointments
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all procedures of status 'Existing Current Provider' in date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all completed work with fee of $0 and with 'D Code' in date range with standard fee listed -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients with sum of treatment fees in given time period with referral source - For Versions 17.1 and greater. Please update your version accordingly
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referrals 'To' of a given status and key word in referral attachment note - to get a patient/referral on this list, for instance, set referral attachment status to Scheduled and put the word xray or x-ray somewhere in the ref attach notes
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patients, Count per week for a given date range. Active patients only. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Income by ref source for date range, only includes referrals received and income collected during that period, also returns TP fee total - For versions 16.4 and below. SEE REPLICATION WARNING for versions before 14.3.1. Submitted by Steve Gershkowitz
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total production in Date Range for a list of specific procedure codes -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production by Carrier for date range (or current day) with sum of patient portion estimate (does not include writeoffs) - Also includes uninsured total production, and for pat portion, insurance estimates, sent and received claims are considered
DBMS version(s): MySQL 5.x, MariaDB 10.5
Sum of differences in fee billed and any given fee schedule for all procs completed in a date range for a given carrier, like medicaid - Useful in calculating medicaid 'writeoffs', only valid after version 6.1.0. If the indicated fee schedule is not global, only default fees will be considered.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient count by Zipcode, with at least 1 completed procedure in date range (excludes broken/missed codes) - limits to count(zipcodes)>3 as outliers, can be changed to 0 to include all like #178 but with date range
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with NO previous date OR Calculated Date in Recall List and who HAD A PROCEDURE COMPLETED in given date range - not really needed after version 6.5
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients that don't have Previous date and Calculated Date in Recall List and HAD AN APPOINTMENT in date range, whether apt was completed or not -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures completed for patients of a given age range in a given date range with primary insurance carrier and fee - considers AGE AT TIME OF SERVICE, other tables are includes to make it easy to get ins payment, estimate etc
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures with date and fee for patients without a planned or scheduled appointment, excludes codes in user defined list - Edit list as needed
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures for patients with neither planned nor scheduled apt, where code is in a user defined list with age and phone numbers - address can be added if needed, but will not fit on one page with phone numbers
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures completed for day(or date range) with latest procedure note and tooth surface(s) - SEE REPLICATION WARNING for versions before 14.3.1. Also shows whether the note is signed
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding Preauths by Date Sent with PatNum -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Annual production and income report For all providers - SEE REPLICATION WARNING for versions before 14.3.1. This is similar to the included P&I Report
DBMS version(s): MySQL 5.x, MariaDB 10.5
Annual production and income report as it would have been pre version 5.6 on a certain date for a given year For designated providers - SEE REPLICATION WARNING for versions before 14.3.1. Pre 5.6 equivalent P&I report for one specified provider
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging with Outstanding Insurance Claim info and Family Balances, summed by guarantor, includes procedure date of oldest outstanding sent claim, last statement date and last payment made - WARNING: This query could lock up your database if you have a large database.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients to Archive: Last visit X days ago for all active patients with balance less than given amount - helpful for archiving patients
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a specified insurance carrier seen in date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a specified insurance seen in date range Who have a balance greater than given amount -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Returns inactive, archived, deceased and nonpatients that have scheduled appointment with apt date and time - Limits to appointments scheduled for Today or later
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding Claims by Date of Service with PatNum, Amount billed insurance, Amount billed patient and Date sent - Edit Interval number if you want to change minimum time outstanding
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims of status 'Sent' or 'Received' by Date of Service with PatNum, Amount billed insurance, Amount billed patient and Date sent - Edit Dates to change date of service range
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with appointments for a date range (in future or past) with primary insurance carrier listed - Also lists sum of fees for day and insurance type
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with appointments for Today with primary insurance carrier listed - Also lists sum of fees for day and insurance type
DBMS version(s): MySQL 5.x, MariaDB 10.5
Lifetime income from new patients that started in date range - Also includes patient address and date of first visit
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient lifetime revenue and insurance revenue and TP'd procedure totals - (also includes PatNum and patient age, demographic marketing query) Like 59 but also shows insurance income associated with patient
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active Patients whose birthday is in a specified month listed with primary Insurance Carrier, Birthdate, Name and Address - Sorted by day of month for birthday, can be easily changed.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned work totalled by patient with annual ins max, ins used and name of carrier - Warning - may be slow
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging report only including families that have any member of family with a particular carrier - SEE REPLICATION WARNING for versions before 14.3.1. Also includes # of open claims and the sum of the open claim amounts
DBMS version(s): MySQL 5.x, MariaDB 10.5
ALL Fee Schedules all procedures, arranged by category with fees - Set fee schedule name at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production by patient, procedures completed today with primary insurance carrier listed - Also lists sum of fees for day and insurance type, does not consider adjustments or writeoffs
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments that are broken for a date range, with note -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures of the specified codes completed in the specified date range with claims created for a specified insurance carrier - with patient name, date of procedure, fee, and amount paid
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patients with referral source, gross production completed in date range with ZIP and Age - For Versions 17.1 and greater. Please update your version accordingly
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance total for claims with the date of service in a date range. includes esitmates and carrier information. - Leave dates blank for all claims with Date of Service more than 30 days ago. Excludes Pre-auths.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Get questionaire results for export and analysis - Replace term 'married' with desired search term or eliminate all between the %% to return all questions
DBMS version(s): MySQL 5.x, MariaDB 10.5
Last visit before given date for all active patients with phone - Also shows last seen date for 1 specified provider
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance plans for patients of a given carrier, with guarantor, social security number and birth date -
DBMS version(s): MySQL 5.x, MariaDB 10.5
claim count by insurance carrier for date range with sum of fees, estimates and paid amounts -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of specified procedures completed in the specified date range for patients with the specified insurance carrier(s), including uninsured - Shows patient name, service date, procedure code, primary insurance carrier, fee, sum of ins est, sum ins paid
DBMS version(s): MySQL 5.x, MariaDB 10.5
All referrals in date range, with additional referral information - For Versions 17.1 and greater. Please update your version accordingly. Columns may be added or removed. As is you need to export to a spreadsheet to print due to width.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Monthly production and income report - SEE REPLICATION WARNING for versions before 14.3.1. counts insurance writeoffs by procedure date (PPO) For all providers
DBMS version(s): MySQL 5.x, MariaDB 10.5
Phone numbers and status of patients in a given zipcode -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Basic deposit query, returns date of deposit and amount in given date range - Change date range as needed
DBMS version(s): MySQL 5.x, MariaDB 10.5
new patient count by zip code - with total
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding Preauth Procedures by Date of Service with Procedure Codes - Edit interval if desired, currently older than 30 days
DBMS version(s): MySQL 5.x, MariaDB 10.5
Calculate current or historical accounts receivable, collectible, outstanding insurance estimates - SEE REPLICATION WARNING for versions before 14.3.1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance (Estimates or Paid) for a month (by date of service) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Estimated Writeoffs Outstanding -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with balances who do not have insurance - (not family balance, patient balance)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count Per Doctor of incoming referrals in date range with last referral date - For Versions 17.1 and greater. Please update your version accordingly
DBMS version(s): MySQL 5.x, MariaDB 10.5
Incoming referrals with refdate for a doctor with a specific last name and (optional) first name - For Versions 17.1 and greater. Please update your version accordingly
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with prescriptions for a specific drug in given Date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient Refunds in Date Range - Looks for any negative patient payments that are not income transfers
DBMS version(s): MySQL 5.x, MariaDB 10.5
Verification list for appointments on given date - Includes Carrier Phone and Procedures in Apt abbreviates to first 15 chars of carrier to save space
DBMS version(s): MariaDB 10.5, MySQL 5.x
Number of procedures of each code completed in date range by provider with claims made to a specified insurance carrier. Broken down by Primary claim or not primary. - Includes procedure count, average and total fees (for primary claims only), average and total amount paid by the carrier, and the average and total writeoff amount for that carrier Only includes procedures attached to claims. Carrier Paid and Writeoff amo
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of procedures of each code group (grouped by first two letters of proccode) completed with claims made to a specified insurance carrier in date range by provider - Includes Count, ave fee, sum of fees, sum amount paid
DBMS version(s): MySQL 5.x, MariaDB 10.5
Labs received in the last 60 days with instructions - interval can be changed
DBMS version(s): MySQL 5.x, MariaDB 10.5
Archived and Inactive patients - with last seen date and completed procedure count
DBMS version(s): MySQL 5.x, MariaDB 10.5
Accounts Receivable WHERE payments have not been made in last X days (30 by default) - summed by guarantor, includes last statement date and last payment made
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients - with PatNum, Date of First Visit and Address
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned work that has not been saved to a treatment plan - excludes listed procedure code matches, like preventative work
DBMS version(s): MySQL 5.x, MariaDB 10.5
Questionaire results for export and analysis - Frequency distribution of answers for specified question(s)
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with appointments for a date range - (in future or past) with primary insurance carrier listed Also lists Fees, Insurance payment estimate and patient portion
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims representing service provided in a given time period for a given carrier - with phone number, claim status, Ins Est and Ins Paid
DBMS version(s): MySQL 5.x, MariaDB 10.5
Call back List for given procedures for given date range - uses guarantor home phone, other numbers can easily be added
DBMS version(s): MySQL 5.x, MariaDB 10.5
Specified procedures representing service provided in a given time period for a given carrier - with phone number, claim status, Ins Est and Ins Paid
DBMS version(s): MySQL 5.x, MariaDB 10.5
Home and wireless Phone numbers - of active, non and inactive patients, perhaps for uploading to your phone
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient Mailing info with given carrier seen since given date -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging report for no payment in last 30 days - SEE REPLICATION WARNING for versions before 14.3.1. Unusual query that includes days since last specified procedure (the procedure is 'M1112' in this example)
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients with no complete procedures - Uses Date of First Visit field.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Insurance effective dates with carriername - for active patients matching a carrier name criteria
DBMS version(s): MySQL 5.x, MariaDB 10.5
Broken Appointment Production for date range - Appointments can be moved to later in the day or another op, but do not delete the broken apt. Production is only accurate until you schedule those procedures to a new apt
DBMS version(s): MySQL 5.x, MariaDB 10.5
End of day check - For Versions 17.1 and greater. Please update your version accordingly. Returns patient name, first visit, referror, sum fee, sum adjust, sum writeoff for date range with next appointment, if none then planned or unsched or recall
DBMS version(s): MySQL 5.x, MariaDB 10.5
Incomplete procedure notes for date range with provider -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients with no appointments in date range - includes phone numbers and address
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients who have specific codes in treatment plan status - with addresses
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with date of first visit in given range - For Versions 17.1 and greater. Please update your version accordingly. With referral source and additional entry for each out referral.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Commlog entries for the day that have notes -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Commlog entries for date range that have notes -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Commlog entries with a key word -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with status of active and non-patient with no email and who referred them - For Versions 17.1 and greater. Please update your version accordingly.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all patients with dual coverage, with guarantor and names of carriers - fixed for 7.5+
DBMS version(s): MySQL 5.x, MariaDB 10.5
All patients with values for a specified patient field def name - (full or partial field def name, all results, for summary see 312)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Summary of All Patient Field Def Entries - Grouped by fieldname and value
DBMS version(s): MySQL 5.x, MariaDB 10.5
Summary of Patient Field Def Entries, for field names matching given criteria - grouped by fieldname and value
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing information with birthdate - for "Patient" status patients with a completed procedure in the last 24 months whose birthday falls in date range
DBMS version(s): MariaDB 10.5, MySQL 5.x
List of "Patient" and "NonPatient" status patients without a referral "from". - For Versions 17.1 and greater. Please update your version accordingly
DBMS version(s): MySQL 5.x, MariaDB 10.5
Show feeschedule for each patient - whether through primary insurance, patient level feesched or provider fee schedule
DBMS version(s): MySQL 5.x, MariaDB 10.5
Show count of active patients using each fee schedule - SEE REPLICATION WARNING for versions before 14.3.1. Whether through primary insurance, patient level feesched or provider fee schedule
DBMS version(s): MySQL 5.x, MariaDB 10.5
Inactive and Archived Patients who had a particular insurance carrier - Will not fit on a report page, needs to be exported as .xls
DBMS version(s): MySQL 5.x, MariaDB 10.5
Accounts Receivable and aging WHERE billing type is other than 'Standard' or other given billing type - SEE REPLICATION WARNING for versions before 14.3.1. Summed by guarantor, includes last statement date and last payment made
DBMS version(s): MySQL 5.x, MariaDB 10.5
Lists all guarantors of patients, and the family members of the guarantor who have the specified billing type with contact info - NOTE: user inputs any part of billing type, replace 'Standard' with your billing type
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments in date range for a given provider - with appointment status, age, and procedures
DBMS version(s): MySQL 5.x, MariaDB 10.5
Calculate Sum unpaid balances on procedures completed before given date - SEE REPLICATION WARNING for versions before 14.3.1. This is NOT quite like an aging report because we are considering only work completed before a given date but we account for all payments adjustments, writeoffs etc through current date
DBMS version(s): MySQL 5.x, MariaDB 10.5
Work treatment planned in a date range - Shows status (is it complete or TP) and whether accepted (scheduled or complete) or just TP and not accepted (not scheduled).
DBMS version(s): MySQL 5.x, MariaDB 10.5
Work treatment planned Today, and status (is it complete or TP) and whether accepted (scheduled or complete) or just TP and not accepted (not scheduled) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures on received claims with zero payment -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income for a particular patient (with adjustments, insurance income and writeoffs) - All by service date (which means the results change as new ins payments are received, except patient payments and adjustments, which is by payment date as there is not always a link between payment and procedure, note that this is in contrast to #162 wh
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with a given fee schedule, showing insurance information, ordered by carrier and then group name -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned total for active patients with benefits remaining who have NO SCHEDULED appointments - calender year benefits, general ins maximum only, comment address lines if you do not need. Warning - slow query.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Show count of active patients using each special 'Allowed' type fee schedule through primary insurance -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Returns information about procedures where the amount paid+writeoff is greater than fee charged patient - Now a standard report
DBMS version(s): MariaDB 10.5, MySQL 5.x
Active Patients who HAVE had procedures completed with a specified provider but not in the past two years - SEE REPLICATION WARNING for versions before 14.3.1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Fees and details for completed procedures, for a week with user defined starting date. - Filtered by specific Primary carrier name, e.g. any starting with Medicaid.
DBMS version(s): MySQL 5.x, MariaDB 10.5
All non-zero family account balances, normally use aging report, this is for troubleshooting or automated reporting. - SEE REPLICATION WARNING for versions before 14.3.1. Can also edit to view transactions that comprise an aging report.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients with scheduled or completed appointments described like "%Prophy%" within a date range, with a specifically named employer (of guarantor). - Gives name, guarantor's employer, appointment status, appt date, appt note.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed procedures in date range, with fees and provider ordered by patient. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed procs in date range, with fees summed by patient. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referrers, along with the names of patients who they have referred, within a given date range. - For Versions 17.1 and greater. Please update your version accordingly. Tells whether they are a patient (have a PatNum) or not. Set the date range and the name of the referrer (optional) at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Patients with more than one insurance. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients for a time span, with billing type. - New patient date based on completed procedure with fee>0.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Carrier Phone List for Printing, only includes carriers with active patients using them -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims for date of service date range defined by user. - This is different than the usual interval as it can cut off older claims if you want.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Show families with remaining debt FROM a previous set date. - SEE REPLICATION WARNING for versions before 14.3.1. Assumes FIFO (first in first out).
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance claim procedures with UCR fee, InsEst and InsAmtPaid. - Change date range as needed, does not distinguish between received claims, sent claims etc. Only default fees are shown, even if the UCR Fee Schedule is not a global fee schedule.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Sum of appointment lengths for time period. - Helps measure utilization.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Monthly Production and Income Report with Insurance Income & Writeoffs by Date of Service. - SEE REPLICATION WARNING for versions before 14.3.1. CAUTION This is an unusual report, read the title carefully.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding Insurance Claims (not preauths) with patient, date sent, plan number, claimfee and ins estimate for a given carrier. -
DBMS version(s): MariaDB 10.5, MySQL 5.x
Payment Report like 169 except for date range, without providers listed. - SEE REPLICATION WARNING for versions before 14.3.1. Similar to Daily Payment Report.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims of status 'Sent' or 'received' by Date of Service For a particular carrier with PatNum, Amount billed insurance, Amount billed patient and Date sent Edit Dates to change date of service range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of Hygiene Appointments and Distinct Hygiene Patients in Date Range, only a count. - Hygiene appointments are defined as appointments with one or more hygiene procedures. There are other tables joined here to allow for easy additional information.
DBMS version(s): MySQL 5.x, MariaDB 10.5
What amount of family balance is for work completed before a user provided date in the past. - SEE REPLICATION WARNING for versions before 14.3.1. Gives OldDebtBalance and OldInsuranceEstimate and allows you to set paid thru date, also shows current family balance and Insurance Estimate.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Show all transactions that comprise an aging report - SEE REPLICATION WARNING for versions before 14.3.1. IMPORTANT: accounts for writeoffs on insurance payment date
DBMS version(s): MySQL 5.x, MariaDB 10.5
Ortho or other marked patient Monthly Production and Income - SEE REPLICATION WARNING for versions before 14.3.1. Change the name match from p.LName LIKE '%Ortho%' to p.LName LIKE '%*%' If the * symbol is what you are using to indicate ortho. Counts insurance writeoffs by procedure date (PPO) For all providers
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients of a given age range with count of completed procedures(at any time) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Patients that have not been since a specific date that have insurance benefits remaining. - Includes Insurance Carrier's Name, Patient's Name, Three Phone Numbers, Benefits Remaining, TP Remaining and Last seen date. Assumes everyone has calendar year benefits. Warning - may be slow
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures WHERE code matches a pattern or is a specific code with age, phone numbers, and email. - Address can be added if needed, but will not fit on one page with phone numbers.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production by Carrier for date range (or current day) with sum of patient portion estimate - Also includes uninsured total production and writeoffs, and for pat portion, insurance estimates, sent and received claims are considered.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Empty Appointments for date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Anticipated new patient production in date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients in alphabetical order with last appt, and next future appt. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients who have had a given procedure done on a tooth but not another. Like root canal without a crown. - FName, LName and their guarantors FName and LName and address
DBMS version(s): MySQL 5.x, MariaDB 10.5
Sum of Payments for day or period and sum of patient portion for day or period at the specified clinic - Useful to see how well front office is collecting patient payments.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing List for guarantors of all patients with insurance. -
DBMS version(s): MariaDB 10.5, MySQL 5.x
Family Balance Outstanding Insurance Claim info,last claim date, last payment date, last statement date, next family appointment and family financial note. - SEE REPLICATION WARNING for versions before 14.3.1. Summed by Guarantor for families with credits.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Returns treatment planned procedures (excluding diagnostic and preventative) for active patients without a scheduled OR planned apt, who were last seen in a given date range. - Useful for those transitioning to planned appointments. (this differs FROM #56 largely in that it is date limited and lists out the treatment and does not include preventative/diag)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors of patients with benefits remaining AND treatment planned procedures, with first names of patients and addresses. - Assumes calendar year benefits
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors of patients with benefits remaining even if there are no treatment planned procedures, with first names of patients and addresses. - Assumes calendar year benefits. Uses largest annual max entered on the plan to determine FamSumMax, so FamSumMax will be wrong if the plan has a Family Annual Max entered.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients by patient status. - i.e. Active, Inactive, Archived, NonPatient, Deceased, etc.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of appointments of any status with a specified procedure attached and appointment date in the specified date range - This might be useful in relation to recall.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients with an existing appointment over a date range that has a specific attached procedure. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of future recall entries grouped by recall type (i.e. Prophy, Perio, etc) where the patient has an "Active" status. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production, procedure and appointment count by time of day (summed hourly), currently set to 7AM to 7PM, can be adjusted, based on apt start time.Production, procedure and appointment count by time of day (summed hourly), can be set to restrict the time - Does not 'spread' production through apt duration could be adjusted to show by provider. Only counts apts with procedures in them.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active Patients: Patient Name, Balance, Family Balance, SSN, Date of Birth and Last Date of Service -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with birthday in given date range who have a scheduled apt that is also in that date range with date of that apt. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled appointments in a date range, with provider and email address, home and cell phone, PreMed and New patient Flags. - Useful for Televox.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Average daily production for a time period, summed by day of week and overall. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of referred procedures in a date range that are in a given list. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily production and income report (as of version 6.8). - Counts insurance writeoffs by ins payment date, so assume that any PPOs are entered as Category Percentage. For all providers.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients without insurance with treatment planned procedures in a date range. - Includes phone number, numer of procedures, tp fee sum for date range and can easily be edited to show address for mail merge.
DBMS version(s): MariaDB 10.5, MySQL 5.x
List of Patients with an appointment associated to a specific clinic and completed in date range. - Includes contact information.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients associated to a specific clinic. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures on received claims with zero payment with carrier listed for a particular carrier. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with addresses past due for recall of type prophy or perio. - With recall type with insurance or without insurance with NO scheduled apt.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors of patients with a particular insurance where the guarantor is of a certain age (in this case 21 or over). -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Utility, missing DateFirstVisit in patient field. - A patient's Date of First Visit is usually set automatically. See "Date of First Visit Logic" on https://www.opendental.com/manual/patienteditother.html
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment Planned Procedures, one specific code. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Date and time that a claim was first sent and last sent for a patient as well as count of times claim sent. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Deleted Procedures viewer. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Income by Carrier for date range (or current day) With patient payments by type. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Returns mailing list of guarantors of patients with unscheduled Treament Planned procedures. - Leaves out diagnostic and preventative (D0*, D1*).
DBMS version(s): MySQL 5.x, MariaDB 10.5
Returns completed procedures in a date range where the charged fee does match the primary insurance fee schedule fee for patients who currently have insurance -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Names of active patients with appointments on unscheduled list. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Compare number with insurance vs number MARKED in patient table as having insurance, should match. - SEE REPLICATION WARNING for versions before 14.3.1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims older than 30 days (by sent date) not including preauths with PatNum and subscriber ID added to help differentiate between patients with same name. - Telephone number of carrier commented out but can be added back easily, some customers do not need this and still want it to fit one page width.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active Patients not seen since a given date (but have had a procedure completed at some point) who have no scheduled appointment. -
DBMS version(s): MariaDB 10.5, MySQL 5.x
Active Patients with birthday in current month, with Age, Birthdate, Name, Address, and Email. - Sorted by day of month for birthday.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with no insurance, with address -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Addresses for patients who referred other patients in a given month. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantor address for: Active patient, with insurance plan, no scheduled or planned appts, treatment planned procedures, $0 fam balance. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures completed with received claims and amount paid=0 with patient name and date of procedure, fee, amount paid. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing list with employers for guarantors of active patients, employer from patient info, including ins carriers. - Shows each carrier name for each plan that a patient is using, so guarantors may be listed more than once, can be edited to only show primary.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Confirmation list for insurance, returns appointments for given date (range), patient name, subscriber id, patients remaining 'general' benefits, carrier , carrier phone organized by carrier, no assumption of calendar year benefits - Only counts patient specific used amounts. Will use patient annual max when both family and patient annual maxes are present for plan.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures with key word in note for date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Associated recall production: production by appointment with patient name, with count of appointments for a date range, showing sum of production for all procedures in appointments containing a particular procedure code, say D0120. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with plans with a specific insurance general max. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all TP procedures with a specified priority, within a user-defined date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment List report with Day of Week added. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Customers with production exceeding given amount between two dates. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing list: Families that have been seen since a given date and have a given account billing type who have zip codes. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing list for guarantors of active patients. - Also groups by address to prevent mailings to same address with different families.
DBMS version(s): MySQL 5.x, MariaDB 10.5
All referrals in date range, with patient date of first visit, columns may be added or removed. - For Versions 17.1 and greater. Please update your version accordingly
DBMS version(s): MariaDB 10.5, MySQL 5.x
Subscribers (not necessarily patients) with specific insurance group num(s). - Gives mailing addresses for subscribers (active or nonpatient) with a group number that matches one of those in a user-defined list.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Sum of insurance income by procedure date for a date range for a given provider or providers -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Customers with production exceeding some amount (could be zero or any other amount) who were seen between two dates and are between two given ages. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients currently in the specified age range who have generated income in a date range. - Income generation is based on the payment date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Active patients seen in date range with procedure count. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen since date with user defined insurance carrier with fee schedule shown. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Hygiene daily production, completed procedures for a given date range and provider abbreviation. - Defaults to current date can be changed to show treatment planned procedure instead. To use an explicit date range instead of current date, remove comment markings from the beginning and end of the following line and remove the two lines after it.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income summed by active patient where there was some activity in time range (with adjustments, insurance income and writeoffs by insurance claim received date). -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of active patients of (or older than) a given age. - Includes patients without a birthdate as well.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active Patient count patients older than 29, completed procs in list. - This counts patients with age > 29, who have completed procedures of a certain code number. Counts matching patients without a birthdate as well.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count by age of active patients older than a given age, with completed procedures in user defined list with first visit on or after date. - Counts matching patients without a birthdate as well. More codes can be added to the user defined list of codes if desired. Change the date of @EarliestVisit to the desired date of the earliest first visits to include.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient Payments for a given patient between two dates with payment type and check number. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance payments received in one date range for procedures completed in a different date range, with both ranges specified by user. - Uses claim level service date as user may not have entered payments by procedure, so if it is a claim with multiple procedure dates, uses the claim level service date. For the payment date note that this usually but not always matches the payment date, e
DBMS version(s): MySQL 5.x, MariaDB 10.5
Sum of insurance income by procedure date for a date range for a given provider. - SEE REPLICATION WARNING for versions before 14.3.1. Also with patient payments summed by payment date CAUTION READ CAREFULLY. Collection (insurance by procedure date, patient payment by pay date).
DBMS version(s): MySQL 5.x, MariaDB 10.5
Return bad addresses for active patients that have ever been seen with last seen date, usually good to go through and call and set to inactive or fix the address. - Less expensive than cold calling or marketing, these are patients you need to contact.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients due for a recall (perio or prophy) in a given time period but scheduled after that time period. - Usefull for trying to move back up recall appointments that had been scheduled farther out.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Patient (individual not family) balances, less than zero (credits). -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients with procedures completed in the date range with and without insurance. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
See which active patients have had a given procedure completed, and what their primary ins carrier is (if any). -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Percentage of patients who have had a particular procedure in a time period relative to the total number of patients seen in that time period. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with remaining benefits, patient name, subscriber id, patients remaining 'general' benefits, carrier, carrier phone organized by carrier. - No assumption of calendar year benefits, can be used as the basis for more powerful queries.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Case Acceptance for Date Range. - We only look at work that was TP in the date range, and then see what happened to it as of NOW (today).
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production grouped by carrier and provider for procedures completed in date range where the plan type of the insurance plan upon which there is a claim is flatCopay. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production grouped by provider for procedures completed in date range where the plan type of the insurance plan upon which there is a claim is flatCopay. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total production by provider for date range and then production grouped by provider for procedures completed in date range where the plan type of the insurance plan upon which there is a claim is flatCopay. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Medical Insurance Only: List of active patients with each insurance plan (not subscribers). - Carrier Names and group names are cutoff for space consideration. Check RAW button to show both PatNum and Patient Name.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments for a given patient for a date range with payment type. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with checks for a specified carrier in a date range. - Note that you may NOT ADD the check amounts, because it is the check amount, not the payment amount in that column.
DBMS version(s): MySQL 5.x, MariaDB 10.5
How many days am I open in a date range? -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Patients that have not been seen since a specific date that have insurance benefits remaining. - Includes Insurance Carrier's Name, Patient's Name, Three Phone Numbers, Benefits Remaining, TP Remaining and Last seen date, Patient Address. Assumes everyone has calendar year benefits.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients that do not have active insurance plans. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Families with active patients in them where no active patient in the family has been seen in x months whose family balance exceeds 0. - SEE REPLICATION WARNING for versions before 14.3.1.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Treatment planned procedures with a treatment planned date in a given date range and completed procedures with a procedure date in a given date range. Limited to procedures with a specified procedure code. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient count with gross production by Zipcode, with at least 1 completed procedure in date range. - Limits to count(zipcodes)>3 as outliers, can be changed to 0 to include all. Like #178 but with date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims received in date range with summary information, fee total,claim fee total, amt paid. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients (not subscribers) with primary insurance plan listed. - Carrier Names and group names are cutoff for space consideration. Check RAW button to show both PatNum and Patient Name.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of referrals received grouped by referral and zipcode. - For Versions 17.1 and greater. Please update your version accordingly
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled or completed production with anticipated or actual writeoffs in user defined date range. - Gives net scheduled production (production - writeoffs) for a date range by appointment. Useful for setting produciton goals in offices with PPO insurance plans. Anticipated writeoffs shown after claim is created.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Summary Scheduled or completed production with anticipated or actual writeoffs (only for primary insurance , if any) in user defined date range. - Gives net scheduled production for a date range, useful for setting produciton goals in offices with PPO insurance plans. Anticipated writeoffs shown after primary claim is created.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number and percentage of patients (active and inactive) who have had a particular procedure performed. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of Procedures and Total Production by Family, ever. - Top 100, remove limit if desired.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special Statement grouped by procedure date. - SEE REPLICATION WARNING for versions before 14.3.1. Special Statement shows transactions grouped by procedure date, helps to understand a families account, alternate to going line by line through someones account
DBMS version(s): MySQL 5.x, MariaDB 10.5
Make list and count of patients with scheduled recalls, and what recall procs are in apts. - Shows first scheduled recall appointment for each patient that has one, also numbers them to give you a count at bottom.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients that have a completed procedure over a specified date range and a birthdate within aspecified month. - Specify the birthdate month that you are interested in displaying. Includes age. The results are ordered by birthdate month and birthdate day.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gross production for active patients by zip code. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Unearned income report with Payment Type and Unearned Type. - Has right-click Go To patient functionality for versions 22.1+
DBMS version(s): MySQL 5.x, MariaDB 10.5
Writeoff report for a particular procedure code, by received date range, with insurance paid amt. - Listed by procedure, includes all procedures matching code for which there is a received or supplemental claim procedure in the date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients who were seen after a user specified date and (optionally) have a user specified primary carrier. - Displays patient, last seen date, primary carrier name (if insured), hmphone and wireless phone. Also shows count of matching patients.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Income for patients who are younger than a given age, defaults to 21, in a date range. - Uses date PAID as date criteria.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients for a time span, with age at time of first visit. - New patient date based on active patients with completed procedure with fee>0.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims for all providers (not including pre-authorizations). - The total is summed by carrier. Optional property to set the number of most recent days to ignore.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of procedures over a specified date range where there is no signature associated with the procedure. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance Payments received for a specific carrier in a specified date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily production and income report. - SEE REPLICATION WARNING for versions before 14.3.1. By Service Date instead of payment date: SO IT WILL CHANGE OVER TIME as payments for past procedures are made. Defaults to all providers.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Monthly (summed by date) production and income report. - SEE REPLICATION WARNING for versions before 14.3.1. By Service Date instead of payment date: SO IT WILL CHANGE OVER TIME as payments for past procedures are made. Defaults to all providers.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Return firstname, lastname, email address for all patients that had a specified procedure completed in a given date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Return firstname, lastname, email address and patient number for all patients that had a procedure in a list completed in a given date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures of active patients with the treatment planned procedures matching a pattern. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients by Carrier who have procedures completed in date range with procedure count and gross and net production. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients by billing type who have procedures . -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with billing type and fee who have procedures completed in date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Version 7.5+ Outstanding insurance claims with date of birth and subscriber ID, by date of service (30 days or more by default) not including PreAuths. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures that are not in a scheduled apt or unscheduled list or ASAP appointment. - Limit to active patients with valid zipcode or valid email, this query repeats patient information for each procedure. Change procedure codes by editing the list near the bottom of the query.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients who have treatment planned procedures that are not in a scheduled apt or unscheduled list or ASAP appointment. - Limit to active patients with valid zipcode or valid email. Change procedure codes by editing the list near the bottom of the query.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Service Notes for patients with appointments scheduled (or ASAP) on a specified day. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and income report (as of version 7.0) summed by provider. - Counts insurance writeoffs by ins payment date, so assume that any PPOs are entered as Category Percentage. For all providers.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Calculate current or historical accounts receivable, collectible, outstanding insurance estimates for a single provider. - SEE REPLICATION WARNING for versions before 14.3.1. When compared to an aging report, the ins estimate includes ins FROM accounts with both positive and negative balances, this is like 272 but for a single provider.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Family level 'aging' for a single provider. - SEE REPLICATION WARNING for versions before 14.3.1. Note that these number will not match the account numbers as we are filtering out other providers and payment plans are ignored.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Summary of payments by payment type for date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures with received insurance claims in given date range where insurance paid zero. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure fee production and the production where a given fee schedule is used, summed by provider -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of communication log entries by type for a date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims by Date of Service for a date range not including Preauths with fee total and insurance payment estimate. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients (inactive and active) who were last seen in a given date range, who have a patient balance of $50 or less, who have no appointment scheduled with procedures in it. - This query uses balance from the patient table, which can be inacurate. Run aging, under Tools>Aging, prior to running this to avoid that.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Broken Appointment Count by Clinic (and percentage of broken compared to all completed and broken apts) with totals at bottom. - NOTE: This only includes broken apts where there is a corresponiding adjustment type where it (adj) is on same or previous date as apt where adjustment type description contains string 'broken'.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patient count by clinic - Using date of AptDateTime for appointment associated with first completed procedure as criteria for new patient, uses appointment clinic number (also see #104 for a similar query defined slightly differently).
DBMS version(s): MySQL 5.x, MariaDB 10.5
Find medical eclaims sent in date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients who have not had a particular procedure completed in date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Percentage and counts of new patients who have had additional work scheduled or completed other than D0* and D1* who were new in given date range. - SEE REPLICATION WARNING for versions before 14.3.1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of New patients with count of additional work scheduled or completed other than D0* and D1* who were new in given date range. - This will change over time as the patients may schedule work that had not previously.
DBMS version(s): MariaDB 10.5, MySQL 5.x
New Patient Report defined by FirstVisit date field, like old new patient report, can be used predictively. - For Versions 17.1 and greater. Please update your version accordingly
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance Preauths received where procedures is complete and was completed in given date range. - Shows Patient's name, procedure code, procedure fee, date received and date completed, ins pay estimate, carrier name, ordered by procedure date descending.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gross Production and Income by procedure, for a service date range and specific providers, summed by patient and service date. - Does not include adjustments or write-offs.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income for a service date range, summed by provider. - SEE REPLICATION WARNING for versions before 14.3.1. Only works if you put patient payment splits by procedure.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with 'referred from' source in given date range (determined by date of 1st visit -- not referral attach date). - For versions 16.4 and below. SEE REPLICATION WARNING for versions before 14.3.1. Sums of: fees for procs completed on day of 1st visit. Payments from that patient on day of 1st visit. Claim amts paid for procs completed on day of 1st visit.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients from each 'referred from' source in given date range (determined by date of 1st visit -- not referral attach date). - For versions 16.4 and below. SEE REPLICATION WARNING for versions before 14.3.1. With sums of: Fees for procs completed on day of 1st visit. Payments from that patient on day of 1st visit. Claim amts paid for procs completed on day of 1st visit.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedures report with amount paid to date for each procedure by treating provider. - As of 7.2 only works if you split patient payments to procedures. For all providers, leave variable like: @Provider='%%'.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing Information for patients without insurance, lists guarantors, valid addresses. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments on schedule with no attached procedures. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Balances of patients with appointments on a given date. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Decay from first exam to second exam in time period. - SEE REPLICATION WARNING for versions before 14.3.1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Family Activity - production, num of complete appts, payments. - SEE REPLICATION WARNING for versions before 14.3.1. Families with activity (complete appts or received payments) within specified date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients who have a specific code in treatment plan, with addresses. - Like 304 with date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging listed by Aging, so 90+ grouped, then 60-90 etc with phone numbers. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Monthly production and income report,with scheduled column, scheduled writeoffs are estimated writeoffs counts insurance writeoffs by procedure date (PPO) - SEE REPLICATION WARNING for versions before 14.3.1. Only counts writeoffs for completed work if a claim has been created, but counts estimated writeoffs for scheduled work For all providers, date range must all be in same month
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients on unscheduled list with all phone numbers. -
DBMS version(s): MariaDB 10.5, MySQL 5.x
Recall Reminders Sent in a specified date range. - Shows patient, reminder description, and date/time the reminder was sent. These are commlog entries with a type having the 'Recall' usage, so if any don't belong they should probably be changed to Misc or some other type, so they will not be counted as re
DBMS version(s): MySQL 5.x, MariaDB 10.5
Somewhat like 325 shows work treatment planned in a date range but that is also in a SAVED treatment plan and not scheduled. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients seen in date range with provider that did procedures where the patient has capitation insurance. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Recall info for insured patients that are due in the specified date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients who have been seen in date range who have double insurance. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patients in given date range who do not have a scheduled appointment. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Show procedures completed in a date range for patients who have a given fee schedule attached to their primary insurance, in this example Delta. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Simple sum of patient payments and sum of insurance payments for a given time span. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors of patients who have been seen in a date range for one or more of a given list of procedure codes with list of patient First Names. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors of families in which none of the patients have been seen after a given date. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gross production and income by provider. - SEE REPLICATION WARNING for versions before 14.3.1. Today, MTD, YTD.
DBMS version(s): MySQL 5.x, MariaDB 10.5
All recall appointments in date range with status. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure level payment report for those who link patient payments to procedures. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Lists of patient's carriers and employers -
DBMS version(s): MySQL 5.x, MariaDB 10.5
All active patients list with clinic and email. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient count by Gender. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Sum of adjustments by type for a given provider and date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List non zero, non-general deductibles. - Useful for finding patients with potentially erroneous benefit information.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Unscheduled appt list, with phone numbers, last seen date, and how long since last seen. - SEE REPLICATION WARNING for versions before 14.3.1. Only includes patients on unscheduled list who have no scheduled appointments.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients in 'Patient' status that do not have a scheduled or planned appointment and have procedures treatment planned in the date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance aging report. - Outstanding insurance aged by date sent 0 to 30, 30 to 60, 60 to 90 etc.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Unscheduled List with phone numbers added. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Time Card summary for all Employees. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Find deposit date and insurance check info by checknum. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance info for patients with appointments in scheduled status with a date in the specified date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims of status 'Sent' by Date of Service with PatNum, Amount billed insurance, Amount billed patient and Date sent. - Edit Dates to change date of service range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Finds procedures in a date range where the writeoff does not match the writeoffest, or the writeoffestoverride. - Edit Dates to change date of insurance payment range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Find procedures in a date range where the writeoffestoverride has been used. - Edit Dates to change date of insurance payment range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Finds procedures in a date range where the writeoff is greater than writeoffest or the writeoffestoverride, also includes remarks. - Edit Dates to change date of insurance payment range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Monthly uninsured and insured new patients over a period. - Uses Date of First Visit. See #106 for a count of uninsured and insured over date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patient Production in a time period, where the patient was new in time span and procedures were completed in time span. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Lists ALL active patients and referral source. - For Versions 17.1 and greater. Please update your version accordingly
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors in collections with positive balances. - Includes address, balance, employer, date of birth and date last seen in family, where last seen date more than 120 Days ago.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Search for Insurance Check by Check Number, includes deposit date. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Possible duplicate patients with identical first and last name with birthdate and preferred name listed. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Very Likely duplicate patients with identical first name, last name, birthdate and preferred name. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all providers who saw patients in a date range. - With date of first and last procedure completion in date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Shows patients with missing teeth. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active Patients referred in date range with ref source and phone numbers. - For Versions 17.1 and greater. Please update your version accordingly. Sorted by ref source, then patient LName.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Average Daily Production by provider. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients addresses, seen or to be seen in given date range, who have insurance. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Ins income by provider for date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures with a given string in the notes. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims by Date of Service for a date range not including Preauths. - With fee total and subscriber information.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List fees and procedures on fee schedules where the fee is higher that the standard fee (or whatever fee schedule name you specify) - Useful for finding errors or fees that you should raise to match PPO fees
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging report for patients that are insurance subscribers, with subscriber ID numbers. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims of status 'Sent' or 'Received' limited by Sent Date with Patient Name, Amount billed insurance, Amount billed patient and Date sent - Edit Dates to change date of claim last sent range
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims of status 'Sent' or 'Received' limited by Service Date with Patient Name, Amount billed insurance, Amount billed patient and Date sent - Edit Dates to change service range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedures report with writeoff column and fee-writeoff column and claim procedure status. - Does not show description or clinic can be limited by provider.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Patients with balance over 90 days AND Billing Type is "Bad Debt-Sent to collections" - Includes Patients Name, Patient Address, Phone Numbers, SSN, DOB, Patnum, Last Date of Service, Date of Last Payment, Last Amount Paid, Balance Due Amount.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Provider summed report: gross production, patient income, insurance income, hours worked (by provider time checked in appointments) for a given date range. - Change dates below, both the start and end dates are included in the results.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients in last 30 days with no follow-up appointment. Shows Primary Provider, Patient Name, $ TP Amount, homephone number and First visit date. - Could also show one provider seen on date of first visit.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging and balance summed by primary provider of guarantor. - Note: one would likely see a skew toward the default practice provider, this would be correted over time as you fix the priprov of the guarantors.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors of families in which none of the active patients have been seen on or after a given date, where no member of the family has listed insurance carrier(s) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients in date range with exam type (edit if needed), insured flag, dollars treatment planned, amount paid at first visit, and date of subsequent visit scheduled or complete, also returns a provnum for one of the procedures and the username that set - Edit as needed.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient count by Billing Type of patients that have had a particular procedure completed in a given date range. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of specified procedure codes completed in the specified date range by the specified provider, where the procedure was sent to the specified carrier - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of Hygiene Appointments and Distinct Hygiene Patients scheduled in future Date Range. - Only a count.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Duplicate Procedures in Date Range. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patients in date range with referral source, current treatment planned production, scheduled production, production completed. - For Versions 17.1 and greater. Please update your version accordingly. All amounts gross production, that is no writeoffs or adjustments considered.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients who have had their first and last visits in a date range and don't have any other scheduled appts in the future. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient income (payments made by patient not insurance company) in date range, sorted highest to lowest with age and remaining Treatment Planned total. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantor Names and Addresses of families with active patients that are not 'new families'. - Specifically where patients in family were seen in date range, like the last three years, where at least one family member was seen in that date range excluding a second date range, that is, families that are active but not new.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of New Families, specifically families where not all of the member patients of those member patients who have been seen in a given period (the active patient period, like three years) were first seen in a second given period (like the last year) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures completed in date range by other than the Primary Provider - Edit date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient visits with no (or zero length) procedure notes or group clinical notes. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active (status) patients with no scheduled apt who have not been in for a time period with the date of their last completed apt. Also shows phone numbers and whether there is pending treatment. - From @daysIntervalStart days ago to @daysIntervalEnd days ago, change the interval currently 365 days: 1 year.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Line Item Accounting: Procedures by date range with associated payments and providers, ONLY for clinics splitting patient payments to procedures. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with appointment AND birthday in given time span, with Age, Birthdate, Name, Phone and AptDateTime. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claim information for specified claim level treating provider , claim level clinic and date range. - NOTES: the reason why we say 'claim level' is because each procedure and each payment (claim proc) can be associate with a provider that may or may not be different from the claim level treating provider. Same is true of clinic.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income for a particular patient (with adjustments, insurance income by inspay date, writeoffs and balance). - By more narrow date range if desired, as is will return all income and production for patient.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income for a particular family (with adjustments, insurance income by inspay date, writeoffs and balance), also shows for every member of family and whole family. - By more narrow date range if desired, as is will return all income and production for patient.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Unsigned Consent Forms in date range. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List all insured patients with name, home and wireless phone number for the specified plan type. - Can also run for primary plans only.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Unscheduled list with sum of fees for appointment and amount insurance remaining before appointment. - Assumes everyone has calendar year benefits.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claim information for specified claim level treating provider , claim level clinic and date range splitting insurance payments by procedurecode type into two groups: xrays (or whatever) and other. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients with missing teeth, also shows age, the missing teeth and the last seen date and next appointment if any. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient name,three Patient Fields and appointment status associated with appointments of all statuses where the AptDateTime is in a given date range. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient schedule list for next day with a patient field def (each office edit to show your field(s)), apt length, age with years and months and appointment status. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Writeoff report showing tooth level info, by date range, with insurance paid amt listed by procedure, includes all procedures matching code for which there is a received or supplemental claim procedure. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List all patients with insurance adjustments to benefits, could be edited to limit by date range. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Percent of appointments by insurance carrier. - See detailed comment at the top of the query for full description
DBMS version(s): MySQL 5.x, MariaDB 10.5
Summary claim information summed by date and clinic, with sums of claim fees, insurance estimates, insurance payments and including counts of claims and patients. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Net Production (with PPO writeoffs removed by procedure date, other adjustments not considered) and Income by referral source for date range. KEY POINT: only includes referral froms and income collected during that period, whereas some other reports may s - For Versions 17.1 and greater. Please update your version accordingly. See 1270 for patient breakdown.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients that have an appointment scheduled in a date range, who also have an outstanding insurance claim - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with a status of 'Patient' that have a current insurance plan with the specified carrier. Includes insurance plan information and the patient's email address - Change the 'Delta' to any carrier name. Remove the % for an exact match, or keep them for partial match.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active Patient count by age and gender. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients who have had a broken appointment but never completed any procedures. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all appointments for active patients, with provider and carrier. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure Notes for date range including group notes. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Families with credit balances, Name, Balance and Address. - For accurate balances, run aging under Tools>Aging prior to running this report.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients listed with address and carrier who have not been seen since some date in the past, also includes patient status (normally you would engage the active patient limitation that is commented out instead of showing status) and allows carrier exclusio - In this example excludes carriers with medicaid in the name
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments made by given insurance carrier on procedures completed by given provider summed by patient. - Only considers the patients' current carriers, and payments received 'by procedure'.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients who have had a given procedure code completed with counts for that code in a given date range. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Summary claim information summed by date and clinic, with sums of claim fees, insurance estimates, insurance payments, outstanding claim fees, writeoffs, count of claims and count of patients. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Received insurance claims FROM a particular carrier showing estimated vs paid amounts. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Providers production per hour, includes gross, adjustments, writeoffs, net production and hours workedin given period for all providers. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with same first and last names with no birthdate on record. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with same first name, last name, and birthdate. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
All patients with their status and current medications. - A patient will show once for each medication.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients whose recall date scheduled is before the recall due date. - Shows patient,recall type description,due date, and scheduled date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of procedures treatment planned in date range with appointment date and time if scheduled. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned work sitting in completed appointments, normally not possible, helpful for some eCW installations. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding Medicaid Claims by Date of Service with PatNum, amount billed to insurance, and date sent. - Edit Interval number if you want to change minimum time outstanding.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients with carrier in date range, includes anticipated and no show patients. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Summary statistics by provider. - See #1820 for additional filtering for the PatCount and $Production columns.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Record of sent claims (shows each time sent or resent) for one patient. - Will not show claims that were 'undone' from Manage Module send claims window.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment Planned or Completed procedures that were treatment planned in a specific date range. - The provider attached to the procedure as well as the providers and secondary providers (hygienists) that saw the patient (by appointment) on the date that the procedure was treatment planned is also listed.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Numbered list of all referrals that are not marked as 'not person' for a given date range. - For Versions 17.1 and greater. Please update your version accordingly. Much like 27 but adds provider of both referred patient and ref source if the source is a patient. Note that it is too wide to print, so usually this one is exported or used as a mail
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients from each referral source, the amount of treatment planned work for those patients, the amount of that treatment planned work that is in a scheduled appointment, and the amount of completed work for those patients. - For Versions 17.1 and greater. Please update your version accordingly. Change date range for date of first visit.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with mailing information seen in date range that have not been seen since and are not scheduled. - With patient status and zipcode.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients seen by each clinic for the date range set. - Count is based on the number of distinct patients that had procedures completed in each clinic in the date range. Note 'No Clinic' as the clinic description means there are procs that do not have a valid clinic assigned.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Verification list for appointments on given date with carrier phone and procedures in apt. - Abbreviates to first 15 chars of carrier to save space. Like 281 but with apt time.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gross Production and total income for a date range with sum of AR, summed by guarantor's billing type. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment plan for a patient with the total amount of the treatment planned procedures. - Change the @PatNum to the patient number you would like to view.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients seen on a given day and count of cleaning, non-cleaning, and total appointments scheduled or completed after that day. - At the bottom of the query, percentages of patients seen on a given day with those appointments. Set date of original appointment to variable @DateDone at the top of the query.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment acceptance (scheduled or complete in an appointment). -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of active patients grouped by primary carrier whose date of first visit is before the EndDate. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of active patients grouped by primary carrier whose date of first visit is before the EndDate or who have never had any work completed. - Similar to #661 except includes active patients who have never had any work completed.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special aging for families with any balance that subtracts writeoff estimates from aged balances. - Includes 91-120 day column as well as >120 day column. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Net and gross production per patient for each provider over a user defined time period, writeoffs by procedure date. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients for a time span, with age at time of first visit, Dr First Seen, and Chart number. - New patient date based on first completed procedure with a fee >0.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedures report without fee. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with a positive balance, includes extra 90-180 and Over 180 column, no over 90 column. - Export to Excel or OpenOffice to view properly. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of visits by billing type for date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with service year benefits, with carrier, service month and service month filter. - Edit service year renewal month filter at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding secondary insurance claims that are on hold, by date of service. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims by Date of Service, not including Preauths for varying days since service ranges. - @StartDaysPast=30, @EndDaysPast=700 will give you 30 or more days since service, up to 700 days old.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Replication customers: Finds appointments that are in the same operatory on the same date with overlapping time. - SPECIAL USE.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients due for recall (of type prophy or perio) in date range with no scheduled apt. - Edit date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active Patients with insurance having a given Copay Fee Schedule. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for one Clinic. - Run Aging first from tools menu, usually customize for each clinic.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients listed with address and carrier who have not been seen since some date in the past, but who have a current or future date in their insurance plan(see #635 to remove this exclusion) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures for patients with specified billing type. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures that are not scheduled that were treatment planned in a user defined date range with patient, codes, description, fees and billing code. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with a positive balance, includes extra 90-180, 180-360 and over 360 column, no over 90 column. - Export to Excel or OpenOffice to view properly. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Clinic filtered Aging for families with a positive balance, includes extra 90-180, 180-360 and over 360 column, no over 90 column - Export to Excel or OpenOffice to view properly. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients (active) who have had a broken appointment with count, date of most recent, counted both ways (by adjustments and broken appointments) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of active patients by insurance plan type including no insurance -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding Insurance Claims (not preauths, only with assigned benefits) with patient, date sent, plan number, claimfee and ins estimate -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily Payments Report, broken down like built in report - See also newer daily payments queries #1653 and #1663
DBMS version(s): MySQL 5.x, MariaDB 10.5
Primary insurance info and provider seen for patients seen in date range. - Each line is a provider on a given date for a particular patient.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patient count by treating provider for date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing list of guarantors of patients with a particular insplan requires group number. - Returns the guarantors of all patients with plan, not the subscriber, also if you drop the plan it goes off the list
DBMS version(s): MySQL 5.x, MariaDB 10.5
All Open Insurance Claims (used for Trojan Professional Services) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gross production for work performed in given date range for patients referred from given referral source. - For Versions 17.1 and greater. Please update your version accordingly
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors of patients who have had a procedure in given list completed in a user defined date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Today's patients with balance,years since pano, isrecall?, #of siblings (non patients under 19 in family), # in family, email, wireless phone -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Shows all patients that had account activity on the date set, their previous balance, ending balance as of date set, every procedure completed on date set,adj,writeoffs,payments on date set, totals, MTD totals, YTD totals and A/R as of date set. - A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients from @StartDate to @EndDate with birthday in months between @StartDate and @EndDate, with Date of First Visit, Age, Birthdate, Name, Address, and Email. - Sorted by month, day for birthday.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List all families with partial or complete offsetting balances -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Waiting Room Report, shows time each patient arrived, length waiting, was seated,chair time, dismissed time, and Appt. Length. Also gives average waiting time -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures (summed by patient) for active patients that are not on the recall list and are without a scheduled OR planned apt, with phone nums. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all patients that shows if they have an appointment scheduled for this month with appointment date and time, phone number and address. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Income report from @FromDate to @ToDate day totalled, separated by specific payment type with totals -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all patients that shows Name, Date First Visit, Fee Schedule(Rarely Used), Primary provider fee schedule (UCR), and all of the patients Primary and secondary ins fee schedules -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients in the specified date range with the specified code(s) completed on their first visit - Uses D0150 in example.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding balance report with last date seen(patient), aging(family), total balance(family), billing type(family), and payplan information(guarantor) - Will show one row for each payment plan a guarantor has.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all patients that shows Name, Date Last Visit, Fee Schedule(Rarely Used), Primary provider fee schedule (UCR), and all of the patients Primary and secondary fee schedules -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients who have had an exam sheet added to their chart in a date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients that do not have any scheduled appointments but have Planned appointments, Unscheduled appointments, or are past due for Recall. - With Name, Status, Primary and Secondary Insurance
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures completed for day(or date range) with latest procedure note, group note and tooth surface(s), and whether the note and group note is signed. - Only shows unsigned entries.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient balances @AsOf date for patients that have not been seen after the user specified @CreditSince date variable. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients that had an appointment on @ApptDate in the specified clinic, by provider. Also shows if they were rescheduled at the specified clinic on that day. - This query uses the Audit Trail to track rescheduled appointments and may be inaccurate for data entered on and after version 17.3.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen on a date that have family members due for Hyg appointment with name, family member, and due date. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients in alphabetical order with last appt, and next future appt, wireless phone, home phone, primary ins carrier, and secondary ins carrier. - Where the future appt is between @FromDate and @ToDate
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with appointments for a date range (in future or past) with estimated patient balance as of appointment date. - A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed medical procedures with insurance estimate but no claim. - With proceduredate in date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments that are broken for a date range, with note, that do not have a future scheduled appointment. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients that had their first completed procedure within a date range. Showing Provider, patient name, first completed procedure date, whether the patient is insured or not, amount paid on first visit, family balance. - Next scheduled appt after first visit, specific procedures from first visit, and user that marked 09970 complete on first visit.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance - Shows values like the account module. Like internal AR report run with "Including negative balances". For versions 14.2 and before. Versions 14.3 to 16.1, use 1078. Versions 16.2 to 19.2, use 1192. Versions 19.3 to 20.5, use 1487.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance with 90 - 120. - A/R for versions 14.2 and before. For versions 14.3 and after, see query 1079.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance with 90 - 120 and billing type filter - A/R for versions 14.2 and before. For versions 14.3 and after, see query 1080
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance with last family payment date - A/R for versions 14.2 and before. For versions 14.3 and after, see query 1081.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special aging for families with any balance that subtracts writeoff estimates from aged balances. Includes 91-120 day column as well as >120 day column. - A/R for versions 14.2 and before. For versions 14.3 and after, see query 1082.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Money made fee schedules for a time period -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients whose first proc of codes D0150 or D0140 was completed in the date range, but who do not currently have a scheduled appointment - With total production, total income, ins used, ins remaining, amount of unscheduled work tp'd, and unscheduled procs tp'd
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with age, address, phone numbers, and last visit date, whose last completed procedure is not in the date range. - Whose age is also between the age range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients without any procs completed in date range and date of last completed proc -
DBMS version(s): MySQL 5.x, MariaDB 10.5
For patients seen today, balance before today's procedures, amounts from today's procedures, and ending balance with insurance pay estimates, writeoff estimates, and patient payment estimates for today's procedures. - This query will not work with versions 17.2 and newer, ProcDate field has been deprecated starting in v17.2.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment Plan report for patients with specific billing type, with Principal,Paid,DueNow,Due in 10 days, and Balance with totals -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total of family completed procedures in date range and current aging amounts with referral source and city of residence. Date range based on completed procedure date. - **NOTE: $CompletedProcs amount will be overstated if the patient has more than one referral attached** Ordered by guarantor last name then first name.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total of family completed procedures in date range and current aging amounts with referral source and city of residence. Date range based on completed procedure date. - **NOTE: $CompletedProcs amount will be overstated if the patient has more than one referral attached** Exactly like query 730 but ordered by $CompletedProcs
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of patients referred from a user specified source - For Versions 17.1 and greater. Please update your version accordingly. Date range based on referral date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of patients referred from all other referral sources other than specified. - For Versions 17.1 and greater. Please update your version accordingly. (The negative of #732). Date range based on referral date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
For day set and provider selected, the production, adjustments, and writeoffs. - With totals and a patient count, where patient is counted if there is some production on that day.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients whose first visit is before @FromDate variable, production and income from procedures completed in date range, age of patient and date of first visit. - Also included patient income and insurance income for calculating total income.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients whose first visit is between date range, production and income from procedures completed in date range, age of patient and date of first visit. - Inverse of query #735
DBMS version(s): MySQL 5.x, MariaDB 10.5
Recalled patients in a given time period, prophy or perio special type recalls only - Results could include new patients with their first visit date showing in the "DatePrevious" column
DBMS version(s): MySQL 5.x, MariaDB 10.5
All recalls with DateDue in the date range, with date scheduled, date of pat's last completed proc and ins carrier -
DBMS version(s): MySQL 5.x, MariaDB 10.5
For all procedures completed in the date range, the fee schedule attached to the claim, sum of procedure fees, sum of writeoffs, and net production=procedure fees - writeoffs - For the writeoff amount: If the claim is received or supplemental we use the actual writeoff amount. If the claim is not received or if the procedure is complete and there is an estimate but no claim has been created yet, then we use the writeoff estimat
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production, Total Production, Patient Payments, Insurance Payments, and Insurance Estimates broken down by Patient and Date of Service within a date range. With Aging of account - Report between FromDate & ToDate. Aging as of date the report is run. Meant to be exported to excel/open office. Balance information uses patient table aging; if on a version prior to 22.2, manual aging must be run before running this query for accurate
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims with a Custom Tracking type set with date of service in a date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total production by assistant over date range, ONLY COUNTS completed production in appointments -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled and unscheduled procedures for patients with a scheduled appointment in a date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Average Daily Production by provider, without hidden providers or hygienists -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance estimates aged by date of service 0 to 30, 30 to 60, 60 to 90 etc summed by carrier -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Time Card summary for all Employees for last full month. With custom multiplier -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of unique active patients seen in a date range with the specified code completed -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance info for patients with apts in date range with plannotes and custom patfield -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Waiting Room Report, shows time each patient arrived, length waiting, was seated,chair time, dismissed time, Dentist on appointment and Operatory - Also gives average waiting time in minutes
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedures report in custom data range with claim and carrier information -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and income report over date range with specific provider and billing type - Insurance writeoffs by ins payment date, so assume that any PPOs are entered as Category Percentage
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen in the last 3 years with Address, City, State, Zip, Birthdate, Date of last visit, and Primary and Secondary insurance carrier names -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding Insurance Claims older than 1 day old with PatNum,MedicaidID,Birthdate and Subscriber's SSN -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Shows all transactions for the date set, MTD totals, YTD totals and A/R as of date set. - A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Time Card summary for all Employees with days worked and avg hours per day -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients with no appointments in the last 18 months, includes phone numbers and date last seen - Set interval at top to change number of months, or to use a custom date range. Only includes patients who have had a procedure in the last 3 years.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients that had a procedure or appointment completed 18 months ago, with no appointments in the last 18 months. Includes phone numbers and last completed procedure date -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total production in a date range by procedure code from patients who have a given fee schedule - Set Date and Fee Schedule at top. Fee schedule from primary insurance if it exists, then fee schedule rarely used, and if that isn't set use primary provider fee schedule
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed appointments in a date range with total production, insurance carriers, insurance estimates, insurance payments, and amount patient paid all from the same date of appointment -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients that have active popups on their account - For versions 13.2 and older. For versions 22.2 and later, see query 1960. For versions 13.3 to 22.1, see query 1112.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production, Writeoffs, Adjustments, Net Production, Ins Payments, Patient Payments and Total Payments by fee schedule in a date range - Payments and writeoffs by procdate. Adjust date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedures report in custom date range for a specific provider with procedure fees, ins payments, writeoffs, and totals - Change date range and provider abbreviation at top. May optionally set a list of specific procedure codes, separated by commas.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Archived Patients (Guarantors) with Balances -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient payments by date of paysplit in a date range for a specific provider with total - Set provider abbreviation and date range at top. To include patient name and payment date, see 772.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production in a date range summed by procedure category. With number completed, total production, average production per procedure, and Percent of production in a date range - Set date range at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient level aging for patients with specific primary provider - Set @AsOf date and @ProvAbbr at top. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily Production and income report not counting Canadian lab fees - Counts insurance writeoffs by ins payment date, so assume that any PPOs are entered as Category Percentage. For all providers
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures treatment planned in a date range grouped by provider and procedure code, with a specified provider - Shows the amount and % of them that were completed, along with the total number completed in date range regardless of when it was TP
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedures report with amount paid to date for each procedure by treating provider - As of 7.2 only works if you split patient payments to procedures. Also shows total amount patient paid in date range. For all providers, leave variable like: @Provider='%%'. For all procedures, leave variable blank like: @ProcList=''.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient payments by date of paysplit in a date range for a specific provider with total - Includes patient name and payment date
DBMS version(s): MySQL 5.x, MariaDB 10.5
Billed amounts for capitation claims sent or received with date of service in a date range, having specific carrier name - Grouped by patient and date of service
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily production and income report with filter to show insured patients, uninsured patients, or all patients. - Counts insurance writeoffs by ins payment date. Set @FromDate and @ToDate for date range at top. Set @InsuredOnly to 'YES' to only show insured patients, to 'NO' to only show uninsured patients, and to 'ALL' to show both.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients in a date range showing procedures, fees, and providers on first appt, patient info, who they were referred from and date of next scheduled appointment. - For Versions 17.1 and greater. Please update your version accordingly.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments report with name and email - Set date range and provider abbreviation at top. Age will show as NONE if a birthdate is not set
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient information for active patients with a specific site name - Also shows carrier names for primary and secondary insurance
DBMS version(s): MySQL 5.x, MariaDB 10.5
Planned tracker with address -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients with PatNum, Date of First Visit and Address, and SubscriberID - SubscriberID column shows insurance carrier name and subscriber id pairs
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment List report with medicaid ID - Set date range at top. If no date of birth entered then it will show "None Entered"
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance with patient and insurance estimates separated - Set aging @AsOf date at top. Practice totals at bottom. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance with more aging ranges, up to >24 months - Set aging @AsOf date at top. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payments grouped by payment type and pay date - Set date range at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Capitation Utilization Report - Like internal report. Set date range and carrier name at top. Set carrier name to '%%' to run for all carriers
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients under set age with specific code completed and date of first visit before certain date. Also gives a count of all patients under set age and date of first visit before certain date. Patients with no birthdate count as age 0 - Change @EarliestVisit to the date of the earliest first visits to include. Change @AgeLessThan to be the age you want both counts to be less than. Change @ProcedureCode to the proccode you want patients in the first count to have completed
DBMS version(s): MySQL 5.x, MariaDB 10.5
Inactive patients with last visit date before specific date, that have never had specified procedure code completed - Set @LastVisitBefore at top to the date that last completed procedure should be before. Set @WithoutCode at top to the code that you want to have never been completed
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of insurance plans with patient name, subscriber name, carrier name, and patient's relationship to subscriber -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Medicaid payments for a date range with location name of the providing service - Only works for insurance plans with names that contain the word "Medicaid"
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of unique patients subscribed to Medicaid - Only works for insurance plans with names that contain the word "Medicaid"
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients and their race (Version 13.2 and above) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients for each patient status -
DBMS version(s): MariaDB 10.5, MySQL 5.x
Daily payments report - Like internal report. Set date range at top. Grouped by check. All payment types.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily production and income report - Like internal report. Set date range at top. Writeoffs by insurance payment date. All clinics and providers.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients who have not had specified procedure treatment planned or completed AND do not have any recall entries - Set procedure code at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments report with additional column showing total check amount - Set date range at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily production and income report with patient billing type and provider totals - Set date range at top. Counts insurance writeoffs by ins payment date, so assume that any PPOs are entered as Category Percentage for all providers
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients that have had one or more of the specified codes completed in the date range - Set date range and list of procedure codes at the top. Procedure codes should be separated by commas with no spaces.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients with have no scheduled apts showing patients who's last visit is between DaysIntervalStart and DaysIntervalEnd days ago. - Set date interval at top. Shows the date of last visit, date last recall, next recall due date, if they have treatment planned procedures or not, and their outstanding account balance. For static date range see #1938.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily Writeoff report broken down by procedure, with insurance payment date in date range - Set date range at top. Totals by procedure code at the bottom
DBMS version(s): MySQL 5.x, MariaDB 10.5
Top most used procedure codes for completed procedures in specific date range - Set date range at top. Set @Limit to how many results you want, like for Top 25, set Limit to 25.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of new patients in a date range, with their referral source and their overall net production. Also shows referral source summaries at the bottom with # of patients referred, and total net production of the patients they have referred - For Versions 17.1 and greater. Please update your version accordingly. Warning - may be slow on large databases. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payments report with insurance payment type - For use on database versions 14.2 and up
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily adjustment report for specific adjustment types and totals -
DBMS version(s): MariaDB 10.5, MySQL 5.x
Broken appointments with appointment date in date range showing user that broke the appointment - This query uses the Audit Trail to track broken appointments and will only be accurate for data entered in versions before 15.4. Set date range at top. See 1746 for versions after 15.4.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total unique broken appointments with appointment date in date range - This query uses the Audit Trail to track broken appointments and will only be accurate for data entered in versions before 15.4. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients who have a specific insurance or specific employer, and their interval for recall - Set insurance carrier name, employer name, and recall type at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily production and income report with totals by provider and month to date - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Show patients in the completed appointment date range who had Denti-Cal as their carrier for their insurance plan - Set date range and carrier name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled appointments in a date range with patient and carrier information - Set date range at top. EstBalance is a patient specific balance, not account balance
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families including negative balances showing InsPayEst, WriteOffEst, and Expected Payment Total - Set AsOf date at top. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding Insurance Claims report to include DaysOld date range, DateResent, and Notes - Set DaysOldMin and DaysOldMax at top. DaysOldMin should be <= to DaysOldMax
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients and their procedure codes that are marked $0.00 and unsent - Set date range at top. Only shows completed precedures with $0.00 ProcFee that have insurance estimates attached
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of deposits of the designated payment type(s) and their deposit dates - To search by multiple payment types, add a "|" and then the other payment type. Ex: @PaymentType='Credit Card|Check. Must NOT end in a "|"
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients seen with a specific carrier in a date range - Set carrier names exactly as they are entered in Open Dental, separated with commas. Start date and end date may be changed at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Current balance for families with a credit amount, last visit date, and patients in the family and their patient statuses - A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income summed by patient for date range where there was some activity in time range - Like query #421 with more fields. Often ran for 'all time' by using large date range. Set date range at top. Returns many fields so you must export to view results.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients that had a D0120,D0140 or D0150 completed in date range. Showing amount of current default treatment plan, amount completed from start of date range until specific date. - Set date range at top. Set the TPCompleteEndDate at top. Assumes calendar year benefits, general ins maximums only
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures treatment planned in the specified date range, with procedure date, appointment date, priority, provider, and procedure fee - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment list with insurance carrier billed for visit - Set date range at top.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Lab cases sent in specific date range with provider, patient name, date of birth, laboratory name, date sent and received, and lab fees - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Lab cases created in specific date range with provider, patient name, date of birth, laboratory name, date created and date received - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding Claims sent over 90 days ago for a specific carrier with Patient Name, PatNum, BirthDate, Date of Service, Procedure codes on claim, and Claimfee - Set carrier name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Lab cases created in specific date range that have not been received - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed procedures for specific clinic with insurance estimates but no claims created - Set date range, clinic, and provider abbrevation at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantor patnum, guarantor name, the last payment date and amount for the account, and account aging for all accounts - Set AsOf date at top. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payment report broken down into four sections by provider with totals - Set date range and provider abbreviations at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payment report broken down into four sections by provider with totals - Set date range and provider abbreviations at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen between two dates, that do not have an appointment (Scheduled or complete) after a specific date - Set date ranges at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding Preauths with Subscriber information - Showing preauths older than 20 days
DBMS version(s): MySQL 5.x, MariaDB 10.5
All claims sent in date range excluding pre-auths - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with NO insurance that hasn't been in since specified date - Set last visit date at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient payments made in date range, grouped by patient - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients that have scheduled appts in date range who have referred other patients - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance payments entered in a specific date range. With payment, claim, carrier, and patient information - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Every active or inactive patient who is edentulous on either arch -
DBMS version(s): MariaDB 10.5, MySQL 5.x
Net production in date range for specific set of procedure codes. Only count writeoffs if claims have been received - Set date range and procedure codes at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of procedures completed for a specified year by month, separated by procedure code - Set report year and procedure list at top. Could be slow when running for all procedure codes, run after hours for all codes the first time to know the runtime for your database.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of patients seen with specified referral source and first appointment in date range - For Versions 17.1 and greater. Please update your version accordingly. Set date range and referral name at top. Shows gross production for patient in date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with work treatment planned in date range that is not attached to a hyg appt - Set date range at top. Also set @OnlyShowNoSched to yes if you only want to see those with no TP work scheduled.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients for a time span, with age at time of first visit, Dr First Seen, Ins Carriers, and Referral Source (order 1) - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Clinic ordered aging for families with a positive balance. Shows totals for each clinic and grand total - Set AsOf date at top. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients 8 years or older that do not have either an FMX or PANO recall entry, showing medical summary -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled appointments in a date range with production, insurance carriers, and insurance estimates for procedures on those appointments - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance claims representing service provided in a given time period for a given carrier, SSN, Subscriber ID, BirthDate, claim status, Ins Est and Ins Paid - Set date range and carrier at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and income report in date range totalled by clinic and provider - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled appointments in specific operatory. Can specify to filter by hidden or visible operatories. - Specify Operatory name at top. Set OperatoryVisibility at top. Acceptable inputs are 'HIDDEN' and 'VISIBLE'
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding claims for a particular family. Set guarantor at top - Set guarantor name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments completed in date range, showing appointments that were scheduled on that same day for a future date - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Avg turnaround time in days for claims grouped by carrier. Shows claims with DateSent and DateReceived in date range - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims with date of service in date range, that have an insurance payment within 28 days of date of service - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Monthly Production and income report for a specific provider with a medicaid payment column - Set date range, provider abbreviation and medicaid carrier name at top. Set @WriteoffByDateOfService to 'YES' to use writeoffs by date of service instead of payment date.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Insurance carrier list showing hidden carriers -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of unique patients seen in a date range broken down by day - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Audit trail deleted adjustment entries in date range - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Unsent Claim Information -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with negative balance with address information - Set AsOf date at top. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with $1500 or more in production and payments in date range with contact information - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with appt schedule or complete in date range that have $1500 or more in payments over entire practice history - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of appointments that were created and broken in the date range, broken down by hour. - This query uses the Audit Trail to track broken appointments. Set date range at top. Only counts appointments created/broken in date range between @StartTime and @EndTime. Currently set to 9 AM and 3 PM.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total fee billed to each insurance carrier from claims with service date in date range. - Set date range at top. Does not count preauths.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment plans created in date range - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Callback information for appointments completed yesterday - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Race/Ethnicity totals for active patients by clinic - For Open Dental versions 14.1 through 16.3. Only counts active patients.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance (Estimates or Paid) for a month (by date of service) showing insurance payment date - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed Procedures in date range with $0.00 fee amounts, showing standard fee -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients seen in the date range by specific provider who's age at time of service is within the age range - Set provider, date range, and age range at top. Useful for EHR reminders stage 1.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance estimates with a date of service in date range that have a different pay entry date from the payment date - Set date range at top.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Appointments in date range with no procedures attached - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referrals entered in date range, with date first appointment, and date next appt - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active Patients with birthday in date range, with Age, Birthdate, Name, Address, and Email. - Set date range at top. Sorted by day of month for birthday. Does not show patients without email set
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with date last seen in date range with address information - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Unscheduled appointments with original scheduled date in date range - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with no referrals entered (to or from) before @AsOf date - Set AsOf date at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Saved and signed treatment plans generated in the date range that contain procedures with specific PriorityName that have not yet been completed - Set priority and date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of active patients by primary provider -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen in date range and which provider they saw (based on completed procedures), with ability to specify provider - Set date range and provider at top. Does not show hygienists
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients seen in date range (by completed procedure) - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with specific fee schedule set as Fee Schedule (rarely used) in the patient edit window - Set fee schedule at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Email addresses of active patients seen in date range with date last seen in date range, with next sched appt - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients referred out in date range - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedures report with procedure category - Set date range, procedure code, and provider abbreviation at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Collection ratio for given month and year - For version 14.3 through 16.1. Set report month/year at top. For more information on this report, see the bottom of https://opendental.com/manual214/productionincome.html
DBMS version(s): MySQL 5.x, MariaDB 10.5
Collection ratio for given month and year - For versions before 14.3. Set report month/year at top. For more information on this report, see the bottom of https://opendental.com/manual214/productionincome.html
DBMS version(s): MariaDB 10.5, MySQL 5.x
Patients that have had one or more of specified codes completed in the date range - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of procedures of each code, matching specified code/code part entered, that are completed in date range by specified provider abbreviation entered. - Set date range at top. Can run for all codes and providers.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with appointments in date range with current total family balance - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payplan principal totals by guarantor - For Open Dental versions 16.1 and earlier
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a value entered for a specific patfield - Set patient field name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with specific active problem seen by provider in the date range - Set problem name, provider abbrevation and date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with specific active medication seen by provider in the date range - Set medication name, provider abbreviation and date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of patients seen in date range with a specific billing type - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Office patient counts - Counts patients with scheduled recalls, patients seen in last two years, active and inactive patients, and of patients with any scheduled appointment.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Office A/R totals at the end of each month for 12 months from the AsOf date - SEE REPLICATION WARNING for versions before 14.3.1. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients report with clinic totals and grand total - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top. Does not consider missed and cancelled appointment codes (D9986/D9987)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Clinic ordered aging for families with a positive balance. Shows totals for each clinic - Set AsOf date at top. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Clinic ordered aging for families with no outstanding claims and a positive balance at least 60 days older than the AsOf date. Shows totals for each clinic - Set AsOf date at top. A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment list separated by clinic in date range with appointment count - Set date range at top. Length is shown in minutes.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging report with guarantor preferred name, date of last patient payment, and sum of patient payments made on last patient payment date where the account balance is greater than 0 - RUN THE AGING TOOL BEFORE RUNNING THIS QUERY
DBMS version(s): MySQL 5.x, MariaDB 10.5
Counts the number of completed appointments per provider or hygienist for appointments completed in a date range - Set date range at top.
DBMS version(s): MariaDB 10.5, MySQL 5.x
List of referred out patients by doctor, including referral specialty and status, and the referred out procedure information - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of invoices in a date range showing patient information and file name - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of procedures treatment planned for the current date for each patient, and how many of those procedures are scheduled in an appointment for today or in the future - Only counts treatment planned work. Will not include procedures that are planned and completed on the same day. NumProcTPSched only counts procedures scheduled for an appointment Today or in the future. Only appointments with statuses Scheduled or ASAP.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment list separated by clinic in date range with appointment count by provider and clinic - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance claim procedures with UCR fee, InsEst,EstPatPortion, and InsAmtPaid in a date range, with specified treat provider and can exclude certain ProcCodes - To add multiple ProcCodes to be excluded, add a pipe "|" without quotes and then the code number. Ex: @ExcludeProcCodes='D1100|D3224|D1102' will exclude 3 codes. If you have 1 ProcCode to exclude, make sure there are no pipes, or the query will fail. Se
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors of active patients who have been seen in the date range where the guarantor has mailing address information filled out. - Does not show patients with an asterisk in their last name. Does not show guarantors with a blank address,city,state,or zip. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Sheets created in a date range with specific sheet descriptions - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients seen by provider, split by primary ins carrier at time of service in date range. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed procedures in date range that have more than one 'CapCom' status insurance estimate - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production per Operatory for appointments in the date range. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients seen in a date range with age, race, carrier, number of visits and other information, for FQHC grant - Patient Name, Age, Number of Visits (a visit is a unique date of service), Patfield ('Sliding Scale ' in this example), Carrier Name (primary if any) and Race for patients seen in given date range. Race will only be accurate for Opendental versions 16.3 a
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen by practice and specified provider, for FQHC grant - # of Patients seen by practice, # of Patients seen by specified provider, # of visits by practice, # of visits by specified provider
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of patients per billing type. Includes patients with specified patfield as its own billing type, for FQHC grant - The specified patient field will show as a billing type in this query (Sliding scale fee for this example).
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of broken and completed appointments, individually and summed, also shows percentages of broken and completed appointments, for FQHC grant - Total appointment count (completed and broken appts), number of completed appointments, number of broken appointments, % of completed appointments, % of broken appointments. Date range based on appointment date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of procedures completed grouped by specific procedure code ranges in date range, for FQHC grant - Shows Procedure type, Number of procedures completed. Uses specific procedure types based on procedure code ranges provided by the requesting practice, the ranges may need to be changed. Date range on procedure date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payments report for specific clinic - Set date range and clinic at top.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Count of each procedures code completed in date range by a specific provider - Set date range and provider abreviation at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients without specific code completed in the date range showing date last completed appointment - Set date range and procedure code at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient payments by date of paysplit in a date range for a specific provider with total. Only shows when paysplit date is different than payment date. - Set provider abbrevation and date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding Insurance Claims sent in date range. Only shows claims for PPO insurance plans. Does not show PreAuth claims - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients that have treatment planned procedures -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance and patient income for payments received during the date range summed by carrier name and employer name. SEE NOTE - Counts patient income for the carrier/employer combination if: 1.) There are any insurance estimates or claims for a procedure with a procedure date in the date range for that patient or 2.) Insurance Plan had a payment in the date range for that patient.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed procedures attached to claims with carrier name and subscriberID. - Will show multiple rows for a procedure if attached to two different claims. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment list with patient info - Does not show unscheduled or planned appointments. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment list with patient and provider info - Does not show unscheduled or planned appointments. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance payments with payment date in date range - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Capitation Utilization Report with plan info - Like 784 with more info. Set date range and carrier name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients seen in date range, and how many of those seen were new patients - Patients seen and new patients based on completed appointment. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of claims with total ins payments entered -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedures report with procedure category, birthdate and tooth surface - Like query 883 with more info. Set date range, procedure code, and provider abbreviation at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims sent or received with service date in date range for specified carrier. Does not include pre-auths - Set carrier name and date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled appointments in a date range with production, insurance carriers, and insurance estimates for procedures on those appointments - Like 845 with more info and totals. Set date range at top.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Daily payment report grouped by patient - Like internal report. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with In-Person statements generated in date range - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Name and address information for guarantors of the last 3000 accounts seen -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of active patients grouped by employer set on the patient -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referrer list with address and phone information only showing doctors -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Finance charges added today -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of total appointments completed in date range, and how many of those had a D0120, D0145, or D0150 - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of referred out patients by doctor, including referral specialty and status, referring provider, and the referred out procedure information - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all supplies -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Statements with date in date range for specified statement modes - Set date range and staement mode at top. Leave @Mode as empty quotes like '' to include all modes. Mode options are 'Mail', 'InPerson', 'Email', 'Electronic'.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen at specific clinics in date range that did not have insurance at time of service - Set date range and clinic name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with completed procedures in date range showing summary fee, writeoffs, writeoff estimates, ins payments, ins estimates, and patient portion - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims - Set days old min/max, provider, and includepreauth at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment Plan report for patients with specific billing type, with Principal, Paid, DueNow, Due in 10 days, Balance, and DaysPastDue with totals - Set billing type at top. For version 16.2 and greater. Inaccurate for Dynamic Payment Plans.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and income report showing daily, monthly and fiscal year to date totals - Set AsOf and FiscalYearStart date at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of appointments completed, scheduled or ASAP created in date range that contain a specific procedure code - Set date range and procedure code at top.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Account aged balances summed by primary provider of guarantor with totals. Only counts positive balances - Based off current account balances.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of patients with claim sent or received in date range for specific carrier - Set date range and carrier name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with claim sent or received in date range for specific carrier - Set date range and carrier name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients with completed procedures in date range by clinic - Set date range at top. A patient may count twice if seen at two different clinics, so % may not add up to 100
DBMS version(s): MySQL 5.x, MariaDB 10.5
Wiki page creators and most recent editors. Searchable by username and page title - Set usernames and page title at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment plans report. Only shows not completed payment plans. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all patients that had account activity on the date set - See note in query.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments in date range with user that created the appointment - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment amounts made in date range summed by the payor and who the payment was split to - Set date range at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total insurance income with check date in date range broken up by carrier and InsPaymentType - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with specified procedure code completed in date range, with no future scheduled appointment with specified procedure code - Set date range and procedure code at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment list report showing appointment notes - Set date range at top. May need to be exported to excel to see full note
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims with claim fees separated into aged columns - Change IncludePreauth at top if you want to show preauths. This report is very wide, use 965 for a more compact version.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims with claim fees separated into aged columns - Change IncludePreauth at top if you want to show preauthorizations. Like 964 but not as wide.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with first completed procedure in date range showing current primary ins carrier - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients who have treatment planned procedures showing total amount of proc fees and total amount of unscheduled treatment planned proc fees - Set date range at top.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Adjustments of the specified type with adjustment date in the specified date range associated with the specified providers - Similar to Daily Adjustments report. Includes current primary insurance carrier
DBMS version(s): MySQL 5.x, MariaDB 10.5
Unscheduled Appointments in a date range. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment acceptance (scheduled or complete in an appointment). Also shows total fee amounts. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment Not Finalized Alert Query - Replaces query # 958. Use this query to find claim payments that are not finalized or insurance checks entered without matching claim payments attached. Note: Will run for all time, useful for cleaning up past insurance checks and claim payments that may
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients with first appointment in date range - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
EHR query - Percentage of Patient Encounters by NPI - Percentage of patient encounters by insurance carrier, for a date range, by NPI. Use to determine Medicaid patient volume for a specific time frame. The percentage may add up to over 100% if a single patient was seen by multiple carriers on the same DOS
DBMS version(s): MySQL 5.x, MariaDB 10.5
EHR query - Percentage of Patient Encounters for all Providers - Percentage of patient encounters by insurance carrier, for a date range, for all providers. Use to determine Medicaid patient volume for a specific time frame. The % may add up to over 100% if a single pat was seen by multiple carriers on the same DOS
DBMS version(s): MySQL 5.x, MariaDB 10.5
EHR Query - Percentage of Patient Encounters - Recent Conversion - For providers who had a recent data conversion. Percentage of patient encounters by insurance carrier, for a date range, for all providers. Use to determine Medicaid patient volume for a specific time frame. See note in query for more details.
DBMS version(s): MySQL 5.x, MariaDB 10.5
EHR Query - Split by Encounter - Claim Details by NPI - Claim details for a date range, by NPI. Use with a Percentage of Patient Encounters query to see patient claim information.
DBMS version(s): MySQL 5.x, MariaDB 10.5
EHR Query - Split by Encounter - Claim Details for all Providers - Claim details for a date range for all providers. Use with a Percentage of Patient Encounters query to see patient claim information.
DBMS version(s): MySQL 5.x, MariaDB 10.5
EHR Query - Split by Encounter - Claim Details Recent Conversion - For providers who had a recent data conversion. Claim details for a date range, for all providers. Use with a Percentage of Patient Encounters after a Conversion query to see patient claim information.
DBMS version(s): MySQL 5.x, MariaDB 10.5
EHR Attestation, Texas Medicaid. "Supporting Documentation for Patient Volume" - For EHR providers who must provide supporting documentation for patient volume when attesting with Texas Medicaid.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with procedures scheduled on a specific date with all of their scheduled/unscheduled procedures listed. - Set date at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production totals for additional products, such as tooth brushes, sold by office in a date range with provider listed and fees totaled at the bottom. - Set date range at top. Set procedurecodes for additional products at top, separated by a "|" character. Must not end with a "|".
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment plan for a patient with the total amount of the treatment planned procedures. - Change @PatNum to the patient number you would like to view. Includes areas for patient signature and date at bottom.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special claim breakdown report. Shows number of claims submitted, total fees submitted, total insurance estimate, total insurance collected, insurance not paid, %not paid, and claims still open. - Set date at top. Do NOT modify @EndDate. Columns broken down by week using the service date listed on the claim. Will show the past 3 months. Open claims are calculated by taking the # of claims that have the S (Sent) status.
DBMS version(s): MariaDB 10.5, MySQL 5.x
List of active patients divided into age group of 0-36mo, 37mo-6yr, 7-14, 15-21, 22-40, 41-65, >65, with percentages for those age groups. - Do NOT modify @ActivePatCount.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of new patients with a completed procedure in date range. Also shows a count of patients with a scheduled appointment and no completed procedure in date range. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with appointments on a given date, with patient/family balance and the dates of patient/family outstanding claims that have a DOS > 30 days from the given date listed. - Set date at top. This query might take a few minutes on large databases because of aged balances. PatClaim dates will also show in FamClaims column.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of insured patients with a scheduled appointment in date range, with Name, PatNum, and FamFinUrgNote listed. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients that have had at least one procedure from a list of procedure codes completed in a date range, group by the day of the week the procedure was completed. - Set date range and procedure code list at top. Put dates in the format 'YYYY-MM-DD'. Enter list of codes separated by commas. Leave blank for all codes.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed appointments in a date range with the total production, insurance carriers, insurance estimates, insurance payments, amount patient paid on date of appointment, and patient next visit date listed. - Set date range at top. Similar to 761 but with the next appointment date and provider subtotals added in.
DBMS version(s): MySQL 5.x, MariaDB 10.5
GroupName and Production of a user-specified insurance plan with average production per patient with that insurance plan listed. - Set date range and carrier name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
All patients that have values for a specified patient field def. Shows the total ProcFees of all treatment planned procedures per patient. - Set patient field def name at top, or use '%%' to get all patient field def names.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Percentage of male, female, and unknown patients seen within a date ranged grouped by clinic. - Set date range at top. Information regarding clinics is taken from the clinic on the procedure.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients in age range who have had a procedure completed in the date range per clinic, grouped by age. Count of total patients grouped by clinic at bottom. - Set date range at top. Patients' ages are calculated based on their age at the time of their procedure. If a patient has come in multiple times during the date range and has had a birthday between procedures, they will count once for each age group.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients who have had a completed procedure in the date range with their clinic and race/ethnicity listed. Count of patients per clinic grouped by race/ethnicity below, and grand total of the number of patients per race/ethnicity at bottom. - Set date range at top. Patients with multiple races/ethnicities specified will have a comma-delimited list at the top, and will count once for each race/ethnicity in the totals.
DBMS version(s): MySQL 5.x, MariaDB 10.5
ProcDate, Provider, Clinic, ProcCode, Fee, and InsPayEst for completed procedures in a date range of a specific provider and carrier. - Set date range, providerNum, and carrierName at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Days scheduled as open, days worked, and the total number of days in the date range. - Set date range at top. Days worked is calculated based on being scheduled as open and having at least one completed procedure for the day.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of completed procedures in date range. Includes patient name, patient phone numbers, and treating provider. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Deposits made for a specified date range. Showing clinic and payment type attached to the deposit. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled appointments in a date range showing production, insurance carrier, and insurance estimates for procedures attached to the appointment. - Set date range at top.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Daily procedures report, showing tooth quadrant information. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily production and income report showing payments, adjustments, and patient income. - Set date range at top. Total insurance writeoffs by ins payment date. Assumes that any PPOs are entered as Category Percentage. Runs for all providers and ordered by specified procedures
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Quick Notes Ordered By Category -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Treatment Planned Procedures by provider in a date range - Set date range at top. Excludes procedure codes D1000-D1999 and procedure codes in the categories: 'Never Used', 'Obsolete', and 'Products'.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a particular insurance type, seen by a specific provider, within a date range. - Set date range, carrier, and provider at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with an appointment today, showing if that appointment has any recall triggers. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with completed appointment in specified date range, whose birthdate falls between specified dates - Set date ranges at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Day sheet for date range showing production, income, and visits by provider with provider totals. Like Windent Day Sheet Summary Listing report - Set date range at top. Writeoffs by procedure date. Only shows insurance payment amounts that are attached to insurance payments. Visits based of completed appt. Visit may not show in breakdown if nothing else was done that day for that provider, but
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of appointments for specific provider in date range showing info on next scheduled/ASAP appointment if any. - Set date range at top. If the appointment IsHygiene it will show the Hygienist, otherwise shows Doctor on appointment. All user information comes from securitylog entries
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of guarantors of accounts with current balance for entire family showing as a credit. Date restriction is the last completed procedure for any family member. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a "referral from" source and their first completed procedure in date range showing list of procedures with procedure date in date range - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top. Set referral last name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of allergies with a specific allergy description. Also shows patient information. - Specify allergy description at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all TP procedures that have priority not in specified priority list, treatment planned in date range. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Like internal Adjustment Report but filtered for adjustments greater than specified amount. - Set date range and clinic at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient and Insurance Checks for a Date Range - Set date range and provider(s) at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Monthly P&I Report with Medicaid payment column - Set date range, the provider abbreviation, the name of the Medicaid carrier, writeoff by date of service, and billing type at top.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Email and home address of active patients without insurance -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Problem breakdown by age -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Custom Daysheet report. Shows Fees, payments, and adjustments by provider with subtotals, grand totals, payment type breakdowns, and Current/MTD/YTD/PrevMonth info. - Set DaySheetDate at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient field values with last Commlog and Task - Set patient field fieldname at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total production and insurance payments for completed procedures - Set date range, procedure code, and provider abbreviation at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income by date of service with aging and next appointment - Set date range and aging date at top. Total and aged balance columns only accurate for Open Dental versions earlier than 14.3.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure fee totals by procedure code and provider - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production, writeoffs, and adjustments per procedure - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments creation information - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients first appointment with Production and Income, Treatment Planned Production, Net Production since first visit, referral source, date of last Preauth, and number of completed appointments - For Versions 17.1 and greater. Please update your version accordingly. Set date to show appointments after at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Recall info for patients without future scheduled appointments - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production, procedure and visit count per provider - Set date range and provider abbreviations at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily production and income report with writeoffs by procedure date with count of patients and average net production per patient - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payments grouped by payment type and pay date with subtotals and grand total - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special daily report. Shows patients seen and reappointments by provider. Also shows procedures completed and total hygiene production - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referral Tracking Procedure information - The same as the internal Referral Tracking Procedure report. Set date range at top.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Procedures not billed to insurance by specific provider - Like internal Not Billed to Insurance report but includes a provider filter. Set date range and provider name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production of first visit, production of treatment planned for first visit, and the amount paid on the first visit - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedure report. Shows count of TP'd procs in date range, the % of them that were completed, count of D1330 TP'd, and % of D1330 completed - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special monthly breakdown report. Shows the count of new patients, distinct patient visits, net production, income, and number of broken appointments per month for date range - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special aging report. Shows normal aging totals and breaks down insurance estimate and patient portion into aged columns. - Set date to run the report for at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedure report showing provider. - Like internal grouped by procedure and provider. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Current balance for families with a credit amount, billing type, last visit date, and next visit date - For versions 14.2 and before.
DBMS version(s): MySQL 5.x, MariaDB 10.5
"Patient" status patients whose last completed procedure was on or prior to the specified date, who have insurance with the specified carrier, and who have no future scheduled appointments - Set date since seen and carrier at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Amount collected from original referral source using user-defined field - Set patient field name and date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Lab cases that are unattached to an appointment or attached to an appointment that is not complete -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active (status) patients with no scheduled apt who have not been in for a time period with the date of their last completed apt. *Modified to exclude certain procedure codes, show the total fees for completed procedures, and show the total fees for treatm - Set the codes to exclude and the date interval at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Return the names of all patients of a specified age with a specified procedure code completed by a specified provider within a specified date range - Set patient age, procedure code, provider abbreviation, and date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Displays production, payments by carrier, writeoffs, and the carrier's percentage of patients for each insurance carrier - Percent of pats on plan calculate based on all patient records including deleted, deceased, archived, non-patient, and patient clones.Not including Capitation. Production will count multiple times if proc sent to multiple carriers
DBMS version(s): MySQL 5.x, MariaDB 10.5
Shows the count of the patients seen by a provider within a date range by age, race, insurance, and sliding scale. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of claims that have not been received. Shows the date sent, date service, carrier information, claim status, claim fee and ins payment estimates. - Set date to run the report for at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Net production and writeoffs by insurance carrier - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily report that shows each providers new patients, broken appointments, daily gross production, daily insurance income, daily patient income, daily total income, month to date production, and month to date income. - Set date to run the report for at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of appointments scheduled for tomorrow with estimated insurance and production - Set clinic and date if not running for tomorrow at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total writeoff amount by carrier with writeoffs for each patient on that carrier - Set date range at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient level aging for patients by treating provider/provider on the payments/provider on the adjustments - Set provider abbreviation and date to run the report for at the top. For version 15.2 and before. For versions 17.3-19.1, use query #1311
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients' date of first visit, whether they have insurance, the sum of TP procedures, the amount they paid on the first visit, and the date of the next visit - Set patient field name and date range at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of broken and completed appointments, also shows percentages of broken and broken appointments for patients under age 18 - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of each insurance plan (not subscribers), with plan note -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures that had the same procedure done multiple times on different dates on the same tooth. - Set the date range and the codes considered to be restorative at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a referral in the date range with next scheduled appointment - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Detailed recall list. Shows all the fields in the internal recall list plus carrier, patient status, and date of last appointment - Set date range, sort method, number of reminders to show, provider abbreviation, clinic, and site at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of specific procedures by location, which is an appointment field name. - Set date range and location name at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Shows a breakdown of procedures per location using an appointment field name Location - Set date range and location name at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Custom Day Sheet Summary Report. Displays MTD Productions, Collections, Adjustments, A\R Impact, Total Walkouts, Estimated Insurance, Collection Ratio, Patients Seen, New Patient Count, Average Production Per Visit, and Average Collection Per Visit - Aging in this query does not consider payment plans. Set date range and clinic at the top. For practices without clinics, see 1110.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total of patient payments by payment type and date of payment - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gross and net production of appointments by the users that created the appointment in date range with specified procedures excluded - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients with completed appointments in date range. Shows how many of those patients have a future appointment in the next 7 months, and whether or not that appointment was completed, broken down by provider. - Future appointment statuses include scheduled, ASAP, and completed. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients and their gross income for specific referral source. - For Versions 17.1 and greater. Please update your version accordingly. Set date range and referral source name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special Production and Income report showing total collections, hygiene collection percent, provider collection percent, new patients, and gross prod per new patient. Also shows provider breakdowns for Production and Income. - New patients are calculated by Patients' DateFirstVisit. Perio Procedures for this report are D4000-D4999. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special day sheet broken down by provider. Shows gross prod, w/os, adj, income, new pats, pats seen, prod/pat, and total ins/wo/pat portion. - Also shows current/MTD/YTD/LastMonth statistics. Set sheet date at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Monthly P&I report for a specific provider. - Set date range and provider at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of procedures TPed in the date range. Will only show procedures still TPed. Also shows if they are scheduled, as well as some appointment information. - Includes production totals by scheduled/unscheduled procedures. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of outstanding PPO claims with a specific clinic. - Set clinic name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures not billed to insurance report filtered by Clinic. - Set clinic name and date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed Procedures in date range with counts for individual procedure codes and percentage of individual procedure code/total procedures completed. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Custom A/R report with extra aging columns for families with members that have not had a completed procedure since a specified date. - Credits and charges are both aged into the columns based on date. Set @LastProcDate and @AgingAsOf at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payments report like internal report without provider split. Similar to 792. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient, total fees, and claim information from completed Appointments within a specified date range with claims from a specified carrier. - If multiple claims have been submitted for an appointment and more than one match the specified CarrierName, PatInsCarrier will appear as a comma-delimited list. Date range is based off of the Claim's DOS. Set Date range and carrier name at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and income report like internal report with writeoffs by insurance payment date and no day splits. Similar to 793. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance - Shows values like the account module. Like internal AR report run with "Including negative balances". For versions 14.3 to 16.1. Versions 14.2 and before, use 718. Versions 16.2 to 19.2, use 1192. Versions 19.3 to 20.5, use 1487. Versions 21.1 and higher
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance with 90 - 120 - A/R for version 14.3 to 16.1. For versions 14.2 and before, see query 719. Versions 16.2 to 19.2, use 1192. Versions 19.3 to 20.5, use 1487. Versions 21.1 and higher use 1655.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance with 90 - 120 and billing type filter - A/R for versions 14.3 and after. For versions 14.2 and before, see query 720.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance with last family payment date - A/R for versions 14.3 to 16.1. For versions 14.2 and before, see query 721.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special aging for families with any balance that subtracts writeoff estimates from aged balances. Includes 91-120 day column as well as >120 day column - Special aging query. Read comment in the query itself carefully. A/R for versions 14.3 to 16.1. For versions 14.2 and before, see query 722.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Commlog search by note text and PatNum - Set note text and PatNum at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Displays a list of subscribers for a specified insurance carrier - Set the the beginning of the carrier name at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance plans where the subscriber is different from the patient and the relationship is self -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing information for guarantors with invalid zip codes -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed procedures not associated with an appointment - Set date range at top (you might have to extend the date range to catch completed procedures attached to future appointments)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients not assigned to a clinic - Active patients only
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled or broken appointments with completed procedures -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed Appointments with treatment planned procedures -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims with Insurance Payment attached that has the Status as not 'Received' - Set date range for the claim date sent at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
UDS Report for FQHC Dental Sealant Measure. - Set reporting year at top. Patient birthdates and date ranges are calculated automatically. For Open Dental versions prior to 17.1, when the Standard Report "FQHC Dental Sealant Measure" was added to the program.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance including aging of credits - Set date for which to calculate aging at the top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Accounts with a positive balance with at least two statements sent since their last payment -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patients report showing the next appointment's date, procedures codes and fees - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top. Date range is for the patients' first completed procedure.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Work treatment planned in a date range with subtotal for each status - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients gross production, net production, and collection in date range by zip code - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top for when production and collection is calculated
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with their referral source, appointment dates, net production, total TP procedures, total scheduled procedures, and total unscheduled procedures - For Versions 17.1 and greater. Please update your version accordingly. Set date range at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
All open claims as of a chosen date, broken down and subtotaled by clinic - Set date for which claims are open at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Visit summary by day. Displays the patients' name, SSN, DOB, DOS, fee, payments, and provider info - Set date range and carrier at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure notes and group notes containing specified text. - Set date range, clinic and specified note text at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of cloned patients (patients who have ALL CAPS FNames and LNames.) - May be useful to ortho practices to count ortho patients.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of active cloned patients (patients who have ALL CAPS FNames and LNames.) - May be useful to ortho practices to count active ortho patients.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active cloned patients (patients who have ALL CAPS FNames and LNames.) - May be useful to ortho practices to who want to see all active ortho patients.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Balance owed by cloned patients (patients who have ALL CAPS FNames and LNames.) - May be useful to ortho practices to see balance for active ortho patients.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Case-sensitive patient searching by patient name. Shows all patient information for any matching patients. - Edit the patient LName and FName fields at top by replacing the template names between the quotes. Leave like '%%' to NOT filter by that category.
DBMS version(s): MySQL 5.x, MariaDB 10.5
All future appointments in a specified operatory. - Can be useful when you want to hide an operatory. Specify OpName at top. Leave like '%%' to run for all operatories.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Day Sheet Summary Report for practices without clinics. MTD Productions, Collections, Adjustments, A\R, Walkouts, Est Insurance, Collection Ratio, Pats Seen, New Pat Count, Avg Production Per Visit, and Avg Collection Per Visit. - Aging in this query does not consider payment plans. For practices with clinics, see 1061. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment Finder Report - Like internal. Set options at the top. Unlike 1221, this report does not take into account categories that are excluded from the annual max nor does it take adjustments to benefits into account.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients that have active popups - For versions 13.3 to 22.1. For versions 22.2 and later, see query 1960. For versions 13.2 and older, see query 762.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with a positive balance as of user defined date. - A/R for version before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gender and age breakdowns for patients seen within the specified date range. - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Orthochart information for patients who have an appointment within a date range with a specific provider. - For all patients with appointments in the date range. Set date and provider abbrs at top. You must have at least one Orthochart field defined. IT IS VERY IMPORTANT THAT THIS QUERY BE RUN IN ITS ENTIRETY AND NOT BROKEN INTO SECTIONS. For OD versions < 21.2
DBMS version(s): MySQL 5.x, MariaDB 10.5
Orthochart information for the patient specified at top. - Like 1115 but for a specific patient. Edit patient number at top. You must have at least one Orthochart field defined. IT IS VERY IMPORTANT THAT THIS QUERY BE RUN IN ITS ENTIRETY AND NOT BROKEN INTO SECTIONS. For Open Dental versions below 21.2.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed appointments in a date range with total production, office fees, insurance carriers, insurance estimates, insurance payments, and amount patient paid on date of appointment - Set date range and fee schedule at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed procedures in date range, with fees, billing type, and whether the patient is new - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Medical patients seen by a provider - Set date range and providers at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Displays the count and percentages of patients seen in the date range by zip code, age and gender, ethnicity, and income - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
All open claims as of a chosen date, broken down and subtotaled by clinic - Set as of date at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients seen in a date range with age, gender, zip, race, whether they have insurance, Medicaid, or a Sliding Scale fee, and whether they are a medical patient - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Upcoming appointments for a list of insurance carriers - Set date at top to show appointments after.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patient production and collection - For Versions 17.1 and greater. Please update your version accordingly. A patient is new if their first completed procedure is in the date range, or if their last completed appointment or procedure was more than 5 years from the current date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients that have completed procedures in date range with any unsigned sheets - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
All upcoming Automated Appointment Reminders. - Only for version 16.2 and prior. Useful for people using automated appointment reminders.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Improperly Formatted Phone Numbers -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Email Message Search - Set search text at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Recall descriptions for patients with appointments in the date range - Set date range and any recall types to exclude at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of un-scheduled hours per operatory by date for given date range - Set date range at top. Can also set the Op name if desired.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Flattened number of hours a specified provider is scheduled every day between the date range. - If the provider is scheduled in multiple operatories at the same time, it will NOT duplicate time. Set provider abbr and date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure Analysis by Provider - Set provider, month, and year at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure Analysis for Practice - Set month and year at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient Visits for Carrier - Set carrier name and date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient detail report. For patients seen in the date range, displays address, whether they are a new patient, their payment type, P&I, and whether they had any specified procedures done - Set date range and procedure codes at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging of patient portion balance. Includes aging of credits - Set as of date at top. A/R for versions before 14.3.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patient problem definitions. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of medication definitions. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of prescription definitions. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with specific active problem seen by provider in the date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special aging report-Patient aging by category(0-30,31-60...) including aging of insurance estimates - Filter by patient with insurance,no insurance, or all.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Provider breakdown of patients seen, billable visits, days worked, and the average of the billable visits per day. - Billable visits is defined as a patient that has a specified billing type and has no insurance. Seen is defined as a patient that has a completed appointment in the date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Shows number of patients of or under 5 years of age and over 5 years of age as of their last completed procedure, with percent. - Only patients that have had a procedure completed within two years of the specified date are considered. Percent is number of active patients under 5 years/total number of active patients.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with completed appointments in the seen date range, and has the specified hygienist on the appointment. - Gives a procedure fee total from the completed appointments. Also totals treatment planned RESTORATIVE procedures, treatment planned in the TP date range, breaking them into TP'd procs on and not on a scheduled appointment.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients that have been seen in the date range with the specified referral source, and being younger than the specified age. - For Versions 17.1 and greater. Please update your version accordingly. Shows patnum, patient name, patient's age, referral from source, and primary insurance carrier for patients.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with complete appointments in the seen date range with a RESTORATIVE procedure(D2000-D2999) attached, and has the specified hygienist on the appointment. - Gives a total from the completed appointments RESTORATIVE procedure codes. Also totals treatment planned RESTORATIVE procedures, treatment planned in the TP date range, breaking them into TP'd procedures on and not on a scheduled appointment.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients that have a completed D0150 that is attached to a completed appointment in the date range and that has either an insurance payment or patient payment. - Leave clinic blank to see all procedures, including thoes not assigned to any clinic.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families also showing dates of the last visit, insurance payment, and statement. Also groups accounts that are above and below a $50.00 balance with subtotals and practice totals. - A/R for versions before 14.3
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of carriers and the claim form that they use. - Can be used for change verification, when making bulk changes to the claim forms that are attached to insurance plans.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients that have an appointment in the date range, showing insurance information. - PH stands for Plan Holder(Subscriber).
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients that had their first completed appointment in the date range, having procedures that are completed and attached to the appointment. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Pulls a list of Allergies, with the ability to enter in the description for no known allergies. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with primary and secondary carrier names and group names for active and inactive patients that have been seen in the specified date range. - Also includes patients with no insurance. Seen counts as a completed procedure in the date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production in a date range summed by procedure category with another section that sums by patient type. - Also has the ability to run for a specific provider or the practice as a whole. Similar to query 767.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors with active family members. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients with PatNum, Date of First Visit, clinic, and address. - Shows count of active patients per clinic at the bottom of report.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment List report - Filtered by Carrier and Operatory
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of scheduled appointments in date range, with patient and insurance information. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients with appointments in date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payments report filtered by provider and carrier. - Will only show production for patients with insurance at the time of the procedure.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Receivable Breakdown report but takes into account payment plans - For versions 14.3 to 16.1 - Set date range and WriteoffByDateOfService accordingly, Calculations are for Open Dental versions 14.3 through 16.1. Query will be inaccurate for later versions.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Special referral report. - For Versions 17.1 and greater. Please update your version accordingly. Shows breakdown of patients seen, number of appointments, as well as specific procedure range breakdowns for patients with referrals entered in date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging report with custom aged columns. - Sorted by highest balance. For Open Dental versions 14.2 and before.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily production and income report with totals by provider and month to date - Modified to include Billing Type filter.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Specific referral demographics breakdown. - For Versions 17.1 and greater. Please update your version accordingly. Also shows an average amount of care per patient. Patients with multiple races will count for each specified race in the breakdown. This can cause the percent to be over 100%.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily procedures report in date range with financial data - May specify providers and procedure codes. Includes procedure-level financial info. Does not consider Payment Plans. May choose not to show procedures with a $0 estimated patient balance.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Un-scheduled hours per operatory by date for given date range with total at the bottom -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures treatment planned in a date range by procedure code. - Shows amount and % that were completed, and how many TPed procedures are attached to an appointment
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients seen in date range with specific completed or treatment planned procedures. - Also shows a count of patients seen in date range with the specific completed procedures and no treatment planned specific procedures.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payment report, like internal but can exclude codes if desired. - Shows procedure information if applicable, shows patient payments per payment split, and Insurance payments per procedure (or claim for as total payments). As total payments do not filter on excluded codes.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients less than or equal to a specified age who were seen in date range - Also shows a percentage of those patients who completed a specific procedure code.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients with specific problem seen in date range - Also shows percentage of those patients who completed a specific procedure code.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of providers and their average hourly gross production, hourly net production, and hourly income in a date range - Providers must also be entered in as an employee, providers first and last name must match the employees first and last name. Providers must also be using the time clock.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients who were referred from a specific source - For Versions 17.1 and greater. Please update your version accordingly. Shows who they were referred by and date of their last appt
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients who have had an NP procedure completed -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of treatment planned procedures with no scheduled appointment. - Patients must have last visit in date range. Filters by specified carrier and clinic.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with appointments for the current date with primary insurance carrier listed. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
All patients who have had an NP procedure completed showing information about procedures completed not on the same day as the NP procedure. - For Versions 17.1 and greater. Please update your version accordingly. Counts for completed procedures are not restricted to the date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance payments received in one date range for procedures completed in a different date range, with both ranges specified by user. - Uses claim level service date as user may not have entered payments by procedure, so if it is a claim with multiple procedure dates, uses the claim level service date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Carriers and count of procedures they have paid on -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient, total fees, and claim information from completed Appointments within a specified date range with claims from a specified carrier. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of procedures by provider and proc code in date range. - Also shows gross production for those procedures
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients over 18 and patients who are younger then 18 that reside at the same address. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing list of patients with a particular insurance carrier and Group Num. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patient procedures showing CoPay, subscriberid(s) and other various procedure information -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments excluding specific provider or payment types - Only will show patients with negative balance
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of dates with appointments by Provider showing how many appointments they had on that day - Also shows how many appointments met the user defined min proc count
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily production and income report showing payments, adjustments, and patient income. Ordered by procedures in a specific order. - Total insurance writeoffs by ins payment date. Assumes that any PPOs are entered as Category Percentage. Runs for all providers and ordered by specified procedures
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment and claim information. Showing 1 row per patient appointment in a user defined date range. - Includes 1 row per claim associated with the appointment Also shows any insurance payments made on claim(s) and patient payments made on the appointment date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance - Shows values like the account module. Like internal AR report run with "Including negative balances". For versions 16.2 to 19.2. Versions 14.2 and before, use 718. Versions 14.3 to 16.1, use 1078. Versions 19.3 to 20.5, use 1487.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of referral sources that are listed as patients and patients referred by them who had production in a date range, with the amount of that production - For Versions 17.1 and greater. Please update your version accordingly. Includes subtotals for each referral, and a grand total.
DBMS version(s): MySQL 5.x, MariaDB 10.5
For Version 16.4 and earlier. Summary of Claim Fees, Proc Fees, Adjustments, Writeoffs, TP'd Fees, Insurance Payments, Pat Payments, and unpaid portion for patients with their first apppointment in the date range and at least one referred from entry - User can specify a date range, referror, carrier, and AsOf date. Uses referral listed first in program interface. AsOf date will use the current date if left blank.. See 1196 for versions 17.1 and later.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Summary of Claim Fees, Proc Fees, Adjustments, Writeoffs, TP'd Fees, Insurance Payments, Pat Payments, and unpaid portion for patients with their first apppointment in the date range and at least one referred from entry - User can specify a date range, referror, carrier, and AsOf date. Uses referral listed first in program interface. AsOf date will use the current date if left blank. See 1195 for versions 16.4 and lower.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims received in date range with no payment. Excludes pre-authorizations. - Similiar to query 448.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients and their preferred contact methods - Includes only patients marked as patient or non-patient
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with the specified active problem seen by the specified provider in the date range - Lists those patients, diseases, and providers. Like query example 891.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a current insurance plan marked as pending -
DBMS version(s): MySQL 5.x, MariaDB 10.5
2017 EHR - Objective 9 Query: Secure Messaging -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of New and Lost Patients as well as Net New Patients in a date range - Set date range at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of new patients, total patients, and total patient visits in date range - Set date range and provider abbreviation at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure report for specific insurance plans - Set date range, insurance carrier, UCR fee schedule, and adjustment codes at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Number of hours scheduled for a specific provider per operatory not including blockout times - Set date and provider abbreviation at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients who have a procedure that have a secondary or other claim but do not have a primary claim - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and patients seen by provider by clinic by date - Set date range at top. Net production calculates writeoffs by insurance payment date. To calculate writeoffs by procedure date use query 1213
DBMS version(s): MySQL 5.x, MariaDB 10.5
Average production per procedure code - Set date range at top
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures with claimprocs that are not attached to a claim and are for a plan the patient no longer has -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Weekly hours worked for all Employees - NOTE: Recommend running after hours. Query can take a long time to run and lock up large databases or those with lots of employees. Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Counts the total number of patients with a completed procedure in the date range and of those patients, the number who currently do or do not have insurance - Set date range at top.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment plans with Payments, balance due, and completed treatment - V16.2 and later. Will be inaccurate with Dynamic Payment Plans (V19.3+).
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and patients seen by provider by clinic by date. - Net production calculates writeoffs by procedure date. For calculations by insurance payment date, use query 1207
DBMS version(s): MySQL 5.x, MariaDB 10.5
Automated Reminders That Have Been Sent. All automated appointment reminders that have been sent for future scheduled appointments. - Version 16.3-22.1. Only shows reminders for future scheduled appointments.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patient and list by treating provider for date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Index cardinality does not match actual values - Run the Optimize tool in Database Maintenance to resolve these.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Task List extraction - Export task list, by Task list name, status and for tasks created in date range - Export task list
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patient appointments for new and existing patients per date created in date range - For OpenDental versions 19.4 and up.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List and count of a specified procedure completed in a date range with name of patient -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient list with language -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment Finder Report - Like internal treatment finder as of version 16.2. Set options at the top. Unlike #1111, this report takes into account categories that are excluded from the annual max and adjustments to benefits.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Income Tax Receipt for a family -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure codes with a default provider assigned - Procedure codes with a default provider assigned. This is the "Assign To Prov" value for the procedure code.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income report for a set date range like internal monthly Production and Income showing estimated net production in Sched column - Set date range at top. Displays net production in Sched column. See 1343 to show gross, writeoff and net production for scheduled appointments.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Task List Extraction - For printing or exporting a task list by specified Task list name and status, for tasks created in date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
End of month accounts receivable per clinic - SEE REPLICATION WARNING Set the date range at top. Only for versions 16.2 - 19.2
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with the same SSN entered - Useful for finding patients that have the same SSN entered
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payments report. - Like internal report grouped by check, but doesn't break down checks by the provider splits.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of carriers with specified fee schedule attached. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of non-hidden employees -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payments report like internal report without provider split. - Uses date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of procedure codes with provider or clinic specific fees. For fee schedules that were/are unchecked for "Use Global Fees". -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance Plans Report - Like Internal report.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of possible duplicate patient entries. - Used in conversions. These patients have the potential to be merged. ALWAYS VERIFY INFORMATION BEFORE MERGING.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance payments in date range grouped by carrier, payment type, and pay date. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with discount plans - This query is for versions 16.4 through 20.4. For versions 20.5+, please see query example 1575
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patients count for a given date range. Active patients only - NewPatCount is the count of new patients that had their first procedure completed in date range. NewSchedPat is the count of patients with sched apt in date range, and no completed procedures yet
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment Plan Aging report - Shows all aged columns based on payplancharge date of overdue amount
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients who were created in Open Dental database in user specified date range - DateAdded = date the patient was added to the Open Dental database if post version 16.1
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled Ops and their open times - HasAppt indicates if the scheduled operatory has an appointment booked at that time Must have a provider scheduled to operatory in order to show rows for those appointments.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with a positive balance who were referred from a user specified source by last or first name of the referral. Option to filter by the referral note attached to the patient. - Patients with more than one referral from source will show in the same Referral column
DBMS version(s): MySQL 5.x, MariaDB 10.5
Confirmation list of patient appointments and insurance benefit info, if any. - Includes patients remaining 'general' benefits, and makes no assumption of calendar year benefits Patients will show as many times as appointments they have scheduled in the date range and one row for each insurance plans they have.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment Plan report for patients with open payment plans - Shows info from the pay plan including payments and amounts due
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed appointment information including average collections for medicaid and non-medicaid appointments in date range by provider -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referral Source counts of new patient appointments by referrer - Includes scheduled and completed new patient appointment counts in date range
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients who broke their very first appointment before having any procedures completed -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gross Production and active patient visit count by Group Number in a date range.* -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gross and Net Production and Patient Count by Insurance Type for patient seen in date range and in age range in specified clinic - This query can be very slow, recommended to run after hours and for a smaller date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income with income related to procedure date rather than payment date - Pay% column calculation: (total payments for the row) * @PayPercent. Useful for paying providers on a percentage of collections.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patient Referral Value -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Annual production and income report with writeoffs by insurance payment date. - Like internal.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing information from guarantors of active patients seen in the date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of carriers with no fee schedule attached. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patient report like internal with added fields. - Includes the following fields, not found on internal: middle initial, billing type, clinic abbreviation, secondary provider, email, and phone numbers.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients currently using Discount Plan feature. v16.4+ - For versions 16.4 through 20.4. For versions 20.5+, please see query example 1575.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Patients in alphabetical with an appointment on the specified date -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment plan charges for a single day with totals by provider and a grand total - Set Day
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen between two dates, that do not have an appointment (Scheduled or complete) after a specific date - Date range, set date for no appointment after the entered date
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance and patient estimates within date range for procedures done on future appointments - Set date range
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled patients with an insurance overpayment -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Net Production (with PPO writeoffs removed by procedure date, other adjustments not considered) and Income by patient showing referral souce for date range. KEY POINT: only includes referrals received and income collected during that period, whereas some - For Versions 17.1 and greater. Please update your version accordingly. See 627 for breakdown by referral source.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of scheduled or ASAP appointments in a specified date range for the specified patient - Shows operatory name and all appointment views for each appointment for the specified patient.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients seen in a date range, gross production by patient for a specific primary provider - Uses date range, provider abbreviation, and production over threshold
DBMS version(s): MySQL 5.x, MariaDB 10.5
Summary claim information summed by date and clinic, with sums of claim fees, insurance estimates, insurance payments, counts of claims and patients, fees for outstaning claims, and writeoffs - SEE REPLICATION WARNING Uses date range, clinic, and provider
DBMS version(s): MySQL 5.x, MariaDB 10.5
Summary claim info by date and clinic, with sums of claim fees, counts of claims and patients, fees for outstanding claims, and writeoffs. AvClimFeeDay - Average Claim Fees per Day Open; AvPtDay - Average Patients per Day Open; AvClmFeePt -Averag - SEE REPLICATION WARNING Date range, and Clinics which can be separated by a pipe character for multiple
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging of A/R by month going back 1 year (12 months) with insurance carrier names -
DBMS version(s): MySQL 5.x, MariaDB 10.5
A/R treatment done prior to date where patient balance existed before date and received payment after date. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Provider New Production and Income Tracker - Date range, billing type, provider abbreviation, set date
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient's with and without referrals entered in date range, with date first appointment,next appt, net production, scheduled production, and total tp - Date range, appointment out to date
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients first appointment,referral source,net production,and upcoming appointments with TP priority and type - SEE REPLICATION WARNING For Versions 17.1 and greater. Please update your version accordingly. Set date range and treatment plan priorities.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Collection by Referral Source - Date range. Will duplicate income for patients with multiple referral from sources. Ideal for offices that have only one referral from added per patient.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily Sheet per provider to track income,new patients, and ortho procedures. - Date range
DBMS version(s): MySQL 5.x, MariaDB 10.5
Estimated Daily Portion and Amount Collected - Date range
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient age within range - Age range
DBMS version(s): MySQL 5.x, MariaDB 10.5
Display QUALIFIED DTI patients and check to see if they were given a follow up appointment and assigned the correct DTI code. - Set appointment date, prcodere codes separated by pipe character, carrier names, form
DBMS version(s): MySQL 5.x, MariaDB 10.5
Display QUALIFIED DTI patients and check to see if they were given a follow up appointment and assigned the correct DTI code. Date of Prior Risk Level and RecareEligibility - Set appointment date, procedure codes and carrier names
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with completed D0150 or D0180 in date range with Tx and amounts. - Date range
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a Fee Schedule(rarely used) set in the Patient Edit window of the Family module -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient level aging for patients by treating provider/provider on the payments/provider on the adjustments. - Just like query 1052, but will work correctly between versions 17.3 and 19.1. Use in tandem with 1313 to validate payment allocations.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of claim procedures with mismatched providers on the procedure - WARNING: This query will likely be slow the first time you run it to find preexisting mismatches! First run this should be before you opened, after those are fixed this can be the date you fixed them rolling forward.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of guarantors with provider income imbalances. - Filter option to only show guarantor accounts needing income transfers. For versions 17.3 through 19.1
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims received in a date range with 0 payment -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Adjustments in the date range with totals by adjustment type. - Similar to 804 but runs for all adjustment types.
DBMS version(s): MariaDB 10.5, MySQL 5.x
List of deleted procedures with each iteration of procedure notes - Useful for copying notes from deleted procedures.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of documents in specified image categories, per patient. - Also shows group concat of dates on the images.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Current and historical list of user specified non-deleted patient Medications -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Current and historical list of user specified non-deleted patient Allergies -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Current and historical list of user specified non-deleted patient problems -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient balances @AsOf date for patients that have not been seen since user specified @CreditSince date variable - Aging for Open Dental versions 16.2 through 19.1
DBMS version(s): MySQL 5.x, MariaDB 10.5
WebSched Recall reminders sent in a date range with msg text - For versions 17.1 to 22.1. To specify date ranges, change dates between the ' ' in format 'YYYY-mm-dd'
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patients Report including information from first visit and more - Like internal report version 17.3 with more info
DBMS version(s): MariaDB 10.5, MySQL 5.x
Popups older than date specified that are not disabled or archived - Only for Open Dental versions 22.1 and earlier
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income report for a set date range including future scheduled net production - Similar to 1224 but shows gross production, writeoff, and net production figures for scheduled
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors with installment plans - The months may be lower than actual if the Finance Charges tool is run to add interest charges.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients who had a recall trigger procedure completed but don't have a corresponding recall type -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Overpaid procedures showing adjustments, writeoffs, insurance payments, and patient payments - This only works correctly if attaching adjustements, patient payments, and claim payment to procedures
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging Report for TSI Aging Manager -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance aging report. Outstanding insurance estimates aged by date of service 0 to 30, 30 to 60, 60 to 90 etc. Summed by carrier and patient -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of deleted procedures and each instance of the procedures note for the specified patient. - Useful when needing to re-enter procedures and notes that were deleted.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Printable form of Internal Carrier List -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient Status Changes - This query will only show data that was changed/ entered after version 17.4
DBMS version(s): MySQL 5.x, MariaDB 10.5
Find stored credit cards by the last 4 digits to see which patient they are attached to. - This pulls credit cards that are stored in the database. Does not look at the paynote on payments.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of blockouts from date specified going forward. - Defaults to date report is ran. May optionally filter on Blockout Type.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production in a date range summed by procedure category - With number completed, total gross and net production, average gross and net production per procedure, and Percent of gross production in a date range. Claims need to be created for Writeoffs to be included.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Custom Practice Daysheet -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Provider totals for Patient, and Insurance Income, deducting Lab Fees from Total column -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of specified appointments types, moved from the unscheduled list, in a user specified date range -
DBMS version(s): MariaDB 10.5, MySQL 5.x
List of patients with a disabled until date entered in their recall or Always Disabled box checked. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients that have multiple completed procedres in the date range in multiple clinics -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patient insurance plans with Option Patient ID entered -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with duplicate recalls. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of subscribers with multiple inssub rows for the specified subscriber - Good for error checking the Insurance Plans report.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Blockout list in a date range - Defaults to current date up to date specified.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance Plans Grid List - Like Internal.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Charted procedures with a base unit or unit quantity greater than 0 or 1 respectively -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure Codes with Base Units Greater than 0 -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance checks received in the date range with total -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with their first ever 'Completed' appointment by date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily patient payments where Entry Date does not equal Payment Date -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients seen in a date range with age, race, carrier, number of visits and other information, for FQHC grant - Use if EHR was not enabled during data entry. See 1651 if EHR was enabled during data entry.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Adjustments in a date range with patient, provider, and procedure information -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily payments report for the specified clinic -
DBMS version(s): MySQL 5.x, MariaDB 10.5
For a specific year, carrier, and clinic, the referral sources that referred patients and the number of patients referred, totals and monthly subtotals - For Versions 17.1 and greater. Please update your version accordingly.
DBMS version(s): MariaDB 10.5, MySQL 5.x
List of referrers, showing count of patients, completed production, and treatment planned production for patient referrals - For Versions 17.1 and greater. Please update your version accordingly.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Broken Appointment Analysis - Only shows if the broken appointment was moved to the unscheduled list and not left on the days schedule.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment Statistics by clinic for the specified date -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment time, patient information, and whether the appointment was completed or not, for appointments that were originally scheduled in specified date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients who had an appointment, in specified date range, that was moved or deleted within 48 hours of the scheduled date and time of the appointment. Also shows the user that performed the move/delete - For versions 19.3 and earlier. For versions 19.4 - 22.3 see 1605. Date range filters the "OldAptDate"
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of all printed claims that were sent in the date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with procedure codes like D80xx Completed. Shows Days Since Last Visit -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of procedures that were referred out in the date range for patients with the specified clinic -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with completed Broken Appointment procedures in a user defined date range, including a count of Broken Appointment procedures in the date range. - Uses code D9986 by default, but this can be set to a list of any codes, such as "D9986,D9987"
DBMS version(s): MySQL 5.x, MariaDB 10.5
Time Card summary for all Employees by clinic - Requires version 21.3 or higher
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of scheduled and completed appointments in the date range per day that were originally scheduled via Web Sched -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily Adjustments Report, same rows as internal plus CDT Code and Insurance Carrier info -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Shows the count of the patients seen by a provider within a date range by age, race, insurance, and sliding scale -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gross and Net Production for specified procedure(s) completed in the date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Clinic, Patient Name, Initial Appointment, count of appts completed in date range for patients who had an appointment completed in the date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referrals sources and patients list with date and patient fields for referrals made in date range -
DBMS version(s): MariaDB 10.5, MySQL 5.x
Appointments in the specified date range. Shows date, time, length of the appointment and operatory the appointment is scheduled in -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Percentage of total patients seen in the specified date range by insurance carrier, including not insured -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Age, carrier, insurance type, and group name for insured with active patient status -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Cross Settlement - Paysplits where receiving Provider is not the patient's Primary Provider -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Saved treatment plans with the amount treatment planned, the amount in a scheduled or ASAP appointment, and the amount that's been completed -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient portion aging for families with any positive balance, by Patient -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient portion aging for families with any positive balance, by Guarantor - For vresions 14.2 and before
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of new patients, or reactivated patients, with patient and procedure information -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients referred by specified referrers, with specified codes treatment planned or completed -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and income report by day, for all income linked to Procedures completed in the date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with missing teeth. Shows their last visit date and, if insured, their remaining insurance coverage -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Mailing information and referral information for patients created in the date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance, with patient and insurance estimates separated -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments in the date range filtered by provider and/or assistant -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments in scheduled status in a date range as of a specific date and time -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients and returning patients from 2+ years with referral source and patient address -
DBMS version(s): MariaDB 10.5, MySQL 5.x
Active Patient List Excluding Clones -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with any balance - Shows values like the account module. Like internal AR report run with "Including negative balances". For versions 19.3 to 20.5. Versions 14.2 and before, use 718. Versions 14.3 to 16.1, use 1078. Versions 16.2 to 19.2, use 1192.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with documents in specified image categories, per patient -
DBMS version(s): MySQL 5.x, MariaDB 10.5
How many days am I open in a date range? - Displays per month/year in the date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a specific billing type or hidden billing types - Includes the patient numbers of the guarantor's family members. The PatNum column displays the guarantor's name.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with recurring charges setup, excluding deleted patients -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Detailed Waiting Room Report with totals and averages, procedure and appointment information - Shows averages and totals for wait time, chair time, appointment length. Filters by clinic.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment plans with mismatched Principal and Credits - Not intended for dynamic payment plans.
DBMS version(s): MySQL 5.x, MariaDB 10.5
User Permissions Audit - Displays permissions as of version 19.4.18 for all users.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients who have an appointment in the date range, with contact information. - Contact list for patients
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active Patients with their last completed procedure in a date range with the specified carrier as their insurance. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments in the Date Range With Hidden Provider(s) - This query will display any appointments with an appointment date in the specified date range where either the primary provider or the hygienist on the appointment is a hidden provider.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with PreMed Checked -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with Medical Urgent Note -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Error Checking for claim payments with a carrier specified on the claim payment that do not match the carriers listed on the bulk payment -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of guarantors with a credit balance and a family member with a payment in a date range - WARNING - this query can be slow on larger databases
DBMS version(s): MariaDB 10.5, MySQL 5.x
Procedure codes marked as Hygiene -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments and Estimates per procedure with user that entered the payment - Shows payment information for procedures with an insurance payment received in the specified date range, where the insurance estimate does not match the total insurance payment amount.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Carriers with Claim payments in the date range. Includes patient payments for patients with and without insurance claims during the date range - This query can be used to compile data more easily for the CARES Act relief fund application.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Carriers with Claim payments in the date range. Cares Act Query v2. See notes below - Includes patient payments made to procedures sent to a primary carrier under the row for that primary carrier. Patient payment not attached to a procedure that was sent on a primary claim are included in the Self-Pay row
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed procedures with one of the specified insurance carriers. - Can be used to get visit counts for PPE insurance payment verification.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of non-hidden Insurance Carriers. Like Lists > Insurance Carriers - Groups by Carrier Group Name if present. Displays like Lists > Insurance Carriers. For version 17.2+
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments made in the specified date range and clinic on procedures completed prior to the cutoff date - Patient payments do not include Income Transfers.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Find all repeating charges added today. - Used to find out which patients were charged.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients seen in a date range by carrier, including uninsured / cash -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments and adjustments entered in the specified date range not attached to procedures -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Printable Employers List -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient payments with the specified check number in a date range, by patient, with amount split to each patient -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with 'In Process' procedures -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Printable pharmacy list -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of appointments completed in the date range. - Shows duration between first confirmation/appointment status changes. Relies on recommended appointment flow. See Specifications for more details.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient count by zip code and patient's primary provider; considering patients with a patient status of Patient, with at least 1 completed procedure (excludes broken/missed codes). - Only counts patient once per provider set as a patient's primary provider. Can be added together to get total patients. See #178 to count by zip only.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Payments in the date range attached to one of the specified providers, clinics and procedure codes. - Leave @ProvList, @ClinicList , and @ProcCodes as empty quotes to not filter results. Payments MUST be attached to procedures to be listed. Includes additional procedure information.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with prophy frequency limit of 2 times per year - For Open Dental version 23.1 and below. Will check both primary and secondary insurance for a frequency limit of 2 times per year. Shows how many prophy codes the patient has completed and the date of their last completed specified code.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of appointments created in the date range by the specified user, with a specified procedure code attached. - Requires version 19.4+. See 1567 for versions 19.3 and lower.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of appointments created in the date range by the specified user, with a specified procedure code attached. - Requires version 19.3 or lower. See 1565 for 19.4+
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of appointments by appointment type clinic and date, that have a date in the specified date range. - Ordered by clinic, appointment status, appointment date, and appointment type
DBMS version(s): MySQL 5.x, MariaDB 10.5
Audit trail entries in date range for patient's primary provider changes. - Enter provider abbreviation for old provider in @FromProvAbbr and/or new provider in @ToProvAbbr. Enter '%' in either to see all.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Guarantors with Credit Balances - Filter by the date of the family/s last payment by entering 'Payment' or the date of the family's last completed appointment by entering 'Seen'
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with discount plans - This query is for versions 20.5+. For versions 16.4 - 20.4, please see query example 1237
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment report like internal - Includes the patient's ID to better identify the specific patient
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of active patients, by age, in the specified age range, with completed procedures in user defined list that have a date of service in the specified date range. - Patient's age uses report run date (age as of the day you run the report). For age based on visit date see example #1581.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of active patients, by age, in the specified age range, with completed procedures in user defined list that have a date of service in the specified date range. - Patient's age uses procedure date (age as of the day the patient was seen). For age based on current date see example #1579.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Monthly production and income broken down by provider - Calculates writeoff estimates by initial claim date and writeoff adjustments by insurance payment date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Customized aging for families - For versions 19.3 - 20.5. Shows values like the account module, with additional information useful for follow-ups regarding balance collection. Like internal AR report run with "Including negative balances". Automatically takes the payment plan setting in
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aged vs Estimated Insurance Payments - Shows insurance payment estimates in aged buckets as well as payments in buckets based on how long it took the paymen to come in from the date of service
DBMS version(s): MySQL 5.x, MariaDB 10.5
Same Day Collection Tracking - Similar to the standard "Patient Portion Uncollected" report, but with one row per patient per service date, rather than one row per procedure.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Credit Aging with family contact information - Includes additional aging buckets up to 5 years. Excludes families with open payment plans. For Open Dental versions 19.3 to 20.5.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Production and Income by Carrier per procedure code - Only considers primary carriers. Adjustments and patient payments must be attached to procedures to be considered. May optionally specify procedure codes to include.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Carrier Subscriber and Revenue Statistics - Includes count of patients on the plan based on sent claims as well as received insurance payments and writeoffs.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referral Collection Analysis for New Patients -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Annual Production and Income including Scheduled Production -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of procedures with specified codes for specified date range and insurance fee schedule -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Blue Book history - Used to verify amounts that are being stored for use by the Blue Book feature in Open Dental versions 20.3+
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients who had an appointment, in specified date range, that was moved or deleted within 48 hours of the scheduled date and time of the appointment. Also shows the user that performed the move/delete. - For versions 19.4 - 22.3. See 1423 for 19.3 and earlier.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Hygienist Gross production in a date range, with average per day -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Scheduled Hours and Net Production Per Provider for the Specified Date - Net production is based on procedures completed on the specified date plus adjustments attached to those procedures minus writeoffs (using estimate if payment is not yet received)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Provider Consult Production Report - Providers who completed consultation codes in a date range, and total adjusted production from all providers in a different date range for the consulted patients
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with "Appointment scheduling is restricted" checked -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure level Production and Income, with estimates, for a specific provider -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Monthly Production and Income by Provider - Like the standard Monthly Production and Income report, but with a breakdown by provider (including "None" for unearned income)
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance and Patient Estimate Aging for specific providers and clinics on each transaction - Unlike the standard Aging of A/R reports, this query filters the provider and clinic for each transaction, regardless of patient assignments. Written for Open Dental version 20.2.40. Aging may not be accurate beyond version 20.5.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Statements in date range with current family balance and amount paid since the statement -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient completed appointments in a date range for specified primary carriers - Includes chair time, production, income, and writeoffs for each appointment
DBMS version(s): MariaDB 10.5, MySQL 5.x
Patients with appointments scheduled for the specified date range - Includes any problems, medications, and allergies on the patient
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income by procedure code, for procedures completed in the date range. - Includes ability to filter by procedure code, treating provider, and clinic. Requires procedures to be attached to adjustments and payments. May be useful for census in specific situations. Similar to 1870.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active ortho patients. - Shows patients with a status of Patient and have all capital letters for their first and last name.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of procedures that are more than half covered by insurance, on patients that have insurance and no schedule appointments - See specifications for more information on how data is pulled, if needed.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Hygiene reappointment statistics per provider on appointments completed in the date range. - See specifications for more information on how data is pulled, if needed.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of text messages sent in a user specified date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Each referral's gross production and income in a date range per provider. - The income is reported by payment date, and may not be associated with the period's production. May optionally filter by clinic on the procedure or payment.Leave @Clinic variable blank (empty quotes) for all clinics.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient Analysis report - Only considers patients in Patient status, except the last section.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New and Lost patient report. - Count of new patients in date range, count of lost patients. List of lost patients in Patient status. "Lost" patients are patients whose last appointment was in a date range one year earlier than the specified date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients per clinic and insurance carrier (including self pay) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients seen in a date range with age, race, carrier, number of visits and other information, for FQHC grant - Use if EHR is enabled during data entry. See 1407 if EHR was not enabled during data entry.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment report like internal - Includes the patient's ID to better identify the specific patient. Also includes Date Issued for insurance payments.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families or patients. Similar to standard Aging of A/R report. - For Open Dental v21.1 and higher. Has many of the same options as the standard Aging of A/R report, plus an option to exclude families/patients with outstanding insurance estimates.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments scheduled in the specified date range that are in the specified op for patients with specified carrier(s). -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of active patients (by patient status) with invalid codes attached to their account - Active is a patient status of Patient.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments report like internal - Includes the patient's ID to better identify the specific patient, also filters on and shows the user that entered the payment.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Patient Portal Payments in Date Range and optionally for a specific patient - Patient Portal Payments in Date Range and optionally for a specific patient, with an indicator if it's been processed yet in Open Dental. See also Main Menu > Tools > Patient Portal Transactions.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of procedure code time patterns by assistant and provider (in minutes) -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Accounts with credit balances and mismatched unearned amounts - Requires running aging tool found in the main toolbar under Tools>Aging for balances to be accurate.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments in the future with an arrived time; Waiting room troubleshooting - This is to troubleshoot patients showing up in the waiting room feature who aren't actually waiting due to having an Arrived time already set on a future scheduled appointment.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Patient, NonPatient, and Inactive patients and their Patient Portal Access status -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Adjustments to Benefits and Procedure History entries done by automation - For error checking Adjustments to benefits and verifying procedure histories that were entered through automatically imported benefit information.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Discount plan adjustments on procedures completed in a date range by a specific provider - Adjustments must be of "dp" type
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of Patients in Screening Groups in the Specified Date Range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Year to Date Production summed for each provider - Providers must not be marked as "hidden on reports". Only specific adjustment types are considered.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures completed on the specified date, with payments attached. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income by procedure code, for procedures attached to appointments and for specified carriers -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income by Carriers, Group Name, Group Number, and Proc Code - Similar to #1593, but allows filtering the primary insurance group name, and procedure codes.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Total Count and Sum of Patient Credit Card Payments by Day in a Date Range - Reconcile credit card patient payments in Open Dental with merchant statement
DBMS version(s): MySQL 5.x, MariaDB 10.5
Sum of Procedure Fees Completed in a Date Range by Provider, Clinic, and Billing Type, and a list of applicable patients -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with one group of codes completed in a given date range but not another -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Negative Production Report - Identify negative production to be manually revised for offices paying on collections.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Active and Inactive patients with an appointment completed in the specified date range who are eligible for implants - A patient is considered eliglble if they have 5 or more missing teeth, a Completed or Existing Other extraction on 5 or more teeth, or a Completed or Existing Other complete/partial denture code. They also can't have a complete D6010
DBMS version(s): MySQL 5.x, MariaDB 10.5
Recall information for the specified patient -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of insurance plans for the specified patient, where the plans have the specified carrier name. - Carrier Names and group names are cutoff for space consideration
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance plan breakdown for the specified patient - Displays like "Edit Benefits" window with "Simplified View" unchecked
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of procedures that are marked "Do not usually bill to Ins" - Looks at the Edit Procedure Code window under Lists>Procedure Codes
DBMS version(s): MySQL 5.x, MariaDB 10.5
Incomplete procedure notes for date range with provider. - Like internal, but will also show procedures with no note. See also 302.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Advertising Postcards: Patients with birthdays in specified month and day range. - Based on example #3, but adjusted to work with the Advertising Postcards feature. For the month and day, enter as "MM-DD" including the quotes. Leading zeros are required. "03-08" will be March 8th, but "03-8" will not work properly.
DBMS version(s): MySQL 5.x, MariaDB 10.5
UTM tracking for Web Sched - To track how many people visit your Web Sched URL. See https://opendental.com/manual/webschedadvanced.html "Other URL Options" section.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance and Patient Estimate Aging for specified treating providers and clinics - Unlike most aging queries, this query uses treating provider and clinic and not the patient's assigned provider and clinic. It should not be expected to match any standard report.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures completed in a Date Range with Specified Provider for Active Patients including Claim ID -
DBMS version(s): MySQL 5.x, MariaDB 10.5
"Patient" status patients with no perio chart or whose last perio chart was at least the specified number of months ago - Defualts to 6 months. Will only display patients who are 18 or older at the time the report is run.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Family Aging for all families that have a balance before insurance - For all Open Dental versions. Requires that Aging has been run today. For more information about Aging, see https://opendental.com/manual/aging.html.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments received in the specified date range by category - Categories are "Commercial Ins" for insurance payments, "Self-Pay" for uninsured patient payments, and "OtherPatPay" for insured patient payments
DBMS version(s): MySQL 5.x, MariaDB 10.5
Average wait time by assistant for the specified hygienist, provider, and/or assistant -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with an appointment scheduled for the specified date, showing information about forms - Patient Registration and Medical History forms will show 'Y' if there was one for the patient in the past year. HIPAA and Fiancial Agreement forms will show Y if there is one from any date.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of Active Patients with current insurance using specified insurance Fee Schedules, seen in the last 3 years. - Does not include patients with no insurance.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Completed procedures within the specified date range with no procedure note -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and income report for the specified date range and provider - Includes daily breakdown and overall totals
DBMS version(s): MySQL 5.x, MariaDB 10.5
Broken appointments with appointment date in date range showing user that broke the appointment - This query uses the Audit Trail to track broken appointments and will only be accurate for data entered in versions on or after 15.4. See 805 for versions before 15.4.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Carrier Aging with a Self-pay row. Only considers patients with a balance owed. - Aging for patients with a positive balance before insurance. Has a row for all patient portions (Self-Pay) and for each carrier name associated with a patient in the Self-Pay row. Only accurate for Open Dental versions 21.1 and above, if the Account Prefe
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of PreAuths for specified clinics sent in specified date range - Preauthorization Tracking, Preautorizations with a 'Date Sent' in the Specified Date Range and Attached to One of the Specified Clinics
DBMS version(s): MySQL 5.x, MariaDB 10.5
Received and Supplemental insurance payments attached to procedures that are not in Complete status, (optionally) for patients with a specified guarantor - Troubleshooting Query - Received and Supplemental insurance payments attached to procedures that are not in Complete status, (optionally) for patients with a specified guarantor
DBMS version(s): MySQL 5.x, MariaDB 10.5
Provider Procedure Counts, with gross and net production per code and per provider - Optionally filter providers and procedure codes. Net production is procedure fees for procedures completed in the date range plus adjustments and Writeoffs or writeoff estimates for the same procedures
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment plan overcharged procedures needing manual correction - This is for troubleshooting accounts where a procedure may be or will become overpaid due to a patient payment plan but an Income Transfer will not correct it.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging of Outstanding Insurance Claims - This report is a list of outstanding insurance claims as of the specified date that have the specified treating provider, for patients with the specified billing type assigned to the specified clinic
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily P&I For Specific Users Filtering On Entry Date Or Transaction Date -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments Entered by Users per claim, check, and clinic -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for patients with balances by treating provider and, optionally, clinic - Just like query 1311, but will work correctly between versions 21.1 and 24.1. Use in tandem with 1766 and 1312 to validate payment allocations.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of guarantors with provider income imbalances. - Filter option to only show guarantor accounts needing income transfers. For versions 21.1+.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with completed procedures in a date range and a specified Referred From source - Referral totals only include patients whose first completed procedure that is not a broken appointment code is in the specified date range
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gross production per patient within 90 days of the patient's first completed specified code -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claim Error Analysis - Custom claim tracking entries must have an error selected to show
DBMS version(s): MariaDB 10.5, MySQL 5.x
Completed appointments per month for the specified year -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Discount Plan Production and Adjustment Totals -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance payments for procedures completed in the date range by specified provider(s) - One row per payment, so a single procedure may be listed several times.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance Payments in a date range with deposit info - Can filter by clinic, deposit date, payment amount, Check number, or the treating provider of the associated claim.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Email list: guarantors of active patients - Useful for an email blast through a third party mass-emailer. Only shows guarantors with an email address that is potentially valid.
DBMS version(s): MySQL 5.x, MariaDB 10.5
First Ordinal From Referral Source that is a Doctor for Patients with the Specified Procedure Attached to a Scheduled Appointment in the Date Range - Only shows the first listed Referred From source that is a doctor. Only accepts one specified code.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Provider level practice health -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments Report by Payment Entry Date -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed Procedures in a Date Range with specified Clinic. - Displays Billing Provider and Carrier on the Primary Claim for the procedure, if one exists.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of appointments in unscheduled or broken status with appointment date in the specified date range. - Amount is sum of procedure fees less estimated writeoffs for procedures on the row's appointment.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insurance claims sent in a date range, with custom tracking history - Similar to internal Outstanding Claims report. Allows filtering by carrier and clinic. Also, shows claims for hidden treating providers, which the internal report does not do.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a specified Procedure Code completed in the Date Range, and their most recent date it was completed - Date range is only used determining if the patient is listed. Shows the most recent completed procedure date for the specified codes, even if the most recent date is after the date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Claims Not Sent Report With Agreed Fee, Carrier Filter, and Patient Number - Similar to the standard Claims Not Sent report but allows filtering by carrier.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Time card entries in a date range for a specified employee by time entered -
DBMS version(s): MySQL 5.x, MariaDB 10.5
User Logon/Logoff for a date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and income by procedure code and provider for a given date range - Can specify provider, procedure codes, and clinic on the procedure
DBMS version(s): MariaDB 10.5, MySQL 5.x
Patients whose first completed appointment has the specified code(s) and who have no scheduled or completed recall since - ProcCodes variable cannot be blank
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedures marked "Do Not Bill to Insurance" on appointments between the specified date and now - Appointment and procedure status do not affect the results
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with their most recently completed appointment date before the specified date - Total production is gross not net. For all Open Dental versions. Requires that Aging has been run today for accurate Outstanding Balance values. For recent versions, this should be done automatically by the Open Dental Service. For more information about
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referral Procedure Status - Pulls 'From' referrals
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments Per Operatory And Provider - Requires payments be allocated to procedures that are attached to completed appointments in the specified date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of webforms sent in the specified date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Summary statistics by provider. - Similar to #649, but filters on appointment date and time in addition to using procedure date for the PatCount and $Production columns.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Sum of PTO hours per employee in a date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance payments entered in a specific date range. - Includes payment, claim, carrier, and patient information. Intended to check that insurance payments match their estimates.
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patients by First Completed Appointment Date - Includes Patient Address, From Referral Sources, Referral Patient Note, Recall Scheduled Date, and Procedure Fees for All Time
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients' Primary Insurance Annual Max and Benefit Usage -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Daily Employee Time Clock Report* - Clinic is optional. Leave blank of clinics feature is not used, or to see time cards for all clinics.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Misc sheet field report for specified sheets created in the specified date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Employee Time Card Log - Break clock events are not included.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointment Information with Additional Patient Information -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patient Appointments Report - Lists patients with no completed procedures (excluding D9986 & D9987) that have a future scheduled appointment, as of the report run date, that was created in the specified date range.
DBMS version(s): MariaDB 10.5, MySQL 5.x
Families with credit balances before insurance -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients that have active popups - This query is for for versions 22.2 and higher. For versions 13.3 thorugh 22.1, see query #1112. For versions 13.2 and older, see query #762.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of same day patient payment amounts attached to procedures and adjustments, summed by payment type - Patient payment dates must be in specified date range.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure production and income report for completed procedures in the date range for patients with discount plans -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient Appointments in a specified date range. - Visit columns report information from inside the date range only. See Specifications for details on columns.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient Payment Report for specified patient. - See variable comments for details about how they are used.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure Code Category Production Analysis -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Current fees from each fee schedule for procedures completed in the specified date range. - See specifications for details on how the fee schedule is selected (selection addapted from https://www.opendental.com/manual/feeschedulelogic.html).
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient referral sources with at least the specified number of patients referred in the date range - Patient Referral Sources only show if they have a Scheduled Appointment after the date the report is run
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed appointments in the specified date range - Appointment must have a specified confirmation status, provider, and (optionally) hygeinist
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed appointments with attached procedures with a specified code, provider, and clinic - Code filter is required
DBMS version(s): MySQL 5.x, MariaDB 10.5
Writeoffs in the specified date range by carrier and provider -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Gross production per hour in the specified date range and hour range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of users who have login and logout entries in the audit trail for the specified day -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and Income by procedure, for procedures completed in the date range. - Ability to filter by procedure code, treating provider, and clinic. Requires procedures to be attached to adjustments and payments. Similar to 1629
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production by Day of the Week. - Filtered by specified Provider and Clinic, and excludes specified weekdays.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payments By Procedure Code Or Procedure Category -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Rescheduling efficiency -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production and income in a date range for specified providers, excluding specified hygiene procedure codes. - Similar to internal production and income reports. Can be run for Monthly, Annual, or Provider.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance Overpaid Report - Similar to the standard Insurance Overpaid Report, but includes the procedure's procedure code and clinic.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Referral Production and Income - Production and income is for transactions allocated to procedures completed on each patient's first visit date
DBMS version(s): MySQL 5.x, MariaDB 10.5
Procedure Payments. Shows individual payments on procedures completed in the specified date range. - Since procedures may show more than once, the ProcNum is listed so you can identify the duplicate procedures. For one row per procedure, see Example #1168 .
DBMS version(s): MySQL 5.x, MariaDB 10.5
Adjustments in the date range on procedures prior to the cutoff date -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Outstanding insruance claims report for claims with procedures in a specified code range. - May optionally filter by the claim's treating provider.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a positive outstanding balance that have the specified insurance carrier -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Families with credit balances before insurance where the last date of activity for any family member is prior to the specified date - This may work as an "unclaimed funds" or "escheatment" report.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Payment and Adjustment Report. - Similar to the Internal Payment Report but Includes Adjustments
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of appointments, unique patients seen, and appointments with lab cases for completed appointments in a specified date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Collection verification -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patient production, adjustments, and income per clinic specialty - Clinic Specialty is found in a patient's Patient Information
DBMS version(s): MySQL 5.x, MariaDB 10.5
Consent form signature verification - For patients with completed appointments in the specified date range with at least one non-excluded code on the appointment
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients needing planned appointments - Patients with treatment planned procedures excluding specified codes, where the patient doesn't have a planned appointment
DBMS version(s): MySQL 5.x, MariaDB 10.5
Provider's Scheduled Hours and Production Goal for Next Month - Hourly production goal comes from the Edit Provider window for each provider. Uses hours from the "schedule", not scheduled appointments. May be quite slow for some users.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed procedures with patient payments and no attached insurance estimates -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of Patients Created in a Specified Date Range and Count of Created Patients with a Completed Appointment by User -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Paysplits in a date range that are not attached to a procedure or are attached to a treatment planned procedure -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance aging report for specified codes -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for families with a positive balance before insurance as of the specified AsOf date, includes aging buckets up to and after the specified Date2 date - For Open Dental v21.1 and higher. Includes bug fixes so may not match standard aging prior to 23.3.37 but is more accurate. Runs using "Any Balance" and "Include Negative Balances". Can patient level aging. Make Date2 any date 3+ months prior to AsOf
DBMS version(s): MySQL 5.x, MariaDB 10.5
New Patient appointments in the specified date range with the specified code(s) attached - Considers the New Patient checkbox in the Appointment Edit window.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production from fee schedules associated with completed appointments with appointment date in the specified date range - Looks at fee schedule of insurance plans attached to each applicable appointment. Does not include patients with no insurance. Gross Production is based on fees in the specified Office Fee Schedule, not actual procedure fees.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of Appointments That Were Broken/Cancelled/Deleted/Moved In Specified Date Range On The Date Of The Appointment And Count Completed In The Specified Date Range By Clinic - Not for future data, only historical
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of pending supplemental insurance payments -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients with a completed procedure with one of the specified procedure codes, where the patient had a subsequent completed appointment - Date range does not apply to the appointment
DBMS version(s): MySQL 5.x, MariaDB 10.5
Active patients with have no scheduled apts showing patients who's last visit is in the specified date range - Shows the date of last visit, date last recall, next recall due date, if they have treatment planned procedures or not, and their outstanding account balance. For a dynamic date range, see #799.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Aging for patients with any positive balance after insurance estimates. Limited to patients assigned to a specified clinic - Aging for versions 21.1 to 24.1
DBMS version(s): MySQL 5.x, MariaDB 10.5
Insurance plans with benefits last verified in the specified date range -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Canada: Payment report like internal, but special handling of procedure codes 99111 and 99555. - May specify codes that payments must be allocated to. Payment amounts allocated to codes 99111 and 99555 are in their own columns, and not included in the Amount column.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with either Text or Email selected in 'Exclude Automated Messages' - Includes patient's assigned clinic.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Patients Needing Perio -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Treatment planned procedures for patients with a future scheduled appointment - Enter a date range for the future appointments. You may optionally limit to showing only specific procedure codes.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Prescriptions with specific drug names that were deleted in a date range. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
New patients production and income with clinic totals and grand total -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Completed appointments in the date range with the specified provider, with specified procedure codes assigned to the hygienist on the appointment -
DBMS version(s): MySQL 5.x, MariaDB 10.5
All active popups, not disabled or archived - For Open Dental version 22.2 and up. Similar to query 1112, which is for Open Dental versions 13.3 to 22.1, and query 762, for versions below 13.3.
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of patients with the specified problem and note -
DBMS version(s): MySQL 5.x, MariaDB 10.5
List of specified procedures completed in the date range, assigned to specified providers, with a sum of insurance payments that is greater than or equal to the specified InsPaidOver amount -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Production, collections, adjustments, and writeoffs summary by date for a given provider -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients with a scheduled appointment in a date range - For each clinic and appointment type that do not have a completed appointment.
DBMS version(s): MySQL 5.x, MariaDB 10.5
Appointments List For Broken And Unscheduled Appointments -
DBMS version(s): MySQL 5.x, MariaDB 10.5
reatment planned procedures attached to scheduled appointments in a date range. - Shows gross and net production, as well as the total of patient paysplits attached to each procedure.
DBMS version(s): MySQL 5.x, MariaDB 10.5