Credit Balance Patients Query

If you are tired of manually searching through accounts to find patients who have overpaid or have unallocated credits, this open dental credit balance query will pull that list for you in seconds. It is a simple way to identify which accounts need attention so you can issue refunds or apply those credits to outstanding treatment plans.

The Query

Copy and paste the following code into your User Query window. This query looks for all active patients who currently have a negative balance (a credit).

/* 
   Finds active patients with a negative balance (credit) 
   Change the @MinCreditAmount if you only want to see credits over a certain dollar amount.
*/
SET @MinCreditAmount = 0.01; 

SELECT 
    PatNum, 
    LName, 
    FName, 
    EstBalance AS 'CurrentBalance'
FROM patient
WHERE PatStatus = 0 
  AND EstBalance < -@MinCreditAmount
ORDER BY EstBalance ASC;

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

• PatNum: The unique identification number for the patient in your database.
• LName: The patient's last name.
• FName: The patient's first name.
• CurrentBalance: The current balance on the account. Because this query filters for credit balances, these numbers will appear as negative values (e.g., -50.00 means the patient has a $50.00 credit).

How to Customize

You can easily tailor this report to fit your specific needs by modifying the variables at the top of the query:

• Filter by Minimum Credit Amount: If you only want to see significant credits (e.g., over $10.00) to avoid cluttering your list with pennies, change the @MinCreditAmount line:
  SET @MinCreditAmount = 10.00;
• Include Inactive Patients: If you want to see credits for all patients, not just active ones, remove the AND PatStatus = 0 line from the WHERE clause.

Variations

If you want to see the credit balance specifically for the Guarantor of the family (since that is where the financial responsibility usually lies), you can use this variation:

SELECT 
    PatNum, 
    LName, 
    FName, 
    EstBalance 
FROM patient 
WHERE PatStatus = 0 
  AND EstBalance < -0.01
  AND PatNum = Guarantor
ORDER BY EstBalance ASC;

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.