help finding tx notes not yet completed

[kennewickdds]

Is there an easy way to search for completed tx codes that dont have a tx note already attached? I feel like this gets forgotten occasionally and would love a way to bring up all the un-noted treatment easily

[dgraffeo]

I'm not sure if this is exactly what you want, but this will show you all completed procedures that have no note text.

SELECT
procedurelog.*
FROM
procedurelog
WHERE procedurelog.ProcStatus=2
AND procedurelog.ProcNum NOT IN
(SELECT
procedurelog.ProcNum
FROM
procedurelog
INNER JOIN procnote
ON procedurelog.ProcNum = procnote.ProcNum
WHERE Note != '' AND procedurelog.ProcStatus=2);

[draluu]

There are completed procedures for which there would be no note text entered such as X-Rays, products, and procedures encompassed in a group note. I have as Default note [""] in procedures where a progress note is needed, and delete that double quote when entering the progress note. At the end of the day, I generate and look over the Incomplete Procedure Notes report as per http://www.opendental.com/manual/report ... cnote.html to catch the ones I forgot to fill in.

[KevinRossen]

This is what I use. I have it run automatically every day to find any patients who did not have any notes written in their charts the day of their appointment:

Code: Select all

SET @FROMDate=DATE_SUB(CURDATE(), INTERVAL 1 DAY), @ToDate=  DATE(CURDATE());
SELECT 'Patient','AptDate','Procs','Prov'
UNION
SELECT CONCAT(pa.LName,', ',pa.FName) AS patient, pl.ProcDate AS DateApt, GROUP_CONCAT(pc.ProcCode) AS ProcedureCodes, pr.Abbr AS Provider
/*,GROUP_CONCAT(pn.Note SEPARATOR ''),GROUP_CONCAT(pn2.Note SEPARATOR ''),LENGTH(GROUP_CONCAT(pn.Note SEPARATOR '')),LENGTH(GROUP_CONCAT(pn2.Note SEPARATOR ''))*/
FROM procedurelog pl 
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
INNER JOIN patient pa ON pa.PatNum=pl.PatNum
INNER JOIN provider pr ON pr.ProvNum=pl.ProvNum
LEFT JOIN procnote pn ON pn.ProcNum=pl.ProcNum AND pn.EntryDateTime=(SELECT MAX(n2.EntryDateTime) FROM procnote n2 WHERE pn.ProcNum = n2.ProcNum) /*this looks at only the latest note, the one you see without turning on audit*/
LEFT JOIN procgroupitem pgi ON pgi.ProcNum=pl.ProcNum
LEFT JOIN procnote pn2 ON pn2.ProcNum=pgi.GroupNum AND pn2.EntryDateTime=(SELECT MAX(n3.EntryDateTime) FROM procnote n3 WHERE pn2.ProcNum = n3.ProcNum) /*this looks at only the latest group note, the one you see without turning on audit*/
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate AND (pl.ProcStatus=2 OR (pl.ProcStatus=3 AND pc.ProcCode='~GRP~')) AND pc.ProcCode NOT LIKE 'Z999%'
GROUP BY pl.PatNum,pl.ProcDate
HAVING (ISNULL(GROUP_CONCAT(pn.Note SEPARATOR '')) OR LENGTH(GROUP_CONCAT(pn.Note SEPARATOR ''))<1) /*both conditions needed as options, could be a zero length note, must also consider the commas in the group concat*/
AND (ISNULL(GROUP_CONCAT(pn2.Note SEPARATOR '')) OR LENGTH(GROUP_CONCAT(pn2.Note SEPARATOR ''))<1) /*this is for the group note*/ 
ORDER BY AptDate DESC;

[Hersheydmd]

I created another "appt confirmed" definition: "DONE" (black).
"Appt Confirmed" status shows in the box in the APPTS module right under LabCases, and you can set it to show a colored circle in the corner of every appointment.
After all the notes have been entered and signed I change the status to "Done" (black). I can tell at a glance from the daily schedule if there are any appointments that have incomplete notes.