Sixteen 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: 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.