Open Dental query examples: 11 safe, read-only SQL queries

You want the list of patients with treatment planned and nothing booked, and the built-in report does not group it the way you need. Open Dental lets you ask the database directly. That is powerful and it is also how people slow down a server at 10 a.m. This page gives you queries that are narrow, read-only and explained.

How does User Query work and what does it permit?

Open Dental’s manual describes it this way (checked 13 Oct 2026, User Query and Using SQL to Write Reports):

• Reports, then User Query opens it. The User Query permission runs released queries and edits their variables. User Query Admin is needed to write, edit, run, save and delete queries.
• Non-select commands need a separate Command Query permission. The manual does not say standard User Query is limited to SELECT, so enforce that yourself.
• The manual’s own guidance for reports: use only SELECT, never INSERT, UPDATE, DELETE, DROP or TRUNCATE. Clauses go in order: SELECT, FROM, WHERE, ORDER BY, LIMIT. Dates are YYYY-MM-DD.
• A column named with a leading $ is formatted as money and totalled at the bottom.
• Results can be exported as .txt or .csv. Stop Execution cancels a long query.
• Open Dental also has a Query Examples page with more than a thousand customer queries. Check its notes, because not every query suits every version.

Table and column names below come from Open Dental’s public schema and the tables Calibic reads. Status codes are numbers. Each query that relies on one carries a verify comment.

What should you check before running any query?

1. SELECT only. Read the whole text. If you see INSERT, UPDATE, DELETE, DROP, TRUNCATE or ALTER, stop.
2. Limit the rows. Put LIMIT 500 on while testing and filter by date.
3. Pick the hour. Not during peak hours on the live server. Early morning or after close.
4. Use a copy. Restore last night's backup somewhere else and run it there first.
5. Leave clinical text alone. Do not select `procnote` text, `securitylog.LogText`, or any column holding notes or Social Security numbers.

These queries have not been run against every Open Dental version. Test each one on a non-production copy first.

How do you list unscheduled treatment plans?

Planned procedures not attached to an appointment, by patient, biggest first.

SELECT p.PatNum, p.LName, p.FName,
       COUNT(*) AS Procs,
       SUM(pl.ProcFee) AS '$ Planned'
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum = pl.PatNum
WHERE pl.ProcStatus = 1      -- treatment planned
  AND pl.AptNum = 0          -- not on an appointment
  AND p.PatStatus = 0        -- active patient
GROUP BY p.PatNum, p.LName, p.FName
ORDER BY SUM(pl.ProcFee) DESC
LIMIT 100;

Procs is how many planned procedures the patient has. $ Planned is their fee total. Caution: a plan with several visits shows as one row, and ProcFee ignores PPO write-offs, so the figure runs higher than what you will collect. See Find unscheduled treatment in Open Dental for the built-in options.

Which planned procedures are oldest?

SELECT p.LName, p.FName, pc.ProcCode, pl.ProcFee, pl.DateTP
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum = pl.PatNum
INNER JOIN procedurecode pc ON pc.CodeNum = pl.CodeNum
WHERE pl.ProcStatus = 1 AND pl.AptNum = 0 AND p.PatStatus = 0
  AND pl.DateTP < CURDATE() - INTERVAL 90 DAY
ORDER BY pl.DateTP
LIMIT 100;

Shows planned work more than 90 days old. Caution: DateTP can be blank on old rows, so they may drop out.

How do you see procedures completed by provider for a date range?

SET @FromDate = '2026-09-01';
SET @ToDate   = '2026-09-30';
SELECT pr.Abbr AS Provider,
       COUNT(*) AS Procs,
       SUM(pl.ProcFee) AS '$ Gross Production'
FROM procedurelog pl
INNER JOIN provider pr ON pr.ProvNum = pl.ProvNum
WHERE pl.ProcStatus = 2      -- complete
  AND pl.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY pr.Abbr
ORDER BY SUM(pl.ProcFee) DESC;

Gross production by provider. Caution: this is the gross fee, before write-offs and adjustments, so it will not equal the net production in Open Dental’s own report. Compare against it before trusting it.

How do you see production by day?

SELECT pl.ProcDate, COUNT(*) AS Procs, SUM(pl.ProcFee) AS '$ Gross'
FROM procedurelog pl
WHERE pl.ProcStatus = 2
  AND pl.ProcDate >= CURDATE() - INTERVAL 14 DAY
GROUP BY pl.ProcDate
ORDER BY pl.ProcDate DESC
LIMIT 14;

Two weeks of gross production. Caution: deleted procedures keep a different status, which is why the status filter matters. Do not drop it.

Which patients are overdue for recall?

SELECT p.PatNum, p.LName, p.FName, rt.Description AS RecallType, r.DateDue
FROM recall r
INNER JOIN patient p ON p.PatNum = r.PatNum
INNER JOIN recalltype rt ON rt.RecallTypeNum = r.RecallTypeNum
WHERE r.IsDisabled = 0
  AND r.DateScheduled = '0001-01-01'
  AND r.DateDue < CURDATE() - INTERVAL 30 DAY
  AND p.PatStatus = 0
ORDER BY r.DateDue
LIMIT 200;

Active patients whose recall was due more than 30 days ago with nothing booked. Caution: DateDue can be manually overridden, so check a few against the Recall List. Pairs with the recall report guide.

How do you total payments by type?

SELECT d.ItemName AS PayType, COUNT(*) AS Payments, SUM(pay.PayAmt) AS '$ Total'
FROM payment pay
LEFT JOIN definition d ON d.DefNum = pay.PayType
WHERE pay.PayDate BETWEEN '2026-10-01' AND '2026-10-31'
GROUP BY d.ItemName
ORDER BY SUM(pay.PayAmt) DESC;

Cash, check, card and the rest. Caution: insurance payments live in claimpayment, so this is patient payments only. Compare against your day sheet, not your bank deposit.

How do you see adjustments by user?

SELECT u.UserName, COUNT(*) AS Adjustments, SUM(a.AdjAmt) AS '$ Net'
FROM adjustment a
LEFT JOIN userod u ON u.UserNum = a.SecUserNumEntry
WHERE a.AdjDate BETWEEN '2026-10-01' AND '2026-10-31'
GROUP BY u.UserName
ORDER BY SUM(a.AdjAmt);

Who entered how many adjustments and the net amount. A negative total means discounts and write-offs. Caution: a user with many small adjustments is not a finding. A user with a few large ones might be. Read before concluding anything.

Which claims have been outstanding over 60 days?

SELECT p.LName, p.FName, c.DateSent, c.ClaimFee,
       DATEDIFF(CURDATE(), c.DateSent) AS DaysOut
FROM claim c
INNER JOIN patient p ON p.PatNum = c.PatNum
WHERE c.ClaimStatus = 'S'          -- sent
  AND c.ClaimType <> 'PreAuth'
  AND c.DateSent < CURDATE() - INTERVAL 60 DAY
ORDER BY c.DateSent
LIMIT 200;

Sent claims with no payment received after 60 days. Caution: partial payments may change the status, and a claim with a received status can still have a balance. The built-in Outstanding Insurance Claims report is the check.

Which appointments tomorrow are unconfirmed?

SELECT a.AptDateTime, p.LName, p.FName, a.Confirmed
FROM appointment a
INNER JOIN patient p ON p.PatNum = a.PatNum
WHERE DATE(a.AptDateTime) = CURDATE() + INTERVAL 1 DAY
  AND a.AptStatus = 1
ORDER BY a.AptDateTime;

Tomorrow’s scheduled appointments with the confirmation value. Look up the number for your “Unconfirmed” label in the definitions table. Caution: the value differs between offices.

What can you see in the security log without reading text?

SELECT u.UserName, sl.PermType, COUNT(*) AS Entries
FROM securitylog sl
LEFT JOIN userod u ON u.UserNum = sl.UserNum
WHERE sl.LogDateTime >= CURDATE() - INTERVAL 7 DAY
GROUP BY u.UserName, sl.PermType
ORDER BY Entries DESC
LIMIT 100;

Counts of actions per user and permission over a week. Caution: LogText is deliberately not selected, since it can carry patient detail. The table is large, so the date filter and LIMIT are not optional. Background: the Open Dental security log.

How do you keep this safe?

Do	Do not
SELECT with a date filter and LIMIT	Run INSERT, UPDATE, DELETE, DROP or TRUNCATE
Run after close or on a restored copy	Run heavy joins at 10 a.m. on the live server
Select ids, dates, counts and amounts	Select note text, LogText, SSNs or addresses
Give User Query Admin to as few people as you can	Share one login for query work
Save tested queries to Query Favorites	Paste a query from a forum without reading it

Export files hold patient names. Treat a CSV like a chart: store it somewhere access-controlled and delete it when done.

What does Calibic do differently?

Calibic reads Open Dental with a database account that holds SELECT grants only, and opens its session read-only, so the server refuses writes (how that works). It never selects clinical note text or the text of securitylog. That is a design choice, not a certification. If you are an IT person writing your own reporting, copy the pattern: a dedicated read-only account, not a User Query Admin login.

What to do next

Copy one query, run it on a restored copy, compare the total with the matching built-in report, then use it. Sources: Open Dental’s User Query and Using SQL to Write Reports manual pages, checked 13 Oct 2026.

If you would rather ask a question in plain English than write SQL, Calibic’s Ask answers from the same records and shows the source rows.