Patient Portion Uncollected Query

If you are struggling to identify which active patients have outstanding balances that are not covered by insurance, this open dental uncollected patient portion query will help you clean up your accounts receivable. This report allows you to quickly generate a list of patients who owe money, making it easier for your front desk team to prioritize collection calls.

The Query

Copy and paste the following code into your User Query window. This query calculates the patient's total charges, subtracts insurance payments and estimates, and subtracts payments already made by the patient.

SET @FromDate = '2025-01-01';
SET @ToDate = '2026-04-09';

SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.Email,
    ROUND(p.EstBalance, 2) AS 'TotalBalance',
    ROUND(SUM(cp.InsPayEst), 2) AS 'InsEstimate',
    ROUND(p.EstBalance - SUM(cp.InsPayEst), 2) AS 'PatientPortion'
FROM patient p
LEFT JOIN claimproc cp ON p.PatNum = cp.PatNum AND cp.Status IN (0, 6)
WHERE p.PatStatus = 0
AND p.EstBalance > 0
GROUP BY p.PatNum
HAVING PatientPortion > 0
ORDER BY PatientPortion 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 list as a CSV file for Excel.

Understanding the Results

• LName/FName: The patient's name.
• HmPhone/WirelessPhone/Email: Contact information to help your team reach out for collection.
• TotalBalance: The total balance currently showing on the patient's account in the Account Module.
• InsEstimate: The total amount Open Dental estimates insurance will pay for completed or planned procedures.
• PatientPortion: The calculated amount remaining that the patient is responsible for paying.

How to Customize

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

• Change Date Range: Modify the SET @FromDate and SET @ToDate lines at the top of the query to look at specific timeframes.
• Filter by Provider: To see balances for a specific provider, add AND p.PriProv = [ProviderNumber] to the WHERE clause. You can find the Provider Number in Setup > Definitions > Providers.
• Filter by Clinic: If you have multiple locations, add AND p.ClinicNum = [ClinicNumber] to the WHERE clause.

Variations

If you want to focus only on patients with very large balances, you can add a filter to the end of the query. For example, add AND (p.EstBalance - SUM(cp.InsPayEst)) > 500 before the GROUP BY line to only show patients who owe more than $500.

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.