Broken appt query broken?

[spolevoy]

We are trying to come up with a query that lists all broken appts for the past week.
The sample query doesn't return any results for past week. When run for super long time, like 700 days, it returns a few results - but not many, 3 broken appts YTD.
We, unfortunately, have had a wee bit more broken appts than 3. If we had only 3, we wouldn't have bothered trying to come up with a query

/*256 Appointments that are broken for a date range, with note*/
SET @FROMDate= DATE_SUB(CurDate(), INTERVAL 7 Day) ,@ToDate= DATE(CurDate());
SELECT p.LName,p.FName, p.PatNum AS 'Pat#', a.AptDateTime, a.Note
FROM appointment a
INNER JOIN patient p ON a.PatNum=p.PatNum
WHERE DATE(a.AptDateTime) BETWEEN @FromDate AND @ToDate
AND a.AptStatus=5
ORDER BY a.AptDateTime;

[allends]

That query appears like it should work and from the tests that I just ran it should work fine.

Broken appointments could have had their status changed somehow, based on the query's date range of 700 days and returning 3 appointments I would wager that is the case.

You could run this query

Code: Select all

SELECT Count(*) FROM appointment INNER JOIN patient ON patient.PatNum=appointment.PatNum WHERE AptStatus=5;

That will return the total number of broken appointments you have had. If it is really low I would guess your patients are very punctual or the appointment status is being changed.

[KevinRossen]

Here you go:

Code: Select all

SET @FromDate= DATE_SUB(CURDATE(), INTERVAL 7 DAY) ,@ToDate=  DATE(CURDATE());
SELECT CONCAT(p.LName,', ',p.FName) AS Patient, a.AdjDate, d.ItemName, a.AdjNote
FROM adjustment a
LEFT JOIN definition d ON a.AdjType=d.DefNum
LEFT JOIN patient p ON a.PatNum=p.PatNum
WHERE ItemName LIKE 'BROKEN Appt%' AND DATE(a.AdjDate) BETWEEN @FromDate AND @ToDate
ORDER BY AdjDate DESC;

[KevinRossen]

The problem with the original example is that it only looks at the appointment status, which will disappear if the appointment is either deleted or moved to the unscheduled list. In our office, we don't leave broken appointments on the schedule. At the end of the day, we either make them unscheduled (for restorative appointments) or delete them (for recall). In both cases, the AptStatus=5 would not find those broken appts.

Here's another version that will look at the previous week's Monday through the current date and give a total count at the bottom:

Code: Select all

SET @FromDate=SUBDATE(ADDDATE(CURDATE(), INTERVAL 1-DAYOFWEEK(CURDATE()) DAY), INTERVAL 6 DAY), @ToDate=CURDATE();
SELECT CONCAT(p.LName,', ',p.FName) AS Patient, a.AdjDate AS DATE, d.ItemName, a.AdjNote
FROM adjustment a
LEFT JOIN definition d ON a.AdjType=d.DefNum
LEFT JOIN patient p ON a.PatNum=p.PatNum
WHERE ItemName LIKE 'BROKEN Appt%' AND DATE(a.AdjDate) BETWEEN @FromDate AND @ToDate
UNION
SELECT '----------','','',''
UNION
SELECT CONCAT('Total: ',COUNT(*)),'','',''
FROM adjustment a
LEFT JOIN definition d ON a.AdjType=d.DefNum
WHERE ItemName LIKE 'BROKEN Appt%' AND DATE(a.AdjDate) BETWEEN @FromDate AND @ToDate;

[tgriswold]

KevinRossen is correct, most offices take their broken appointments off the schedule. His query will look for broken appointment adjustments instead of appointments with the broken status. Another way would be to look in the audit trail for appointmentedit entries with the word broken in the log text, but this gives the possibility for false positives as there might be another reason for the word "broken" to be in the log text, like if it is part of a procedure abbreviation for example.