Missed Appointment Note

[KevinRossen]

I'm working on revamping one of my queries and I've discovered something new regarding the broken appointment function that utilizes the CDT D9986 code. It seems to automatically add an entry to the procnote table as soon as the appointment is broken. Then it adds an additional procnote when the OK button is entered. So, there will always either be a duplicate procnote or a second note if the user enters notes for the missed appointment.

I understand that this is probably great for auditing purposes, but it's challenging me to pull the most recent note for some queries I'm re-doing. I'm including a broken appointment query for example below:

Code: Select all

SELECT
	pl.ProcDate AS DateBroken,
	CONCAT(p.LName,', ',p.FName) AS Patient,
	DATE(ap.DateNextSchedApt) AS NextApt,
	pc.Descript AS BrokeType,
	pn.Note AS BrokeNote
FROM procedurelog pl
LEFT JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
LEFT JOIN procnote pn ON pl.ProcNum = pn.ProcNum
LEFT JOIN patient p ON pl.PatNum = p.PatNum
LEFT JOIN 
	(
	SELECT
		a.PatNum,
		MIN(DATE(a.AptDateTime)) AS DateNextSchedApt
	FROM appointment a
	WHERE 
		DATE(a.AptDateTime)>=CURDATE()
		AND a.AptStatus IN (1,2,4)
	GROUP BY a.PatNum
	) ap ON p.PatNum=ap.PatNum
WHERE 
	pc.ProcCode LIKE 'D9986%'
	AND pl.ProcStatus = 2
	AND pl.ProcDate = CURDATE()
;

[jsalmon]

The procedure note with the largest date and time is the most recent note. So you simply need to add something like this to your WHERE clause:

Code: Select all

AND pn.EntryDateTime=(SELECT MAX(pn2.EntryDateTime)
FROM procnote pn2
WHERE pn.ProcNum = pn2.ProcNum)

[KevinRossen]

The procedure note with the largest date and time is the most recent note. So you simply need to add something like this to your WHERE clause:

Code: Select all

AND pn.EntryDateTime=(SELECT MAX(pn2.EntryDateTime)
FROM procnote pn2
WHERE pn.ProcNum = pn2.ProcNum)

Worked like a charm. Thanks!!! Below is my modified query for those who are interested. It actually finds broken appointments from the previous day.

Code: Select all

SELECT
	pl.ProcDate AS DateBroken,
	CONCAT(p.LName,', ',p.FName) AS Patient,
	DATE(ap.DateNextSchedApt) AS NextApt,
	pc.Descript AS BrokeType,
	pn.Note AS BrokeNote
FROM procedurelog pl
LEFT JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
LEFT JOIN procnote pn ON pl.ProcNum = pn.ProcNum
LEFT JOIN patient p ON pl.PatNum = p.PatNum
LEFT JOIN 
	(
	SELECT
		a.PatNum,
		MIN(DATE(a.AptDateTime)) AS DateNextSchedApt
	FROM appointment a
	WHERE 
		DATE(a.AptDateTime)>=CURDATE()
		AND a.AptStatus IN (1,2,4)
	GROUP BY a.PatNum
	) ap ON p.PatNum=ap.PatNum
WHERE 
	pc.ProcCode LIKE 'D9986%'
	AND pl.ProcStatus = 2
	AND pl.ProcDate = (SELECT MAX(DATE(AptDateTime)) FROM appointment WHERE AptStatus=2 AND DATE(AptDateTime)<CURDATE())
	AND pn.EntryDateTime=(SELECT MAX(pn2.EntryDateTime) FROM procnote pn2 WHERE pn.ProcNum = pn2.ProcNum);