Open Dental Payment Plan Balance Query

If you need to see exactly how much money is still owed on active patient payment plans, this report provides a clear list of balances for your entire practice. This open dental payment plan balance query helps office managers identify which accounts need follow-up without manually checking every chart.

The Query

Copy and paste the following code into your User Query window. This query calculates the total amount agreed upon for the payment plan and subtracts the payments already made to show the remaining balance.

/* Set the status to only include active patients */
SET @PatStatus = 0;

SELECT 
    p.LName, 
    p.FName, 
    pp.TotalAmount AS 'Total Plan Amount', 
    SUM(ps.SplitAmt) AS 'Total Paid', 
    (pp.TotalAmount - SUM(ps.SplitAmt)) AS 'Remaining Balance'
FROM payplan pp
INNER JOIN patient p ON pp.PatNum = p.PatNum
LEFT JOIN paysplit ps ON pp.PayPlanNum = ps.PayPlanNum
WHERE p.PatStatus = @PatStatus
GROUP BY pp.PayPlanNum
HAVING (pp.TotalAmount - SUM(ps.SplitAmt)) > 0
ORDER BY p.LName, p.FName;

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 the text box.

Understanding the Results

• LName / FName: The patient's last and first name.
• Total Plan Amount: The original total amount set up for the payment plan.
• Total Paid: The sum of all payments linked specifically to this payment plan.
• Remaining Balance: The calculated difference between the total plan amount and the payments received.

How to Customize

You can easily filter this list to focus on specific needs.

• Filter by Provider: If you want to see payment plans for a specific provider, add a line to the WHERE clause. You will need to know the ProvNum from your Provider setup.
  • Add this line: AND pp.ProvNum = 1 (Replace 1 with your specific provider number).
• Filter by Clinic: If your practice uses clinics, you can filter by ClinicNum.
  • Add this line: AND pp.ClinicNum = 1 (Replace 1 with your specific clinic number).

Variations

If you only want to see patients with a very high remaining balance, you can add a filter to the HAVING clause to exclude smaller balances.

Change the HAVING line to:
HAVING (pp.TotalAmount - SUM(ps.SplitAmt)) > 500

This will only show patients who still owe more than $500 on their payment plan.

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.