Query: Patients who left without a future appointment

For users or potential users.
Post Reply
rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Query: Patients who left without a future appointment

Post by rhaber123 » Sat Sep 29, 2018 9:34 am

This query will show you what patients had treatment and left without an appointment scheduled in the future. Patients who were seen in the last 14 days.
Change the interval from 14 days to as many days as you want
It will show the procedures performed on that day, and the patient information: their phone number, email, insurance, etc.

/*Modified 264 - what patients left with out an appointment in the future. It will show the procedures performed on that day, and the patient information: their phone number, email, insurance, etc */
/*Just change the interval values below from 14 */

SET @FromDate=Date_Sub(CurDate(),Interval 14 day),@ToDate=Date_Sub(CurDate(),Interval 0 day);
SELECT p.LName,p.FName, p.HmPhone, p.WirelessPhone, p.email,pl.ProcDate, pc.ProcCode, CarrierName AS 'PriInsName'
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum /*just in case we need field from patient table*/
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum AND ORDINAL=1/*by current primary insurance*/
LEFT JOIN inssub iss ON pp.InsSubNum=iss.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=iss.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=ip.CarrierNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND PatStatus = '0'
AND pl.ProcStatus=2
AND p.PatNum NOT IN(SELECT DISTINCT(pl.PatNum) FROM procedurelog pl
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
LEFT JOIN appointment a ON a.AptNum=pl.AptNum
WHERE ((pl.ProcDate>@ToDate AND pl.ProcStatus=2)
OR (a.AptStatus=1 AND a.AptDateTime>=CURDATE())))
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate, p.LName,p.FName;

------------------------------------------------------------------------------------------------------------------------

In the procedure codes, we created 2 Zcodes (no fees procedure codes).
One Zcode for Clinical notes - Zclin - for general info during a visit
and a second Zcode, for tooth notes - Ztooth - for info about a specific tooth during a visit
Even if no treatment was rendered and no procedure codes were entered, there will always be a Zcode in the chart and in the patient account .
Last edited by rhaber123 on Sat Sep 29, 2018 10:13 pm, edited 1 time in total.

teethdood
Posts: 267
Joined: Sun Jul 29, 2007 12:39 am
Location: Visalia, CA
Contact:

Re: Query: Patients who left without a future appointment

Post by teethdood » Fri Oct 05, 2018 7:21 pm

Thank you. Will come in handy.
Philip H. Doan, DDS
http://www.kaweahdental.com/

Post Reply