Need Query Help - Trying to bypass Planned Appointments

[spolevoy]

Like many other users, I feel PlannedAppointments module is extremely unfriendly, time-consuming, very difficult to use, and in general useless.

So I am trying to bypass it, using a query to return patients with unscheduled treatment that were tx planned in a time range.
Unfortunately, I am running into the same problem as in TxFinder - if a patient has a future recall appointment, they do not show up in the list for unscheduled tx.

I thought the query below would exclude appts for recall procedures, but we have the same problem.
Shouldn't the highlighted line exclude recall appts?

/*164 Returns all treatment planned procedures for active patients without a scheduled OR planned apt, who were last seen in a given date range
with phone numbers, useful for those transitioning to planned appointments
(this differs FROM #56 largely in that it is date limited and lists out the treatment)*/
DROP TABLE IF EXISTS tmp1;
DROP TABLE IF EXISTS tmp2;
SET @FROMDate= DATE_SUB(CurDate(), INTERVAL 7 Day) ,@ToDate= DATE(CurDate()) ;
CREATE TABLE tmp1 AS SELECT patient.PatNum, CONCAT(LName, ', ',FName, ' ', MiddleI) AS PatName, HmPhone, WkPhone,
WirelessPhone AS Wireless, ProcFee, pc.ProcCode FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum AND ProcStatus=1
LEFT JOIN appointment ap ON patient.PatNum=ap.PatNum AND (ap.AptStatus=1 OR ap.AptStatus=6 )
WHERE ap.AptNum IS NULL AND patient.PatStatus=0 AND pc.ProcCode NOT IN('D0120', 'D0150','D1120', 'D1110','D1203','D1204', 'D1206', 'D0272','D0274','D0210', 'D0220', 'D0230', 'D4910', 'D4910.3') AND ProcFee NOT IN ('0');
CREATE TABLE tmp2 AS SELECT p.PatNum, DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') AS 'LastVisit', MAX(ProcDate) AS 'DateLast'
FROM patient p
INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum
WHERE pl.ProcStatus=2 AND p.PatStatus=0
GROUP BY pl.PatNum;
SELECT PatName,Left(HmPhone,15) AS HmPhone,Left(WkPhone,21) As WKPhone,Left(Wireless,15) AS Wireless, ProcFee AS '$Fee', LastVisit
FROM tmp1 INNER JOIN tmp2 ON tmp1.PatNum=tmp2.PatNum
WHERE DateLast BETWEEN @FROMDate AND @ToDate
ORDER BY PatName;
DROP TABLE IF EXISTS tmp1;

[KevinRossen]

Here's a report I made that sounds similar to what you're looking to do. It pulls unscheduled treatment based on three time ranges: 2 weeks ago, 8 weeks ago, and 18 weeks ago. It's setup relative to the day it's run and you don't have to change the dates. I run it every Monday morning. It only finds procedures with a fee over $100, which takes out all our recall procedures. If your fees are higher you can just adjust the three entries of ProcFee >= 100.

Code: Select all

DROP TABLE IF EXISTS t1,t2,t3;
CREATE TABLE t1(Name VARCHAR(30) NOT NULL,Age VARCHAR(3),Wireless VARCHAR(13) NOT NULL,Home VARCHAR(13) NOT NULL DEFAULT '',DateTP date,Procs VARCHAR(35) NOT NULL,Fees double NOT NULL DEFAULT 0,NextAppt date);
INSERT INTO t1(Name,Age,Wireless,Home,DateTP,Procs,Fees,NextAppt)
SELECT CONCAT(LName,', ',IF(Preferred='',FName,CONCAT(FName,' (',Preferred,')'))) AS 'Name', DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS 'Age', pt.WirelessPhone AS 'Wireless', pt.HmPhone AS 'Home', ProcDate AS 'DateTP', GROUP_CONCAT(pc.AbbrDesc) AS 'Procs', SUM(ProcFee) AS 'Fees', DATE(A.DateNextSchedApt) AS 'NextAppt'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN (SELECT appointment.PatNum, MIN(DATE(AptDateTime)) AS DateNextSchedApt FROM appointment WHERE DATE(AptDateTime)>=CURDATE() AND AptStatus=1 GROUP BY appointment.PatNum) A ON A.PatNum=pl.PatNum
WHERE ProcStatus = '1' AND AptNum = '0' AND ProcFee >= 100 AND (ProcDate BETWEEN DATE_SUB(NOW(), INTERVAL 14 DAY) AND DATE_SUB(NOW(), INTERVAL 8 DAY))
GROUP BY pl.PatNum ORDER BY Fees DESC;
CREATE TABLE t2(Name VARCHAR(30) NOT NULL,Age VARCHAR(3),Wireless VARCHAR(13) NOT NULL,Home VARCHAR(13) NOT NULL DEFAULT '',DateTP date,Procs VARCHAR(35) NOT NULL,Fees double NOT NULL DEFAULT 0,NextAppt date);
INSERT INTO t2(Name,Age,Wireless,Home,DateTP,Procs,Fees,NextAppt)
SELECT CONCAT(LName,', ',IF(Preferred='',FName,CONCAT(FName,' (',Preferred,')'))) AS 'Name', DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS 'Age', pt.WirelessPhone AS 'Wireless', pt.HmPhone AS 'Home', ProcDate AS 'DateTP', GROUP_CONCAT(pc.AbbrDesc) AS 'Procs', SUM(ProcFee) AS 'Fees', DATE(A.DateNextSchedApt) AS 'NextAppt'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN (SELECT appointment.PatNum, MIN(DATE(AptDateTime)) AS DateNextSchedApt FROM appointment WHERE DATE(AptDateTime)>=CURDATE() AND AptStatus=1 GROUP BY appointment.PatNum) A ON A.PatNum=pl.PatNum
WHERE ProcStatus = '1' AND AptNum = '0' AND ProcFee >= 100 AND (ProcDate BETWEEN DATE_SUB(NOW(), INTERVAL 56 DAY) AND DATE_SUB(NOW(), INTERVAL 50 DAY))
GROUP BY pl.PatNum ORDER BY Fees DESC;
CREATE TABLE t3(Name VARCHAR(30) NOT NULL,Age VARCHAR(3),Wireless VARCHAR(13) NOT NULL,Home VARCHAR(13) NOT NULL DEFAULT '',DateTP date,Procs VARCHAR(35) NOT NULL,Fees double NOT NULL DEFAULT 0,NextAppt date);
INSERT INTO t3(Name,Age,Wireless,Home,DateTP,Procs,Fees,NextAppt)
SELECT CONCAT(LName,', ',IF(Preferred='',FName,CONCAT(FName,' (',Preferred,')'))) AS 'Name', DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS 'Age', pt.WirelessPhone AS 'Wireless', pt.HmPhone AS 'Home', ProcDate AS 'DateTP', GROUP_CONCAT(pc.AbbrDesc) AS 'Procs', SUM(ProcFee) AS 'Fees', DATE(A.DateNextSchedApt) AS 'NextAppt'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN (SELECT appointment.PatNum, MIN(DATE(AptDateTime)) AS DateNextSchedApt FROM appointment WHERE DATE(AptDateTime)>=CURDATE() AND AptStatus=1 GROUP BY appointment.PatNum) A ON A.PatNum=pl.PatNum
WHERE ProcStatus = '1' AND AptNum = '0' AND ProcFee >= 100 AND (ProcDate BETWEEN DATE_SUB(NOW(), INTERVAL 126 DAY) AND DATE_SUB(NOW(), INTERVAL 120 DAY))
GROUP BY pl.PatNum ORDER BY Fees DESC;
SELECT * FROM t1
UNION
SELECT * FROM t2
UNION
SELECT * FROM t3;
DROP TABLE IF EXISTS t1,t2,t3;

[spolevoy]

Kevin, that is EXACTLY what I was looking for!
I was working on a very similar setup, except your query groups procedures, combines the three date ranges (we use last week/3weeks/3 months), shows next appt, is in general much more elegant.
Thank you so much!!!

steve

[KevinRossen]

Kevin, that is EXACTLY what I was looking for!
I was working on a very similar setup, except your query groups procedures, combines the three date ranges (we use last week/3weeks/3 months), shows next appt, is in general much more elegant.
Thank you so much!!!

steve

The other step that I do for this report is automated. I actually don't even run this report inside open dental. I've setup a batch script on my server that runs the report, exports it to a spreadsheet, and emails it to the person who will be make the calls. I have it setup to run every Monday morning before our office opens. That way I don't have to worry about someone forgetting to run it on a specific date. I can also check to see if the calls are being made, too (which I rarely have to do).

I'm interested in how successful your schedule is. I feel ok with ours, but I'm always looking to improve. The goal, of course, is to get patients to scheduled their needed treatment. Finding the sweet spot in calling often enough, but not too often can be a challenge. What feedback have you gotten on that schedule?

[spolevoy]

Kevin, that's an awesome idea.
Could you share the script?
Re calling schedule - to be honest, I don't think it matters very much.
If the patient didn't schedule at the tx plan date, chances are we failed to make them understand the need.

I doubt that if the doctor failed to communicate the urgency in a face-to-face conversation, a phone call from a receptionist will ignite the fire.
So trying to play catch up days, weeks, or months later is rarely effective...but it's better than nothing.

steve