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

Result Count:  1392                                                                                                                                                           

QueryIDQuery, with Title and Notes
All claims that are currently open that were sent on a given day - change DateSent value as needed
All treatment planned procedures, ordered by patient - See also #50 & #56
Birthday postcards for a one week range - In the example, from 10/06 to 10/13
Daily patient payments organized by chart number and date entry. - In the example, for 7/29/2005
Daily Insurance payments organized by chart number - In the example, for 7/29/2005
Aging report which includes date of last payment - The last payment date is only for payments entered on the guarantor of the family.
Aging report which includes chart numbers -
Daily procedures report which includes chart numbers -
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+
All lab cases with a status of received, and appointment not complete -
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
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.
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.
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.
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.
For public health clinics, the production by gradeschool. - For versions greater than 7.3, replace GradeSchool with SiteNum to get the query to work.
For public health clinics, the number of patients seen at each grade school for a date range -
Count of patients, grouped by billing type - Includes inactive and archived patients
List of new patients for a given date range - Does not confirm patient was actually seen
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.
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.
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.
For Payment Plans, this lists guarantors of plans for which the date of the last payment is approaching. -
For Payment Plans, lists all charges due for a date range and includes billing type. - Example is for the month of March.
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
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.
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.
Patient payments for one day - For instance, cash, check, or credit card.
Patient payments for one day grouped by type, so only the totals for each type show -
A list of all outstanding preauthorizations -
List of writeoffs for a daterange and for one provider - Leave out the line containing ProvNum to get for all providers
Quarterly Production by billing type. - Does not include adjustments.
List of families seen in the last three years - Useful for generating a list of patients for Christmas cards.
A list of patients seen between two dates (based on procedures completed in date range). -
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.
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.
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
List of subscribers for a given carrier and groupnum. - Note: This is a list of subscribers, not patients. See 118 also.
Sums of Aging report without having to print the entire report. - If you do not update aging first, results will not be current
Appointment history for one patient - Change the 581 to the appropriate PatNum before running
Mailing information for guarantors of active patients. -
Daily payments summarized by type -
New patients for time span who were actually seen - Excludes ones who made an appointment, but then no-showed.
Patient contact info for patients not seen since a certain date - Excludes broken/missed codes. Submitted by Jorge Bonilla, DMD.
Treatment planned procedures, that are not in a scheduled apt - For a date limitation, see #194
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.
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
Production for date range with pay splits and tooth and surface -
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
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.
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
Insurance payments received after a certain date for the specified carrier, sorted by carrier - Chart Number used, can use patnum if needed.
Received Lab Cases with sent and received date with patient number -
Patient lifetime revenue and TP'd procedure totals - also includes PatNum and patient age, does NOT include insurance revenue
Treatment Planned Procedures with Referror and Insurance -
Query scheduled procedures for code with date range -
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)
Number of active patients of each age - Doesnot imply that a procedure has been completed on patient
Shows all records with duplicate first and last name -
Active patients list who have seen a hygienist within a date range -
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
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)
Count of patients by Carrier with procedures completed in date range -
Income by insurance company for date range -
Carrier and patient list seen in date range - to include only active patients add WHERE...AND PatStatus=0
Count of active patients seen in a date range grouped by billing type -
Received Preauths over last X months -
Patient payments in date range -
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.
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.
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.
Insurance claim procedures with UCR fee, InsEst and InsAmtPaid - Change date range as needed, does not distinguish between received claims, sent claims etc
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
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.
Patients from given city seen in date range - theoretically helpful for splitting practice
List of patients with the specified code attached to a scheduled appointment with appointment date in the specified date range -
Patient and insurance payments by patient for date range for given provider - Patient and insurance payments by patient for date range for given provider
Adjustments summed by type -
Sum of payments made by carrier for procedures in a date range -
Insurance income for time period summed by guarantor and carrier -
Aging Report for Balance over 90 with no payment in last 30 - SEE REPLICATION WARNING for versions before 14.3.1.
Find an Insurance check by Check Number - Change check number between the '%%' symbols
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.
Find an Insurance check by Amount and Date -
Get Patient info based on subscriber id (or part of it) - Add more fields from patient table as needed
Procedures with notes for date range (completed procs) -
Find insurance plans without assigned benefits - Returns subscribers of plans (vs patients using plan)
Patients with saved Treatment Plans and date of plan -
Patient, provider, balance, like shown in patient payment window - SEE REPLICATION WARNING for versions before 14.3.1.
CommLog notes by Type -
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.
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.
Patients names, addresses & phone(s) seen today -
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.
Referred procedures by date range -
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
Patients' image count - (only returns patients with images, runs fast), note that images include PDFs stored by Open Dental
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.
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.
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 -
Outstanding insurance claims by carrier -
Outstanding insurance claims for a particular carrier -
Guarantors of families where no payment has been made in 1 Month and they are over 90 days past due -
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)
Total Anticipated and incurred writeoffs for procedures completed in range - Like 116 but sum only
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
Completed procedures for date range showing tooth surface - Change dates to change range
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
List of active patients referred to you or from you in date range - For Versions 17.1 and greater. Please update your version accordingly.
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
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
Commlog entries in the specified date range, for the specified commlog type. -
Hygiene Production For Time Period -
Hygiene Summed by Procedure for Date range -
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. -
Count of Patients seen by each provider in date range - A patient will count once for each provider seen in the date range.
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
Patients not seen since a date plus proc count -
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
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.
Outstanding insurance claims by Date of Service not including Preauths -
Preauths received in last year but procedures not scheduled -
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
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
New Patients in Date Range with Date of last visit and Procedure Count -
Patients with a given insurance fee schedule -
Check secondary claims received in date range for writeoff amounts -
Mailing List for patients with TP procs without a scheduled apt -
Recall information with e-mail -
Count of complete appointments in date range -
Treatment planned procedures for patients with no scheduled apt with specified carrier - (see also #50 & #56)
Treatment Planned procedures that were treatment planned in a specific date range and which provider treatment planned it -
Patient list of Sum of the Fees of all Treatment planned and all scheduled procedures - SEE REPLICATION WARNING for versions before 14.3.1.
Active patients who have had recall disabled -
Treatment Planned procedures that match codes in list, for patients with no scheduled appts containing the specified codes -
Daily procedures call list - Runs for 'today'
Insurance estimates and paid amounts of claims that were created in Date Range -
End of day call back list for completed procedures -
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
Addresses (with insurance) of active patients with tp procs with no sched apt -
Addresses of active patients (with no insurance) with tp procs with no sched apt -
Treatment Plans Master - This will give you a summary of all of your save treatment plans... See comments in query
Outstanding insurance claims by Date of Service, secondary claims ONLY -
Balance and Fee for every procedure for a given patient - (useful when entering procedure split payments)
Patients that have Service Year instead of Calendar Year insurance benefit - with count of service year benefits
Count number of Active patients in each billing type. -
Production and Income for a particular patient - (with adjustments, insurance income by inspay date and writeoffs)
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
Returns all treatment planned procedures for active patients without a scheduled OR planned apt, who were last seen in a given date range - SEE REPLICATION WARNING for versions before 14.3.1. 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)
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)
Received insurance claims FROM a particular carrier showing estimated vs paid amounts -
On Hold insurance claims -
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
Daily Patient Payment Report - Cannot Export Patient payment report as of version 6.0.4, so this is a way to get the data exported
Active Patients listed with Insurance Carrier -
Lists all patients with billing type indicated and how much they have paid total, ever -
Insurance Payments received in a given period, summed by carrier -
Insurance Payments entered and Checks received in a given period - not summed by carrier, useful for finding discrepancies*
Last visit before given date for all active patients with phone numbers -
Patients of specifed age range and gender -
Patients (in 'Patient' status) that have Service Year instead of Calander Year insurance benefits -
Active Patients who have had a particular proc completed relative to Active patients who have had any proc completed within a given date range -
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
Patient count by city, with at least 1 completed procedure -
Patients with no entry in the recall table - Not for patients with a recall due, but no appointment.
Guarantors (heads of households) of patients with no ins - with address
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
Completed Production in Appointments by Assistant -
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
Scheduled treatment for date range with apt notes includes fee and patient portion -
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. Does not fit quite on one page, export as a file and print.
Subscribers ordered by employer - lists active patients who are subscribers with insurance through each employer like 67, but lists each subscriber instead of count
Adjustments of non-zero amounts - change date range as needed
EMail Addresses of Active Patients -
"Patient" status patients with procedures completed in the specified date range and an email address entered. - Includes date last seen within the date range
Guarantors of patients not seen since a given date -
TP Procs with date of treatment plan of patients with no scheduled appointment with date of TP > given date - Like #50 with date limitation
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)
Referred out patients in a date range, with specialty and status - For Versions 17.1 and greater. Please update your version accordingly.
Mailing Info Active patients who have no email address for either themselves or guarantor -
Phone Numbers for Active patients who have no email address for either themselves or guarantor - SEE REPLICATION WARNING for versions before 14.3.1.
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%'
Recall info for insured patients with a specific insurance carrier, and are due for recall in the specified date range. -
Patients seen in date range by specified providers with age, zip and sum of fees for procs completed - Displays gross production.
Aging with Outstanding Insurance Claim info and Patient Balances -
Aging with Outstanding Insurance Claim info and Family Balances, Summed by Guarantor - SEE REPLICATION WARNING for versions before 14.3.1.
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)
Birthdate query for mailing including year -
Referred out patients by doctor - For Versions 17.1 and greater. Please update your version accordingly. Editable user date range filter for referral date.
Patient count by age, with at least 1 completed procedure -
Aging report which only includes families who do NOT have a balance >90 days AND who have a positive balance (owe) -
Returns patients with unscheduled Treament Planned procedures - leaves out diagnostic and preventative (D0*, D1*)
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.
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
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
Appointments in the past that are scheduled but not complete or broken or unscheduled -
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
List of all procedures of status 'Existing Current Provider' in date range -
List of all completed work with fee of $0 and with 'D Code' in date range with standard fee listed -
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
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
New Patients, Count per week for a given date range. Active patients only. -
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
Total production in Date Range for a list of specific procedure codes -
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
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
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
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
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 -
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
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
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
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
Outstanding Preauths by Date Sent with PatNum -
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
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
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.
Patients to Archive: Last visit X days ago for all active patients with balance less than given amount - helpful for archiving patients
Patients with a specified insurance carrier seen in date range -
Patients with a specified insurance seen in date range Who have a balance greater than given amount -
Returns inactive, archived, deceased and nonpatients that have scheduled appointment with apt date and time - Limits to appointments scheduled for Today or later
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
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
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
List of patients with appointments for Today with primary insurance carrier listed - Also lists sum of fees for day and insurance type
Lifetime income from new patients that started in date range - Also includes patient address and date of first visit
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
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.
Treatment planned work totalled by patient with annual ins max, ins used and name of carrier - Warning - may be slow
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
ALL Fee Schedules all procedures, arranged by category with fees - Set fee schedule name at top
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
Appointments that are broken for a date range, with note -
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
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
Outstanding insurance total for a time period, insurance estimates, writeoffs, with carrier information -
Get questionaire results for export and analysis - Replace term 'married' with desired search term or eliminate all between the %% to return all questions
Last visit before given date for all active patients with phone - Also shows last seen date for 1 specified provider
Insurance plans for patients of a given carrier, with guarantor, social security number and birth date -
claim count by insurance carrier for date range with sum of fees, estimates and paid amounts -
List of procedures for a date range and specified carrier showing patient name, service date, procedure code, primary insurance carrier, fee, sum of ins est, sum ins paid -
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.
Monthly production and income report - SEE REPLICATION WARNING for versions before 14.3.1. counts insurance writeoffs by procedure date (PPO) For all providers
Phone numbers and status of patients in a given zipcode -
Basic deposit query, returns date of deposit and amount in given date range - Change date range as needed
new patient count by zip code - with total
Outstanding Preauth Procedures by Date of Service with Procedure Codes - Edit interval if desired, currently older than 30 days
Outstanding insurance claims by Date of Service not including Preauths with fee total and insurance payment estimate -
Calculate current or historical accounts receivable, collectible, outstanding insurance estimates - SEE REPLICATION WARNING for versions before 14.3.1.
Insurance (Estimates or Paid) for a month (by date of service) -
Estimated Writeoffs Outstanding -
Patients with balances who do not have insurance - (not family balance, patient balance)
Count Per Doctor of incoming referrals in date range with last referral date - For Versions 17.1 and greater. Please update your version accordingly
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
Patients with prescriptions for a specific drug in given Date range -
Patient Refunds in Date Range - Looks for any negative patient payments that are not income transfers
Verification list for appointments on given date - Includes Carrier Phone and Procedures in Apt abbreviates to first 15 chars of carrier to save space
Number of procedures of each code completed with claims made to a specified insurance carrier in date range by provider - Includes Count, ave fee, sum of fees, sum amount paid
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
Labs received in the last 60 days with instructions - interval can be changed
Archived and Inactive patients - with last seen date and completed procedure count
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
Active patients - with PatNum, Date of First Visit and Address
Treatment planned work that has not been saved to a treatment plan - excludes listed procedure code matches, like preventative work
Questionaire results for export and analysis - Frequency distribution of answers for specified question(s)
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
Claims representing service provided in a given time period for a given carrier - with phone number, claim status, Ins Est and Ins Paid
Call back List for given procedures for given date range - uses guarantor home phone, other numbers can easily be added
Specified procedures representing service provided in a given time period for a given carrier - with phone number, claim status, Ins Est and Ins Paid
Home and wireless Phone numbers - of active, non and inactive patients, perhaps for uploading to your phone
Patient Mailing info with given carrier seen since given date -
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)
New patients with no complete procedures - Uses Date of First Visit field.
Insurance effective dates with carriername - for active patients matching a carrier name criteria
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
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
Incomplete procedure notes for date range with provider -
List of active patients with no appointments in date range - includes phone numbers and address
Active patients who have specific codes in treatment plan status - with addresses
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.
Commlog entries for the day that have notes -
Commlog entries for date range that have notes -
Commlog entries with a key word -
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.
List of all patients with dual coverage, with guarantor and names of carriers - fixed for 7.5+
All patients with values for a specified patient field def name - (full or partial field def name, all results, for summary see 312)
Summary of All Patient Field Def Entries - Grouped by fieldname and value
Summary of Patient Field Def Entries, for field names matching given criteria - grouped by fieldname and value
Mailing information with birthdate - for "Patient" status patients with a completed procedure in the last 24 months whose birthday falls in date range
List of "Patient" and "NonPatient" status patients without a referral "from". - For Versions 17.1 and greater. Please update your version accordingly
Show feeschedule for each patient - whether through primary insurance, patient level feesched or provider fee schedule
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
Inactive and Archived Patients who had a particular insurance carrier - Will not fit on a report page, needs to be exported as .xls
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
Lists all patients with billing type indicated with contact info - NOTE: user inputs any part of billing type, replace 'Standard' with your billing type
Appointments in date range for a given provider - with appointment status, age, and procedures
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
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).
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) -
Procedures on received claims with zero payment -
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
List of patients with a given fee schedule, showing insurance information, ordered by carrier and then group name -
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.
Show count of active patients using each special 'Allowed' type fee schedule through primary insurance -
Returns information about procedures where the amount paid+writeoff is greater than fee charged patient - Now a standard report
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.
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.
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.
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.
Completed procedures in date range, with fees and provider ordered by patient. -
Completed procs in date range, with fees summed by patient. -
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.
List of Patients with more than one insurance. -
New patients for a time span, with billing type. - New patient date based on completed procedure with fee>0.
Carrier Phone List for Printing, only includes carriers with active patients using them -
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.
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).
Insurance claim procedures with UCR fee, InsEst and InsAmtPaid. - Change date range as needed, does not distinguish between received claims, sent claims etc.
Sum of appointment lengths for time period. - Helps measure utilization.
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.
Outstanding Insurance Claims (not preauths) with patient, date sent, plan number, claimfee and ins estimate for a given carrier. -
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.
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 -
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.
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.
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
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
List of patients of a given age range with count of completed procedures(at any time) -
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
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.
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.
Empty Appointments for date range. -
Anticipated new patient production in date range. -
List of active patients in alphabetical order with last appt, and next future appt. -
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
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.
Mailing List for guarantors of all patients with insurance. -
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.
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)
Guarantors of patients with benefits remaining AND treatment planned procedures, with first names of patients and addresses. - Assumes calendar year benefits
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.
Count of patients by patient status. - i.e. Active, Inactive, Archived, NonPatient, Deceased, etc.
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.
Count of patients with an existing appointment over a date range that has a specific attached procedure. -
Count of future recall entries grouped by recall type (i.e. Prophy, Perio, etc) where the patient has an "Active" status. -
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.
Active Patients: Patient Name, Balance, Family Balance, SSN, Date of Birth and Last Date of Service -
Patients with birthday in given date range who have a scheduled apt that is also in that date range with date of that apt. -
Scheduled appointments in a date range, with provider and email address, home and cell phone, PreMed and New patient Flags. - Useful for Televox.
Average daily production for a time period, summed by day of week and overall. -
List of referred procedures in a date range that are in a given list. -
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.
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.
List of Patients with an appointment associated to a specific clinic and completed in date range. -
List of patients associated to a specific clinic. -
Procedures on received claims with zero payment with carrier listed for a particular carrier. -
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.
Guarantors of patients with a particular insurance where the guarantor is of a certain age (in this case 21 or over). -
Utility, missing DateFirstVisit in patient field. -
Treatment Planned Procedures, one specific code. -
Date and time that a claim was first sent and last sent for a patient as well as count of times claim sent. -
Deleted Procedures viewer. -
Income by Carrier for date range (or current day) With patient payments by type. -
Returns mailing list of guarantors of patients with unscheduled Treament Planned procedures. - Leaves out diagnostic and preventative (D0*, D1*).
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 -
Names of active patients with appointments on unscheduled list. -
Compare number with insurance vs number MARKED in patient table as having insurance, should match. - SEE REPLICATION WARNING for versions before 14.3.1.
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.
Active Patients not seen since a given date (but have had a procedure completed at some point) who have no scheduled appointment. -
Active Patients with birthday in current month, with Age, Birthdate, Name, Address, and Email. - Sorted by day of month for birthday.
Patients with no insurance, with address -
Addresses for patients who referred other patients in a given month. -
Guarantor address for: Active patient, with insurance plan, no scheduled or planned appts, treatment planned procedures, $0 fam balance. -
Procedures completed with received claims and amount paid=0 with patient name and date of procedure, fee, amount paid. -
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.
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.
Procedures with key word in note for date range. -
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. -
Patients with plans with a specific insurance general max. -
List of all TP procedures with a specified priority, within a user-defined date range. -
Appointment List report with Day of Week added. -
Customers with production exceeding given amount between two dates. -
Mailing list: Families that have been seen since a given date and have a given account billing type who have zip codes. -
Mailing list for guarantors of active patients. - Also groups by address to prevent mailings to same address with different families.
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
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.
Sum of insurance income by procedure date for a date range for a given provider or providers -
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. -
Patients currently in the specified age range who have generated income in a date range. - Income generation is based on the payment date.
List of Active patients seen in date range with procedure count. -
Patients seen since date with user defined insurance carrier with fee schedule shown. -
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.
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). -
Count of active patients of (or older than) a given age. - Includes patients without a birthdate as well.
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.
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.
Patient Payments for a given patient between two dates with payment type and check number. -
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
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).
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.
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.
Patient (individual not family) balances, less than zero (credits). -
Count of patients with procedures completed in the date range with and without insurance. -
See which active patients have had a given procedure completed, and what their primary ins carrier is (if any). -
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. -
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.
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).
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. -
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. -
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. -
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.
Payments for a given patient for a date range with payment type. -
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.
How many days am I open in a date range? -
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.
List of active patients that do not have active insurance plans. -
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.
Procedures of treatment planned or completed status that were treatment planned OR completed in given date range, one specific code. -
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.
Claims received in date range with summary information, fee total,claim fee total, amt paid. -
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.
Count of referrals received grouped by referral and zipcode. - For Versions 17.1 and greater. Please update your version accordingly
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.
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.
Number and percentage of patients (active and inactive) who have had a particular procedure performed. -
Count of Procedures and Total Production by Family, ever. - Top 100, remove limit if desired.
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
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.
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.
Gross production for active patients by zip code. -
Unearned income report with Payment Type and Unearned Type. -
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.
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.
Income for patients who are younger than a given age, defaults to 21, in a date range. - Uses date PAID as date criteria.
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.
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.
List of procedures over a specified date range where there is no signature associated with the procedure. -
Insurance Payments received for a specific carrier in a specified date range. -
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.
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.
Return firstname, lastname, email address for all patients that had a specified procedure completed in a given date range. -
Return firstname, lastname, email address and patient number for all patients that had a procedure in a list completed in a given date range. -
Treatment planned procedures of active patients with the treatment planned procedures matching a pattern. -
Count of patients by Carrier who have procedures completed in date range with procedure count and gross production. -
Count of patients by billing type who have procedures . -
List of patients with billing type and fee who have procedures completed in date range. -
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. -
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.
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.
Service Notes for patients with appointments scheduled (or ASAP) on a specified day. -
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.
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.
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.
Summary of payments by payment type for date range. -
Procedures with received insurance claims in given date range where insurance paid zero. -
Procedure fee production and the production where a given fee schedule is used, summed by provider -
Count of communication log entries by type for a date range. -
Outstanding insurance claims by Date of Service for a date range not including Preauths with fee total and insurance payment estimate. -
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.
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'.
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).
Find medical eclaims sent in date range. -
Active patients who have not had a particular procedure completed in date range. -
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.
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.
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
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.
Production and Income by procedure, for a service date range, summed by patient and service date. - Does not include adjustments.
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.
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.
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.
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='%%'.
Mailing Information for patients without insurance, lists guarantors, valid addresses. -
Appointments on schedule with no attached procedures. -
Balances of patients with appointments on a given date. -
New Decay from first exam to second exam in time period. - SEE REPLICATION WARNING for versions before 14.3.1.
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.
Active patients who have a specific code in treatment plan, with addresses. - Like 304 with date range.
Aging listed by Aging, so 90+ grouped, then 60-90 etc with phone numbers. -
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
Patients on unscheduled list with all phone numbers. -
Recall Reminders Sent in a specified date range. - Shows patient, reminder description, and date/time the reminder was sent. These are commlog entries of 'Recall' type, so if any don't belong they should probably be changed to Misc or some other type, so they will not be counted as reminders.
Somewhat like 325 shows work treatment planned in a date range but that is also in a SAVED treatment plan and not scheduled. -
List of patients seen in date range with provider that did procedures where the patient has capitation insurance. -
Recall info for insured patients that are due in the specified date range. -
Patients who have been seen in date range who have double insurance. -
New Patients in given date range who do not have a scheduled appointment. -
Show procedures completed in a date range for patients who have a given fee schedule attached to their primary insurance, in this example Delta. -
Simple sum of patient payments and sum of insurance payments for a given time span. -
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. -
Guarantors of families in which none of the patients have been seen after a given date. -
Gross production and income by provider. - SEE REPLICATION WARNING for versions before 14.3.1. Today, MTD, YTD.
All recall appointments in date range with status. -
Procedure level payment report for those who link patient payments to procedures. -
Lists of patient's carriers and employers -
All active patients list with clinic and email. -
Patient count by Gender. -
Sum of adjustments by type for a given provider and date range. -
List non zero, non-general deductibles. - Useful for finding patients with potentially erroneous benefit information.
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.
List of patients in 'Patient' status that do not have a scheduled or planned appointment and have procedures treatment planned in the date range. -
Insurance aging report. - Outstanding insurance aged by date sent 0 to 30, 30 to 60, 60 to 90 etc.
Unscheduled List with phone numbers added. -
Time Card summary for all Employees. -
Find deposit date and insurance check info by checknum. -
Insurance info for patients with appointments in scheduled status with a date in the specified date range. -
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.
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.
Find procedures in a date range where the writeoffestoverride has been used. - Edit Dates to change date of insurance payment range.
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.
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.
New Patient Production in a time period, where the patient was new in time span and procedures were completed in time span. -
Lists ALL active patients and referral source. - For Versions 17.1 and greater. Please update your version accordingly
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.
Search for Insurance Check by Check Number, includes deposit date. -
Possible duplicate patients with identical first and last name with birthdate and preferred name listed. -
Very Likely duplicate patients with identical first name, last name, birthdate and preferred name. -
List of all providers who saw patients in a date range. - With date of first and last procedure completion in date range.
Shows patients with missing teeth. -
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.
Average Daily Production by provider. -
Patients addresses, seen or to be seen in given date range, who have insurance. -
Ins income by provider for date range. -
Procedures with a given string in the notes. -
Outstanding insurance claims by Date of Service for a date range not including Preauths. - With fee total and subscriber information.
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
Aging report for patients that are insurance subscribers, with subscriber ID numbers. -