Open Dental Forum

We wanted to track patients who are leaving without an appointment but not include those who have said they don't want to schedule a follow up/re-care appointment. I added a commlog type (439) for my staff to use so that the query can discard those patients. I've taken open dental example query 1265 and tried to achieve my intent but the problem I'm facing is that with the query below I'm still getting patients who have the commLog type 439. Debugging the issue I found that when a patient has another commLog entry in the given time period then the query still includes them even though commlog type 439 is present. What should I add to the query?

/*1265 Patients seen between two dates, that do not have an appointment (Scheduled or complete) after a specific date*/
SET @SeenFrom=adddate(curdate(), INTERVAL 1-DAYOFWEEK(curdate()) DAY), @SeenTo=adddate(curdate(), INTERVAL 5-DAYOFWEEK(curdate()) DAY);
SET @NoApptAfter=adddate(curdate(), INTERVAL 1 DAY);

/*-------------------- Do not modify under this line --------------------*/
/*Query code written/modified: 05/23/2017, 05/23/2019 MattG,03/05/2021:SalinaK*/
#
SET @Pos = 0;

SELECT
@Pos := @Pos +1 AS 'Count',
a.LName as 'Last Name',
a.Fname as 'First Name',
a.DateLastVisit as 'Last Visit Date',
a.City,
a.State,
a.HmPhone,
a.WirelessPhone
FROM (
SELECT
p.LName,
p.FName,
DATE_FORMAT(MAX(lastseen.AptDateTime),'%m/%d/%Y') AS 'DateLastVisit',
p.Address,
p.Address2,
p.City,
p.State,
p.Zip,
p.HmPhone,
p.WkPhone,
p.WirelessPhone,
t1.TpAmt AS '$TpAmt_',
cl.Note,
(
SELECT
GROUP_CONCAT(DISTINCT ap.PatNum)
FROM appointment ap
WHERE DATE(ap.AptDateTime) >= @NoApptAfter
AND ap.AptStatus IN (1,2) /*Scheduled, Complete*/
AND ap.PatNum = p.PatNum
) AS AptAfter
FROM appointment appt
INNER JOIN patient p
ON p.PatNum = appt.PatNum
INNER JOIN commlog cl ON p.PatNum = cl.PatNum AND cl.CommType<>439
INNER JOIN appointment lastseen
ON lastseen.PatNum = p.PatNum
AND lastseen.AptStatus = 2 /*Complete*/
AND DATE(lastseen.AptDateTime) BETWEEN @SeenFrom AND CURDATE()
/*left JOIN procedurelog pl ON pl.PatNum=p.PatNum
AND pl.ProcStatus=1*/
LEFT JOIN(
SELECT
SUM(pl.ProcFee*(pl.UnitQty+pl.BaseUnits)) AS 'TpAmt',
pl.PatNum
FROM procedurelog pl
WHERE ProcStatus = 1 -- Treatment Planned
GROUP BY PatNum
)t1
ON t1.PatNum = p.PatNum
WHERE DATE(appt.AptDateTime) BETWEEN @SeenFrom AND @SeenTo
AND appt.AptStatus = 2 /*Complete*/
AND cl.CommDateTime BETWEEN @SeenFrom AND @SeenTo
GROUP BY p.PatNum
HAVING ISNULL(AptAfter)
ORDER BY p.LName,p.FName
)a;

I think this mod should work for you. What your query was doing was getting every patient that had a commlog that was not the one you wanted. Rather than only excluding those that had that commlog. That's what this line was doing : What I changed was to switch it to a LEFT JOIN looking for commlogs of that type in the date range and then adding to the WHERE clause a line that excluded anyone that had results in the LEFT JOINed query.
Let me know if this doesn't work for what you are needing!

Actually, I need the line below because patients having this commType need to be excluded.

Can you also PM me the complete query that you used?