Patient Balance Report Query

If you need to identify which patients have an outstanding balance over a certain dollar amount to prioritize your collections efforts, this query provides that list instantly. It filters for active patients and calculates their current balance based on the information in their account.

The Query

Copy and paste the following code into your User Query window. You can change the 500 in the last line to whatever dollar amount you wish to filter by.

SET @MinBalance = 500;

SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.Email, 
    p.EstBalance AS 'CurrentBalance'
FROM patient p
WHERE p.PatStatus = 0
AND p.EstBalance >= @MinBalance
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.

Understanding the Results

• LName / FName: The patient's last and first name.
• HmPhone / WirelessPhone: Contact numbers to reach the patient for billing inquiries.
• Email: The email address on file, useful if you send digital statements.
• CurrentBalance: This reflects the EstBalance field in the patient's record, which is the total amount currently owed by the patient after insurance estimates have been applied.

How to Customize

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

• Change the Balance Threshold: Change the number 500 in the first line (SET @MinBalance = 500;) to any amount you choose (e.g., 1000 for patients owing over $1,000).
• Filter by Provider: If you only want to see 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 you are looking for. You can find this in Setup > Definitions > Providers).
• Filter by Clinic: If you have multiple locations, you can filter by clinic by adding this line:
  AND p.ClinicNum = 1
  (Replace 1 with the ClinicNum for your specific location).

Variations

If you want to focus only on patients who have not had a payment in a long time, you can cross-reference this with the payment table. However, for most offices, the primary query above is sufficient for daily collections tasks. If you find that the EstBalance includes insurance estimates you disagree with, remember that this query relies on the accuracy of your insurance claim processing in the Account Module.

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.