Thirty-one free Open Dental queries

Answers: How much work have we diagnosed that nobody ever booked?

The trap: Deleted procedures carry ProcStatus 6 and are still in the table. Forget to exclude them and the number comes back inflated by work that was cancelled, sometimes badly.

Answers: How many genuinely new patients are on the books for this month?

The trap: Counting new patients by when they were seen answers a different question than counting by when they are booked. Marketing wants the second one.

Answers: Who came in, had treatment, and walked out without a next visit?

The trap: A patient can have a future appointment that is neither Scheduled nor ASAP. Check the status values you count as "on the books" or you will chase people who are already booked.

Answers: What was on the schedule between these dates, for this provider?

The trap: AptStatus 5 is Broken. It is still a row in the appointment table, and most hand-written appointment reports silently include it.

Answers: Are we on pace today, against a figure we set ourselves?

The trap: Open Dental's built-in goal is hourly per provider. Most practices think in a number per day for the whole office, which is a different calculation.

Answers: What did each dentist produce, including hygiene billed under their licence?

The trap: This is the associate-compensation number, and it is wrong unless hygiene production attributed to a provider rolls back onto the dentist it was billed under.

Answers: Principal, charged, paid and due, as of a date I choose, not today.

The trap: Aging a payment plan to an arbitrary past date is not the same as filtering by date. The charges that existed then are the ones that count.

Answers: The GUI shows a schedule my query does not return. Where is it?

The trap: On a dynamic plan those future charges are not data. Open Dental computes them for display and materialises a row when the charge falls due, which is also why the API's getExpected returns nothing.

Answers: Who is overdue for hygiene and has no future appointment?

The trap: Recall rows carry a disabled flag and a sentinel date of 0001-01-01. Both have to go, or the list fills with people who were never on recall.

Answers: Where is our money stuck, and how old is it?

The trap: Aged from the claim procedure date rather than the send date, because that is the date the carrier works from.

Answers: Who owes us what, in 0-30 / 31-60 / 61-90 / 90+?

The trap: The Bal_0_30 columns on the patient table are not live. Open Dental writes them during the aging run. If aging last ran on the 1st, that is the date your report is really as of, whatever today is.

Answers: What did we actually get paid, and who earned it?

The trap: Read the splits, not the payments. One payment can cover several patients and providers, and unearned prepayment will inflate a day unless you keep it in its own column.

Answers: Who keeps breaking appointments, and what did it cost us in chair time?

The trap: Chair time lives in the Pattern string, one character per five minutes, not in a duration column.

Answers: Of what each provider diagnosed, how much got done?

The trap: Measure it in dollars. One crown is not one sealant, so a count-based acceptance rate flatters whoever diagnoses a lot of small work.

Answers: Who fell off entirely?

The trap: This is not the recall report. The recall report can only find people who had a recall row to go overdue in the first place; plenty of lapsed patients never had one.

Answers: How many genuinely new patients did we see, without the count quietly dropping some?

The trap: Open Dental's own library query #1203 has this wrong. With GROUP BY PatNum, a bare ProcDate column is not the group's minimum, it is whichever row was stored first, so a patient whose rows landed out of date order fails the test and vanishes. Reported on the forum in 2024, never fixed. Reproduced in the file header.

Answers: Who on this insurance has planned work in these codes, and when can we reach them?

The trap: Priority is a foreign key into the definition table, so sorting by it sorts by creation order rather than the sequence the treatment plan shows. GROUP_CONCAT also truncates at 1024 characters by default, which silently cuts the end off a large treatment plan with no error.

Answers: Production, plan writeoffs, adjustments and payments for everyone on the plan, over a date range.

The trap: Joining procedures, adjustments and payments in one FROM clause multiplies them together. A patient with four procedures, two adjustments and three payments becomes twenty-four rows and every total is inflated. Each total here is its own subquery grouped by patient.

Answers: Which patients are on an in-house plan, and under which fee schedule?

The trap: Open Dental moved discount plans off the patient record and into their own subscription table with effective and termination dates. Both still exist. Read only one and you miss every patient on the other side of the change.

Answers: How many of our patients have been with us since before X?

The trap: DateFirstVisit is an editable field, and on a converted database it is often blank or the conversion date for the entire chart. Check it against the earliest completed procedure before trusting it.

Answers: Which codes automatically assign to a provider, and to whom?

The trap: Nothing in the interface lists these overrides. One code still pointed at a provider who left keeps assigning their production, and the only symptom is a number that looks slightly off.

Answers: We produced well in March, so where is the money?

The trap: Production and collections reported side by side for the same month compares March's work against March's money, most of which is for earlier work. paysplit carries the procedure date as well as the payment date, which is what lets the two line up.

Answers: How does this PPO schedule compare to ours, before we sign?

The trap: A code with no fee row is left out rather than shown as zero. Open Dental treats no fee set and a fee of zero as different things, and flattening them changes the comparison.

Answers: Who did we actually tell about the fee increase?

The trap: The Commlog window searches one patient at a time. CommType is a foreign key into the definition table, so without the join the type is an integer.

Answers: What does the time clock actually say each person worked?

The trap: TimeDisplayed is the adjusted punch the timecard shows and the one payroll runs from; TimeEntered is the raw punch. Use the raw one only if you are auditing rather than paying. Rows still clocked in have no clock-out and must be excluded, not counted as zero.

Answers: A carrier changed its fee schedule or went out of network. Who do we have to call?

The trap: Open Dental stores "never terminated" as the sentinel date 0001-01-01, not as NULL. A plain date comparison silently drops every patient whose coverage is still active.

Answers: How much work do we close in the chair rather than reschedule?

The trap: Different from case acceptance. This counts only what never had to be re-sold, so it needs DateTP and ProcDate compared, not a status filter.

Answers: We hit the production goal, so why is the bank balance short?

The trap: Production counts the full fee. The PPO writeoff is the gap between that and what the contract allows, and it appears in no production report at all.

Answers: How much came in as cash, card, cheque or insurance?

The trap: payment.PayType is a foreign key into the definition table, not a fixed enum, so the types are whatever the practice set up. Without the join you get a column of integers.

Answers: Who changed what, across a date range rather than one chart at a time?

The trap: securitylog is enormous in an established practice. Keep the window narrow. PermType is a version-specific numeric code, so read LogText, which is the line Open Dental wrote itself.

Answers: Who still has annual maximum left to spend before it resets?

The trap: Open Dental stores the annual maximum as a benefit row, and I could not verify from the public documentation which numeric BenefitType means Limitations, so this query does not hard-code it. It derives the maximum from your own data and prints the BenefitType it used, with a one-line query in the header to pin the value for your database.