Find Procedures Not Billed to Insurance

If you are worried about revenue slipping through the cracks, this report identifies completed procedures that have not yet been attached to an insurance claim. Using this open dental procedures not billed query allows you to catch missed billing opportunities before they become uncollectible.

The Query

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

SELECT 
    p.LName, 
    p.FName, 
    pl.ProcDate, 
    pc.ProcCode, 
    pc.Descript, 
    pl.ProcFee
FROM procedurelog pl
INNER JOIN patient p ON pl.PatNum = p.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
LEFT JOIN claimproc cp ON pl.ProcNum = cp.ProcNum 
    AND cp.Status IN (0, 1, 2, 4, 5, 6, 7, 8)
WHERE pl.ProcStatus = 2 
  AND pl.ProcDate BETWEEN @FromDate AND @ToDate
  AND p.PatStatus = 0
  AND cp.ClaimProcNum IS NULL
ORDER BY pl.ProcDate;

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.
• ProcDate: The date the procedure was marked complete.
• ProcCode: The ADA procedure code used.
• Descript: The description of the procedure.
• ProcFee: The amount charged for the procedure.

If a patient appears on this list, it means they have a completed procedure in the Chart Module that is not currently linked to any insurance claim activity.

How to Customize

You can easily adjust the date range to look further back or focus on a specific month.

• Change Date Range: Modify the lines starting with SET @FromDate and SET @ToDate. Ensure you keep the format 'YYYY-MM-DD'.
• Filter by Provider: To see only one provider's missing claims, add this line before the ORDER BY clause:
  AND pl.ProvNum = 1 (Replace 1 with the actual ProvNum from your provider list).

Variations

If you want to ensure you are only looking at patients who actually have insurance coverage, you can add an extra filter to the WHERE clause:

-- Add this to the WHERE clause to only show patients with active insurance plans
AND p.PatNum IN (SELECT PatNum FROM patplan)

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.