Patient Balance Report Query

If you are struggling to identify which accounts need immediate attention, this open dental patient balance query allows you to pull a list of active patients with an outstanding balance over a specific dollar amount. This report helps your front desk staff prioritize billing calls and manage your accounts receivable more effectively.

The Query

Copy and paste the following code into your User Query window. You can change the 500.00 value in the first line to whatever threshold you prefer.

SET @BalanceThreshold = 500.00;

SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.Email, 
    p.EstBalance AS 'Balance'
FROM patient p
WHERE p.PatStatus = 0
AND p.EstBalance >= @BalanceThreshold
ORDER BY p.EstBalance DESC;

How to Run This Query

1. In Open Dental, go to Reports in the Main Menu.
2. Click User Query.
3. Paste the query provided above into the large text box.
4. Click Submit Query.
5. The results will display in the grid below.

Understanding the Results

• LName / FName: The patient's last and first name.
• HmPhone / WirelessPhone: Contact numbers to help your staff reach out for payment.
• Email: The email address on file for sending statements or digital communication.
• Balance: The current estimated balance for that patient (this matches the balance seen in the Account Module).

How to Customize

You can easily modify this query to fit your specific needs:

• Change the Balance Threshold: Change the 500.00 in the first line (SET @BalanceThreshold = 500.00;) to any amount you choose, such as 100.00 or 1000.00.
• Filter by Provider: If you only want to see patients assigned to a specific provider, add this line before the ORDER BY clause:
  AND p.PriProv = 1 (Replace 1 with the actual ProvNum of the provider).
• Filter by Clinic: If you have multiple locations, add this line before the ORDER BY clause:
  AND p.ClinicNum = 1 (Replace 1 with your specific ClinicNum).

Variations

If you want to focus only on patients who have not had a completed procedure in the last year, you can add a subquery to exclude them. Alternatively, if you want to see only patients who have insurance, you can add a join to the patplan table to filter out patients without active coverage.

Skip the Query — Use DentalCanvas Instead

Don't want to write SQL? DentalCanvas connects to your Open Dental database and shows you this data automatically in a visual dashboard — no queries required.

This article is provided by opendentalsupport.com, an independent community resource. We are not affiliated with Open Dental Software, Inc.