Open Dental AR Aging Query

Need a custom report for open dental ar aging? Use this simple SQL query to pull a list of active patients with outstanding balances in your practice.

If you need a clear, actionable list of active patients who currently have an outstanding balance, this query provides a clean breakdown of who owes money to your practice. It is designed for office managers who need to quickly identify accounts for follow-up without navigating through multiple standard reports.

The Query

Copy and paste the following code into your User Query window.

/* 999 Active Patients with Outstanding Balances */
SET @AsOfDate = '2026-03-27'; /* Enter the date you want to run this report for */

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 > 0
ORDER BY p.EstBalance DESC;

How to Run This Query

In Open Dental, go to Reports in the Main Menu.
Click User Query.
Paste the query above into the large text box.
Click Submit Query.
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 front desk staff reach out for collections.
Email: The patient's email address for sending statements or reminders.
Balance: The current estimated balance for that patient.

How to Customize

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

Change the Date: While this query pulls the current EstBalance, if you are running this for a specific historical date, ensure your SET statement reflects the correct timeframe.
Filter by Provider: If you only 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 the provider).
Filter by Clinic: If you have multiple locations, add this line to filter by a specific clinic:
AND p.ClinicNum = 1 (Replace 1 with your specific ClinicNum).

Variations

If you want to focus only on patients with significant balances, you can add a minimum threshold to the query. Change the AND p.EstBalance > 0 line to:
AND p.EstBalance > 100
This will exclude any patients who owe less than $100, allowing your team to focus on larger outstanding accounts first.

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.