Broken appt query broken?

For users or potential users.
Post Reply
spolevoy
Posts: 74
Joined: Wed Oct 17, 2012 4:45 am

Broken appt query broken?

Post by spolevoy » Tue Oct 21, 2014 5:41 am

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 :D

/*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
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: Broken appt query broken?

Post by allends » Tue Oct 21, 2014 7:21 am

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 :D or the appointment status is being changed.
Allen
Open Dental Software
http://www.opendental.com

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Broken appt query broken?

Post by KevinRossen » Tue Oct 21, 2014 7:51 am

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;
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Broken appt query broken?

Post by KevinRossen » Tue Oct 21, 2014 7:58 am

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;
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Broken appt query broken?

Post by tgriswold » Thu Oct 23, 2014 5:01 pm

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.
Travis Griswold
Open Dental Software
http://www.opendental.com

Post Reply