Keyword match in commlog query

For users or potential users.
Post Reply
juntas
Posts: 5
Joined: Thu Nov 10, 2022 6:25 am

Keyword match in commlog query

Post by juntas » Tue Nov 15, 2022 10:58 am

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;

agrasley
Posts: 15
Joined: Mon Jan 03, 2022 11:45 am

Re: Keyword match in commlog query

Post by agrasley » Fri Nov 25, 2022 1:01 pm

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 :

Code: Select all

INNER JOIN commlog cl ON p.PatNum = cl.PatNum AND cl.CommType<>439
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.

Code: Select all

LEFT JOIN (
		SELECT
			commlog.CommlogNum,
			commlog.PatNum,
			commlog.Note
		FROM commlog
		WHERE commlog.CommType = 439
			AND commlog.CommDateTime BETWEEN @SeenFrom AND @SeenTo
	) cl
		ON cl.PatNum = p.PatNum
	WHERE DATE(appt.AptDateTime) BETWEEN @SeenFrom AND @SeenTo
		AND appt.AptStatus = 2	/*Complete*/
		AND ISNULL(cl.CommlogNum)
Let me know if this doesn't work for what you are needing!
Alex Grasley
Open Dental Software
http://www.opendental.com

juntas
Posts: 5
Joined: Thu Nov 10, 2022 6:25 am

Re: Keyword match in commlog query

Post by juntas » Fri Nov 25, 2022 1:20 pm

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

Code: Select all

INNER JOIN commlog cl ON p.PatNum = cl.PatNum AND cl.CommType<>439

juntas
Posts: 5
Joined: Thu Nov 10, 2022 6:25 am

Re: Keyword match in commlog query

Post by juntas » Fri Nov 25, 2022 1:23 pm

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

Post Reply