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:  1431                                                                                                                                                           

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 - In the example, for 7/29/2005
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
The account balances for all 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. - For versions greater than 7.3, replace GradeSchool with SiteNum to get the query to work.
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.
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 - (the example uses Sept 1, 2005). It will also give you the date of their last visit. Submitted by Jorge Bonilla, DMD
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 - usefull 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 mulitple times if they have mulitple 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
Patients names, addresses & phone(s) seen today -
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 before or after a given date, as is shows after ...As written only shows patients seen after 2007
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 specifed 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 $200, (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 Treament Planned procedures - leaves out diagnostic and preventative (D0*, D1*)
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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 patients with billing type indicated 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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
List of Patients with an appointment associated to a specific clinic and completed in date range. -
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
Procedures of treatment planned or completed status that were treatment planned OR completed in given date range, one specific 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): MariaDB 10.5, MySQL 5.x
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 date range, with insurance paid amt. - Listed by procedure, includes all procedures matching code for which there is a received or supplemental claim procedure.
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): MySQL 5.x, MariaDB 10.5
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 production. -
DBMS version(s): MySQL 5.x, MariaDB 10.5
Count of patients by billing type who have procedures . -
DBMS version(s): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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
Production and Income by procedure, for a service date range, summed by patient and service date. - Does not include adjustments.
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): MariaDB 10.5, MySQL 5.x
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): MySQL 5.x, MariaDB 10.5
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.