Can this be made into a USER QUERY?

[AussieDentistThird]

I need to do a report, on appointments; however, all of the broken appointments are included

Can someone far smarter than me, figure out a user query, so I can put a date range in and provider, and just get the appointments shown which has NOT been broken?

Alternatively, if this is not a user query issue, can it be done any other way?

Many thanks!!

[ishubham2101]

It is a user query issue, and the missing piece is one line: broken appointments are AptStatus = 5, so you just exclude that status.

Code: Select all

SELECT
    ap.AptDateTime,
    prov.Abbr                   AS provider,
    pat.PatNum,
    pat.LName,
    pat.FName,
    ap.ClinicNum,
    CASE ap.AptStatus
        WHEN 1 THEN 'Scheduled'
        WHEN 2 THEN 'Complete'
        WHEN 3 THEN 'UnschedList'
        WHEN 4 THEN 'ASAP'
        WHEN 6 THEN 'Planned'
    END                         AS apt_status,
    ap.ProcDescript,
    ap.Note
FROM appointment ap
    INNER JOIN patient  pat  ON pat.PatNum   = ap.PatNum
    LEFT  JOIN provider prov ON prov.ProvNum = ap.ProvNum
WHERE ap.AptDateTime >= '2026-01-01'
  AND ap.AptDateTime <  '2026-02-01'
  AND ap.AptStatus   <> 5
  -- one provider only? uncomment and set the number:
  -- AND ap.ProvNum    = 3
ORDER BY ap.AptDateTime;

Edit the two dates at the top of the WHERE - the second one is exclusive, so that example is the whole of January. For a single provider, uncomment the ProvNum line and put their number in; you'll find it in Lists > Providers.

The full status list is 1 Scheduled, 2 Complete, 3 UnschedList, 4 ASAP, 5 Broken, 6 Planned. If you also want the unscheduled-list ones out, use AptStatus NOT IN (3, 5) instead.

Run it read-only first. Seven other Open Dental report queries here, free: https://github.com/ishubham21/opendental-queries - each one is run against Open Dental's published schema before it goes up.