Monthly Production Report Query

If you need a quick, itemized list of all completed procedures for a specific month to verify your production numbers, this SQL query provides the data you need. It is perfect for generating monthly production reports in Open Dental when you want to see exactly which procedures contributed to your total production for the month.

The Query

Copy and paste the following code into the User Query window. You can change the dates in the SET statements to match the month you are analyzing.

SET @FromDate = '2026-01-01';
SET @ToDate = '2026-01-31';

SELECT 
    p.ProcDate AS 'Date',
    pat.LName AS 'Last Name',
    pat.FName AS 'First Name',
    pc.ProcCode AS 'Code',
    pc.AbbrDesc AS 'Description',
    p.ProcFee AS '$ Fee',
    prov.Abbr AS 'Provider'
FROM procedurelog p
INNER JOIN patient pat ON p.PatNum = pat.PatNum
INNER JOIN procedurecode pc ON p.CodeNum = pc.CodeNum
INNER JOIN provider prov ON p.ProvNum = prov.ProvNum
WHERE p.ProcStatus = 2
  AND p.ProcDate >= @FromDate
  AND p.ProcDate <= @ToDate
ORDER BY p.ProcDate, pat.LName;

How to Run This Query

1. In Open Dental, go to Reports in the Main Menu.
2. Click User Query.
3. Paste the query above into the large text box.
4. Click Submit Query.
5. The results will display in the grid below.

Understanding the Results

• Date: The date the procedure was marked complete.
• Last Name / First Name: The patient associated with the procedure.
• Code: The ADA procedure code (e.g., D0120).
• Description: The abbreviated description of the procedure code.
• $ Fee: The fee charged for that specific procedure.
• Provider: The provider abbreviation assigned to the procedure.

How to Customize

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

• Change the Date Range: Modify the two lines at the top. For example, to run it for February 2026, change the lines to:
  SET @FromDate = '2026-02-01';
SET @ToDate = '2026-02-28';
• Filter by Provider: Add a line to the WHERE clause to see only one provider's work. For example, add AND prov.Abbr = 'DOC1' before the ORDER BY line.
• Filter by Clinic: If you use multiple clinics, add AND p.ClinicNum = 1 (replace 1 with your specific ClinicNum) to the WHERE clause.

Variations

If you want to see only hygiene production, you can add a filter for the procedure code category or specific hygiene codes. For example, add this line to the WHERE clause:
AND pc.ProcCode LIKE 'D1%' (This filters for common hygiene codes starting with D1).

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.