Scheduled Production Query for Tomorrow

If you need to know exactly how much production is on the books for tomorrow to prepare your team for the day ahead, this report provides the answer instantly. This open dental scheduled production query calculates the total fees for all procedures attached to appointments scheduled for the next business day.

The Query

Copy and paste the following code into your User Query window. This query looks specifically for appointments with a status of "Scheduled" (AptStatus = 1) for the date defined in the variable.

SET @TargetDate = CURDATE() + INTERVAL 1 DAY;

SELECT 
    a.AptDateTime AS 'Appt Time',
    p.LName AS 'Last Name',
    p.FName AS 'First Name',
    SUM(pl.ProcFee) AS 'Total Production'
FROM appointment a
INNER JOIN patient p ON a.PatNum = p.PatNum
INNER JOIN procedurelog pl ON a.AptNum = pl.AptNum
WHERE a.AptStatus = 1
AND DATE(a.AptDateTime) = @TargetDate
AND pl.ProcStatus != 6 -- Exclude deleted procedures
GROUP BY a.AptNum
ORDER BY a.AptDateTime;

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, showing the appointment time, patient name, and the total production value for that specific appointment.

Understanding the Results

• Appt Time: The exact date and time the appointment is scheduled in the Appointments Module.
• Last Name / First Name: The patient associated with the appointment.
• Total Production: The sum of the fees for all procedures attached to that specific appointment. This is based on the fee schedule assigned to the patient or procedure.

How to Customize

You can easily adjust this query to look at different dates or filter by specific criteria.

• Change the Date: To look at a specific date rather than "tomorrow," change the first line of the query. For example, to look at a specific date, use:
  SET @TargetDate = '2026-04-15';
• Filter by Provider: If you only want to see production for a specific provider, add a line to the WHERE clause:
  AND a.ProvNum = 1 (Replace '1' with the actual ProvNum found in Setup > Providers).

Variations

If you want to see a breakdown by provider for the entire day, you can modify the SELECT statement to group by the provider's name instead of the appointment.

Another useful variation is to filter for only hygiene appointments. You can add this line to the WHERE clause:
AND a.IsHygiene = 1

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.