Manually set Daily Production Goal?

[aboval]

Has anyone figured out a way to just manually set a number for the daily production goal rather than having OD calculate it based on hourly production goal by provider? I'd like to just set a goal on a daily basis - eg. tomorrow I may set a goal of $10,000 and the following day I may have a goal of $12,000, etc. If someone comes with up an acceptable solution, I'll happily mail you a crisp $100 bill for your troubles

[ishubham2101]

Open Dental's goal model is hourly and per-provider - that's provider.HourlyProdGoalAmt - so there's no field anywhere in the UI where you can type "Tuesday is $12,000". You can get what you're describing out of a User Query instead, with the goals written into the query itself.

This gives you one row per day: the goal you set, what's actually scheduled against it, and the variance.

Code: Select all

SELECT
    g.goal_date,
    DAYNAME(g.goal_date)                                  AS weekday,
    g.daily_goal,
    ROUND(COALESCE(SUM(pl.ProcFee), 0), 2)                AS scheduled_production,
    ROUND(COALESCE(SUM(pl.ProcFee), 0) - g.daily_goal, 2) AS over_under,
    COUNT(DISTINCT ap.AptNum)                             AS appointments
FROM (
    -- YOUR GOALS. One row per day. Add or remove rows freely.
              SELECT DATE('2026-09-14') AS goal_date, 10000 AS daily_goal
    UNION ALL SELECT DATE('2026-09-15'), 12000
    UNION ALL SELECT DATE('2026-09-16'), 12000
    UNION ALL SELECT DATE('2026-09-17'),  9000
    UNION ALL SELECT DATE('2026-09-18'),  8000
) g
    LEFT JOIN appointment ap
           ON  DATE(ap.AptDateTime) = g.goal_date
           AND ap.AptStatus        IN (1, 4)
    LEFT JOIN procedurelog pl
           ON  pl.AptNum     = ap.AptNum
           AND pl.ProcStatus IN (1, 2)
GROUP BY g.goal_date, g.daily_goal
ORDER BY g.goal_date;

The block in the middle is the "manually set a number" part - one row per date, any figure you like, as many days out as you want to plan.

Two things about what it counts, so the number doesn't surprise you. Scheduled production here is the fees on procedures attached to appointments that are actually on the books - AptStatus 1 (Scheduled) and 4 (ASAP). Broken and unscheduled-list appointments are left out, which I think is what you want for a goal report, but say so if not. And it counts procedure fees only, no adjustments or writeoffs, so it will read a little higher than collections.

Run it read-only first and check a single day against the Appointment Book before you trust it. If it errors on your version, post the error and your Open Dental version and I'll fix it.

If your goals actually repeat by weekday rather than changing per date, there's a DAYOFWEEK variant in the comments here, along with seven other Open Dental report queries, free: https://github.com/ishubham21/opendental-queries - each one gets run against Open Dental's published schema before it goes up.