help finding tx notes not yet completed

For users or potential users.
Post Reply
kennewickdds
Posts: 3
Joined: Wed Apr 22, 2015 12:06 pm

help finding tx notes not yet completed

Post by kennewickdds » Wed Apr 22, 2015 12:09 pm

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

User avatar
dgraffeo
Posts: 147
Joined: Wed Sep 24, 2014 3:19 pm

Re: help finding tx notes not yet completed

Post by dgraffeo » Wed Apr 22, 2015 2:45 pm

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);
"To understand what recursion is, you must first understand recursion."

David Graffeo
Open Dental Software
http://www.opendental.com

draluu
Posts: 15
Joined: Sat Mar 28, 2015 1:23 am

Re: help finding tx notes not yet completed

Post by draluu » Wed Apr 22, 2015 5:16 pm

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
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: help finding tx notes not yet completed

Post by KevinRossen » Wed Apr 22, 2015 9:22 pm

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;
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

User avatar
Hersheydmd
Posts: 700
Joined: Sun May 03, 2009 9:12 pm

Re: help finding tx notes not yet completed

Post by Hersheydmd » Sat Apr 25, 2015 10:05 am

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.
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

Post Reply