Open Dental AR Aging Query

If you are struggling to get a clear picture of which patients owe money and how long those balances have been outstanding, this report is for you. While the standard aging reports in the software are excellent, running a custom open dental AR aging query allows you to pull specific data points into a spreadsheet for deeper analysis or targeted collections.

The Query

This query calculates the total balance for active patients and categorizes them based on how long the balance has been sitting on the account.

SET @AsOfDate = '2026-03-31';

SELECT 
    p.PatNum, 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone,
    p.EstBalance AS TotalBalance,
    -- This is a simplified calculation for demonstration
    -- In a production environment, use the built-in aging reports for complex ledger math
    (SELECT SUM(SplitAmt) FROM paysplit WHERE PatNum = p.PatNum AND DatePay > DATE_SUB(@AsOfDate, INTERVAL 30 DAY)) AS PaidLast30Days
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. You can click Export to save the data as a CSV file for Excel.

Understanding the Results

• PatNum: The unique identification number for the patient in your database.
• LName/FName: The patient's last and first name.
• HmPhone/WirelessPhone: Contact numbers to help your front desk staff reach out regarding the balance.
• TotalBalance: The current estimated balance on the patient's account (this matches what you see in the Account Module).
• PaidLast30Days: A helpful column showing if the patient has made any payments recently, which helps determine if they are actively working on their balance.

How to Customize

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

• Change the Date: To change the "As Of" date, modify the first line: SET @AsOfDate = '2026-03-31'; to your desired date.
• 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 Minimum Balance: To hide small balances (e.g., under $50), add this line: AND p.EstBalance > 50.

Variations

If you want to focus only on patients who have insurance, you can add a join to the patplan table to filter the results:

SELECT p.LName, p.FName, p.EstBalance
FROM patient p
INNER JOIN patplan pp ON p.PatNum = pp.PatNum
WHERE p.PatStatus = 0 AND p.EstBalance > 0
GROUP BY p.PatNum;

This variation ensures you are only looking at patients who have at least one active insurance plan attached to their file.

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.