Tracking Payment Plan Balances

If you are struggling to keep track of which patients have active payment plans and how much they still owe, this report provides the clarity you need. This open dental payment plan balance query allows you to see exactly who is on a plan and what their remaining balance is, helping you manage your accounts receivable more effectively.

The Query

You can copy and paste the following code directly into the User Query window in Open Dental.

/* Set the date range for the report */
SET @FromDate = '2026-01-01';
SET @ToDate = '2026-12-31';

SELECT 
    p.LName, 
    p.FName, 
    pp.DatePayPlan, 
    pp.TotalAmount, 
    pp.DownPayment, 
    pp.APR,
    (pp.TotalAmount - (SELECT IFNULL(SUM(ps.SplitAmt),0) 
                       FROM paysplit ps 
                       WHERE ps.PayPlanNum = pp.PayPlanNum)) AS RemainingBalance
FROM payplan pp
INNER JOIN patient p ON pp.PatNum = p.PatNum
WHERE pp.DatePayPlan BETWEEN @FromDate AND @ToDate
AND p.PatStatus = 0
ORDER BY pp.DatePayPlan;

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.
• DatePayPlan: The date the payment plan was created.
• TotalAmount: The original total amount agreed upon for the payment plan.
• DownPayment: Any initial payment collected at the start of the plan.
• APR: The annual percentage rate applied to the plan, if applicable.
• RemainingBalance: This is a calculated column showing the TotalAmount minus all payments (paysplit) currently linked to this specific payment plan.

How to Customize

You can easily adjust this report to fit your specific needs:

• Change the Date Range: Modify the SET @FromDate and SET @ToDate lines at the top of the query. For example, to see all plans created in 2025, change the dates to '2025-01-01' and '2025-12-31'.
• Filter by Provider: If you want to see plans for a specific provider, add AND pp.ProvNum = [InsertProviderNumber] to the WHERE clause. You can find the Provider Number in Setup > Appointments > Providers.
• Filter by Clinic: If you have multiple locations, add AND pp.ClinicNum = [InsertClinicNumber] to the WHERE clause.

Variations

If you only want to see patients who still have a significant balance remaining, you can add a filter to the end of the query:

-- Add this line before the ORDER BY clause
HAVING RemainingBalance > 0

This will hide any patients who have already paid off their plans in full, leaving you with a clean list of active accounts that still require collection.

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.