Tracking Uncollected Patient Balances

If you are struggling to identify which active patients still owe money after insurance has processed, this report provides the clarity you need. This open dental uncollected patient portion query isolates active patients with a positive balance, helping your front desk team prioritize their collection efforts.

The Query

Copy and paste the following code into your User Query window. This query filters for active patients (PatStatus = 0) who have an estimated balance greater than zero.

SET @FromDate = '2025-01-01';

SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.EstBalance AS 'PatientBalance'
FROM patient p
WHERE p.PatStatus = 0
AND p.EstBalance > 0
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, showing you the list of patients and their corresponding balances.

Understanding the Results

• LName / FName: The patient's last and first name.
• HmPhone / WirelessPhone: Contact numbers to help your team reach out for collection.
• PatientBalance: This represents the EstBalance column from the patient table, which is the amount Open Dental calculates as the patient's responsibility after insurance estimates are applied.

How to Customize

You can easily tailor this report to fit your office's specific needs:

• Filter by Provider: If you want to see balances for 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 your provider).
• Filter by Clinic: If you run a multi-clinic practice, add this line to filter by a specific location:
  AND p.ClinicNum = 1 (Replace '1' with your specific ClinicNum).
• Minimum Balance: If you only want to see patients who owe more than $100, change the WHERE clause:
  AND p.EstBalance > 100

Variations

If you want to focus on patients who have not had a payment posted in a long time, you can cross-reference the payment table. However, for a quick snapshot of total outstanding patient portions, the query above is the most efficient starting point. You can also export these results to Excel by clicking the Export button in the User Query window to create a call list for your administrative staff.

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.