Open Dental AR Aging Query for Patient Balances

If you need to see exactly which patients have outstanding balances and how long those balances have been sitting on their accounts, this report is for you. This open dental AR aging query provides a clear, actionable list of patient balances, helping your front desk team prioritize collections efforts effectively.

The Query

Copy and paste the code below into the User Query window. This query calculates the total patient balance (excluding insurance estimates) and groups them by the standard aging buckets (0-30, 31-60, 61-90, and 90+ days).

/* Set the aging date to today */
SET @AgingDate = CURDATE();

SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone,
    /* Calculate balances based on patient portion only */
    SUM(IF(DATEDIFF(@AgingDate, bal.DatePay) <= 30, bal.Amount, 0)) AS '0-30 Days',
    SUM(IF(DATEDIFF(@AgingDate, bal.DatePay) > 30 AND DATEDIFF(@AgingDate, bal.DatePay) <= 60, bal.Amount, 0)) AS '31-60 Days',
    SUM(IF(DATEDIFF(@AgingDate, bal.DatePay) > 60 AND DATEDIFF(@AgingDate, bal.DatePay) <= 90, bal.Amount, 0)) AS '61-90 Days',
    SUM(IF(DATEDIFF(@AgingDate, bal.DatePay) > 90, bal.Amount, 0)) AS '90+ Days',
    SUM(bal.Amount) AS 'Total Balance'
FROM patient p
INNER JOIN (
    /* Subquery to get patient-specific transactions */
    SELECT PatNum, DatePay, Amount FROM paysplit
    UNION ALL
    SELECT PatNum, AdjDate, AdjAmt FROM adjustment
    /* Note: This is a simplified view of ledger activity */
) bal ON p.PatNum = bal.PatNum
WHERE p.PatStatus = 0
AND bal.Amount > 0
GROUP BY p.PatNum
HAVING `Total Balance` > 0
ORDER BY `Total Balance` 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 above into the large text box.
4. Click Submit Query.
5. The results will display in the grid below. You can click the "Export" button to save the data as a CSV file for Excel.

Understanding the Results

• LName/FName: The patient's name.
• HmPhone/WirelessPhone: Contact information to facilitate collection calls.
• 0-30, 31-60, 61-90, 90+ Days: These columns represent the patient's portion of the balance, categorized by how many days have passed since the transaction date.
• Total Balance: The sum of all outstanding patient-portion charges.

How to Customize

You can easily filter this report to focus on specific segments of your practice.

• Filter by Provider: To see balances for a specific provider, add this line before the GROUP BY clause:
  AND p.PriProv = 123 (Replace 123 with the actual ProvNum of the provider).
• Filter by Clinic: If you have multiple locations, add this line before the GROUP BY clause:
  AND p.ClinicNum = 1 (Replace 1 with your specific ClinicNum).

Variations

If you want to focus only on the most delinquent accounts, you can add a HAVING clause to filter out small balances. For example, to only show patients who owe more than $100, change the last line to:
HAVING SUM(bal.Amount) > 100

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.