Home                            Welcome to the Open Dental Query Examples Page
 

        If you find a query you like, copy it to your favorites, see http://www.opendental.com/manual/queryfavorites.html
        Select the contents of the textbox with the query in it, then right click to choose <copy>.
       
        You may limit your results to Query Examples that are associated with one or more categories, up to three categories.
        A single Query Example is usually found in more than one category.  Selecting multiple categories returns Query Examples
        that match ALL of the selected categories, not just any one of the selected categories, narrowing your result set.

        Don't see what you need?  Call us and we can help you find it.  What if it is not here?  We will do custom queries, our usual
        starting rate is $90 for a new query, more if it is very complex ($90/hr).  If  you just need one or more fields (columns) added that are
        already available, we may be able to add that at no charge.

        To submit a query request, go to our Query Request Form
      
        Return Queries About:     AND    AND

Result Count:  1171                                                                                                                                                           

QueryIDQuery, with Title and Notes
All claims 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 -
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 -
A list of all referrals you have received for a month - (in the example, its 9/1/05 to 9/30/05). Shows how many patients referred by each source.
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
List of referral sources, how many patients referred, and how much production from each source - In the example, it lists all plans where the date of the last charge is in the month of July, 2006.
Number of patients seen for a given date range (two years in the example). - Patients will only be counted once for the date range, not once for every visit. This is a good way to measure active patients.
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 subscibers 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. - Used to export to text file for merge letters. Jan, 2006 in the example
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.
Calculates the total income from each carrier in a given period of time. - Might be useful for comparing 1099s received from insurance at the end of the year.
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 - Change the dates to get the date range you want. Submitted by Jorge Bonilla, DMD. NOTE: look for other queries as this one does not differentiate referred FROM and referred TO
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
MISLEADING QUERY List of all patients, their balances, and their most recent payments. - NOTE: not just active patients, note also that guarantors are the only ones that actually have aging balances, so don't count on this one to give useful information.
List of subscribers for a given carrier and groupnum. - Note that it is 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
For a user set time period, this query will return the number of procedures, the total fees, and the percentages of each for each category of dental codes. - SEE REPLICATION WARNING for versions before 14.3.1.
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.
Patients not seen since given date. - Submitted by Jorge Bonilla, DMD. SET @pos=0;
Treatment planned procedures, that are not in a scheduled apt -
Answers the question: during a given period, what is the production generated by different sources (like ads, signage, yellow pages, other referrals) including residual (that is, even if the initial referral was made during a different period) Summary Fee - /*uses referral listed first in program interface, usually first referral, can be switch to use last referral*/
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 -
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, with chart number, sorted by carrier - ChartNum may be easily replaced with PatNum
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 - (remove aptstatus constraint to return all appointment types)
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 - SEE REPLICATION WARNING for versions before 14.3.1. Useful for elimintaing potential duplicates...TO see PatNum, choose 'raw' option button in query window
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 - will not return patients with no fees on first visit date or if there is 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 -
Recieved Preauths over last X months -
Patient payments in date range -
New patients in date range with address and ref source -
Returns guarantors of patients who have not recieved 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. /*78. Active (status) patients with no scheduled apt who have not been in for a time period with the date of their last completed 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 recieved 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 - SEE REPLICATION WARNING for versions before 14.3.1. 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
Scheduled procedures with associated appointments and patients for date range for given code -
Patient and insurance Payments for date range for given provider -
Adjustments summed by type -
Count of patients seen in date range by carrier -
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 -
Insurance and Patient income for time period summed by guarantor and carrier - Patient income will be repeated for multiple insurance carriers in family
Gives Count of Insured and not Insured New Patients for date range -
EXPORT PATIENT DATA TO IMPORT INTO ANOTHER PROGRAM: - The file created as a result of this query will be c:\TEMP\patients.csv One should ensure that the c:\TEMP folder exists 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) - Note that since we do not delete notes, there may be multiple entries if notes were altered To see last note only, try query 136 below
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 - Exludes patients who have never actually had a procedure performed
Count of new patients by clinic - Exludes patients who have never actually had a procedure performed
Patients names, addresses & phone(s) seen today -
New Patient count, insured and not insured over date range - 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
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 recieved or supplemental
Returns procedures with estimated insurance greater than charged fee claim proc status is not recieved 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 with tooth surface added - 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, whether or not you saw them - sorted by referral, can be changed easily
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 - SEE REPLICATION WARNING for versions before 14.3.1. Useful for dentists trying to sell non recall procs
CommLog Entries for Date Range -
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 - (repeats if patient seen by multiple providers)
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 -
Patients 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 recieved in last year but procedures not scheduled -
Completed procedures for date range, limited to specified procedures -
Returns all notes for procedures in given range (check 98) - SEE REPLICATION WARNING for versions before 14.3.1. Great for reviewing the days notes, export to view full, longer notes
New Patients in Date Range with Date of last visit and Procedure Count -
Patients with given insurance fee schedule-revised for new table 'feesched' -
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 - can be edited to only include certain procedures by using pc.ProcCode IN ('D1234', 'D5678')
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 - SEE REPLICATION WARNING for versions before 14.3.1. 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 Calander 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 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)
Recieved 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 - NOTE: user inputs any part of billing type, replace CASH with your billing type
Insurance Payments recieved in a given period, summed by carrier -
Insurance Payments entered and Checks recieved 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 (Active) 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 Zipcode, with at least 1 completed procedure - 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 without any recall -
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
Production by assistant - ONLY COUNTS production in appointments
Mailing list of guarantors of patients with a particular insplan - 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 - SEE REPLICATION WARNING for versions before 14.3.1. useful for start of day apt query, also counts each porcedure as new patient procedure, recall procedure or other
New Patients with referral source, production diagnosed on first visit, total of payments that day with gender position and age - Does not fit quite on one page, so perhaps eliminate age and gender etc to print
Subscribers grouped by employer - lists active patients who are subscribers with insurance through each employer like 67, but lists each subscriber instead of count
Benefit information by patient number - Edit query as needed with patient number
Adjustments of non-zero amounts - change date range as needed
EMail Addresses of Active Patients -
Email Addresses of Active Patients Seen in Date Range With Date Last Seen in Date Range -
Guarantors of patients not seen since a given date - SEE REPLICATION WARNING for versions before 14.3.1.
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 by doctor, with specialty and status - Optional date range, use large date range if none needed. Obsolete as of version 15.3.1. For later versions see query 1103.
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. usefull in conjunction with #197
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 patients with a specific insurance carrier, and specific date range, with date of last visit - Change dates and carrier AS needed in first line of query, for blue cross you could use @Carrier='%Blue%'
Patients seen in date range with age, zip and sum of fees for procs completed - (apx production, as it neglects writeoffs)
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, optional date range - use large date range if no date limitation needed
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*)
Referrered out patients for a specific ref source - optional date range, use large date range if none needed
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 recieved 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 recieved 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 so 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 -
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 - 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 - 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 - 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 seen in date range -
Patients with a specified insurance seen in date range Who have a balance greater than given amoun -
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 'Recieved' 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 listed with primary Insurance Carrier, Birthdate, Name and Address - Sorted by day of month for birthday, can be easily change, Add Address2 if needed
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 completed with claims made to a specified insurance carrier in date range matching the procedures in list - with patient name and date of procedure, fee, amount paid
New Patients with referral source, gross production completed in date range with ZIP and Age -
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 with 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 - 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 - range is set very broadly by default
Incoming referrals with refdate for a doctor with a specific last name and (optional) first name -
Pull patients with prescriptions for a specific drug in given Date range -
Patient Refunds in Date Range -
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
Daily Report - Various Daily Statistics - SEE REPLICATION WARNING for versions before 14.3.1. Calculate current or historical accounts receivable, collectible, outstanding insurance estimates It is advised to print each daily report (production, procedure, payment, writeoff, etc.) for supporting
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 -
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 - 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 a specific code in treatment plan - with addresses
Patients with date of first visit in given range - 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 -
List of all of 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 patients seen in last x months whose birthday falls in date range
List of patients without a referral "from". -
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
Archived Patients who had a particular insurance carrier - edit the CarrierName LIKE('%Blue Cross%') section below with desired carrier name. Will not fit on sheet, export to Excel or OpenOffice to deal with that
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 'Bad' with your billing type
patient, procedure and insurance information for appointments in date range - with fees and Standard (or other fee schedule) fee for each procedure
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 - and 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 insured with carrier, group name and group number for a given fee schedule, ordered by carrier and then group name - SEE REPLICATION WARNING for versions before 14.3.1.
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. - Tells whether they are a patient (have a PatNum) or not.
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 -
#Hygiene Appointments and Distinct Hygiene Patients in Date Range, only a count - 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) - SEE REPLICATION WARNING for versions before 14.3.1.
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 and phone numbers. - 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 with sum of family estimated balances for patients seen in period and individual family after insurance balances. - SEE REPLICATION WARNING for versions before 14.3.1. Usefull 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 with phone numbers and primary provider. - 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.
Count of patients by patient status. - i.e. Active, Inactive, Archived, NonPatient, Deceased, etc.
Count of appointments (with specific attached procedures) by patient over a 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), currently set to 7AM to 7PM, ca - 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. - SEE REPLICATION WARNING for versions before 14.3.1.
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 assoiciated 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. - SEE REPLICATION WARNING for versions before 14.3.1. 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. -
Subscribers (not neccesarily 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. -
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, 1 year and 17 years in this example. -
Patients listed who have generated income in a time period (by date of payment). - Insurance and Patient income include limits to patients whose age is at or between given ages.
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). -
Patient count with ins vs patients without. -
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. -
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 by appointment, useful for setting produciton goals in offices with PPO insurance plans. Anticipated writeoffs shown after primary 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. -
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.
Production and Income by ref source for date range. - SEE REPLICATION WARNING for versions before 14.3.1. KEY POINT: only includes referrals received and income collected during that period. Note: duplicates for multiple 'from' referrals in period
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. - SEE REPLICATION WARNING for versions before 14.3.1. Returns patient name, balance, family balance, last seen date, address, number of procedures ever completed.
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. -
Get all fees for all or many fee schedules, takes a lot of manual editing to be done by open dental staff. - ADVANCED users only.
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. -
Insurance Preauths received where procedures is complete and was completed in given date range. - Shows Patient's name, procedure code, procedure fee, date recieved 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. - SEE REPLICATION WARNING for versions before 14.3.1. Only works if you put patient payment splits by procedure.
Production and Income by procedure, 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). - 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). - 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.
Dr Crippen's Report: Current and historical accounts receivable, collectible, and outstanding insurance estimates as of a specific date. - SEE REPLICATION WARNING for versions before 14.3.1. See text for description. It is advised to print each daily report (production, procedure, payment, writeoff, etc.) for supporting documentation. Reporting numbers can change overtime and the daily re
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 patients with any insurance carrier, and specific date range, with date of last visit. - Change dates as needed in first line of query.
Patients who have been seeen in date range who have double insurance. -
New Patients in given date range who do not have a second appointment scheduled. -
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. - SEE REPLICATION WARNING for versions before 14.3.1.
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. -
List all patients with carrier and employer. -
All active patients list with clinic and email. -
Patient count with and without insurance. -
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.
Returns all treatment planned procedures for active patients without a scheduled OR planned apt. - With phone nums where the procedures were treatment planned in a given date range.
Insurance aging report. - Outstanding insurance aged by date of service 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 check info by checknum. -
Insurance info for patients with apts in 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. - 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. -
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 Check by Check Number, include 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.
Payments to accounts of defined billing type in date range. - Put billing type, e.g. Standard Account, in place of billingtypehere.
Shows patients with missing teeth. -
Active Patients referred in date range with ref source and phone numbers. - 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. -
Claims of status 'Sent' or 'Recieved' limited by Sent Date with Patient Name, Amount billed insurance, Amount billed patient and Date sent - Edit Dates to change date of claim last sent range
Claims of status 'Sent' or 'Received' limited by Service Date with Patient Name, Amount billed insurance, Amount billed patient and Date sent - Edit Dates to change service range.
Daily procedures report with writeoff column and fee-writeoff column and claim procedure status. - Does not show description or clinic can be limited by provider.
List of Patients with balance over 90 days AND Billing Type is "Bad Debt-Sent to collections" - Includes Patients Name, Patient Address, Phone Numbers, SSN, DOB, Patnum, Last Date of Service, Date of Last Payment, Last Amount Paid, Balance Due Amount.
Provider summed report: gross production, patient income, insurance income, hours worked (by provider time checked in appointments) for a given date range. - Change dates below, both the start and end dates are included in the results.
New patients in last 30 days with no follow-up appointment. Shows Primary Provider, Patient Name, $ TP Amount, homephone number and First visit date. - Could also show one provider seen on date of first visit.
Aging and balance summed by primary provider of guarantor. - Note: one would likely see a skew toward the default practice provider, this would be correted over time as you fix the priprov of the guarantors.
Guarantors of families in which none of the active patients have been seen on or after a given date, where no member of the family has listed insurance carrier(s) -
New patients in date range with exam type (edit if needed), insured flag, dollars treatment planned, amount paid at first visit, and date of subsequent visit scheduled or complete, also returns a provnum for one of the procedures and the username that set - Edit as needed.
Patient count by Billing Type of patients that have had a particular procedure completed in a given date range. - Edit date range at top.
Procedures completed in a given date range with given provider, carrier (so must be in a claim) and that are in a list of D Codes. - Edit date range at top.
Count of Hygiene Appointments and Distinct Hygiene Patients scheduled in future Date Range. - Only a count.
Duplicate Procedures in Date Range. - Edit date range at top.
New Patients in date range with referral source, current treatment planned production, scheduled production, production completed. - All amounts gross production, that is no writeoffs or adjustments considered, base units not considered.
List of patients who have had their first and last visits in a date range and don't have any other scheduled appts in the future. -
Patient income (payments made by patient not insurance company) in date range, sorted highest to lowest with age and remaining Treatment Planned total. - SEE REPLICATION WARNING for versions before 14.3.1.
Guarantor Names and Addresses of families with active patients that are not 'new families'. - Specifically where patients in family were seen in date range, like the last three years, where at least one family member was seen in that date range excluding a second date range, that is, families that are active but not new.
List of New Families, specifically families where not all of the member patients of those member patients who have been seen in a given period (the active patient period, like three years) were first seen in a second given period (like the last year) -
Procedures completed in date range by other than the Primary Provider - Edit date range.
Patient visits with no (or zero length) procedure notes or group clinical notes. - Edit date range at top.
Active (status) patients with no scheduled apt who have not been in for a time period with the date of their last completed apt. Also shows phone numbers and whether there is pending treatment. - From @daysIntervalStart days ago to @daysIntervalEnd days ago, change the interval currently 365 days: 1 year.
Line Item Accounting: Procedures by date range with associated payments and providers, ONLY for clinics splitting patient payments to procedures. - Edit date range at top.
Patients with appointment AND birthday in given time span, with Age, Birthdate, Name, Phone and AptDateTime. - Edit date range at top.
Claim information for specified claim level treating provider , claim level clinic and date range. - NOTES: the reason why we say 'claim level' is because each procedure and each payment (claim proc) can be associate with a provider that may or may not be different from the claim level treating provider. Same is true of clinic. Option is also included
Production and Income for a particular patient (with adjustments, insurance income by inspay date, writeoffs and balance). - By more narrow date range if desired, as is will return all income and production for patient.
Production and Income for a particular family (with adjustments, insurance income by inspay date, writeoffs and balance), also shows for every member of family and whole family. - By more narrow date range if desired, as is will return all income and production for patient.
Unsigned Consent Forms in date range. - Edit date range at top.
List all PPO insured patients with name, home and wireless phone number. -
Unscheduled list with sum of fees for appointment and amount insurance remaining before appointment. - Assumes everyone has calendar year benefits.
Claim information for specified claim level treating provider , claim level clinic and date range splitting insurance payments by procedurecode type into two groups: xrays (or whatever) and other. - Edit date range at top.
Active patients with missing teeth, also shows age, the missing teeth and the last seen date and next appointment if any. -
Referrals sources and patients list with date and patient fields for referrals made in date range. - Edit date range at top.
Patient name,three Patient Fields and appointment status associated with appointments of all statuses where the AptDateTime is in a given date range. - Edit date range at top.
Patient schedule list for next day with a patient field def (each office edit to show your field(s)), apt length, age with years and months and appointment status. -
Writeoff report showing tooth level info, by date range, with insurance paid amt listed by procedure, includes all procedures matching code for which there is a received or supplemental claim procedure. - Edit date range at top.
List all patients with insurance adjustments to benefits, could be edited to limit by date range. - Edit date range at top.
Percent of appointments by insurance carrier, add the medicaid percentages for a time period to determine EHR eligibility (this query works for current Open Dental users as well as post conversion and trial conversions: if there was no claim for a particu - Adjust dates at top as needed.
Summary claim information summed by date and clinic, with sums of claim fees, insurance estimates, insurance payments and including counts of claims and patients. - Edit date range at top.
Net Production (with PPO writeoffs removed by procedure date, other adjustments not considered) and Income by referral source for date range, KEY POINT: only includes referrals received and income collected during that period,whereas some other reports. - Edit date range at top.
Patients that have an appointment scheduled in a date range, who also have an outstanding insurance claim - Edit date range at top.
Email list (and some other fields, editable) of active patients (not subscribers) whose carrier matches a given insurance carrier string. - Change the 'Delta' to any carrier name string.
Active Patient count by age and gender. -
List of patients who have had a broken appointment but never completed any procedures. -
List of all appointments for active patients, with provider and carrier. -
Procedure Notes for date range including group notes. -
Families with credit balances, Name, Balance and Address. -
Patients listed with address and carrier who have not been seen since some date in the past, also includes patient status (normally you would engage the active patient limitation that is commented out instead of showing status) and allows carrier exclusio -
Payments made by given insurance carrier on procedures completed by given provider summed by patient. -
Patients who have had a given procedure code completed with counts for that code in a given date range. - Edit date range at top.
Summary claim information summed by date and clinic, with sums of claim fees, insurance estimates, insurance payments, outstanding claim fees, writeoffs, count of claims and count of patients. - Edit date range at top.
Received insurance claims FROM a particular carrier showing estimated vs paid amounts. - Edit date range at top.