Hi,
I noticed that ProcNote table will saved the changes of the procedure note, and when I try to pull out the raw data, it appear multiple times, I am guessing it will saved the change / delete note. Is there any way we can identified which note is the active one?
Thank you!
ProcNote Stored Historical Change
Re: ProcNote Stored Historical Change
The active note is whichever is showing in the chart module when not in Audit mode. It's typically the note with the latest date stamp.
Entropy isn't what it used to be...
Arna Meyer
Arna Meyer
-
- Posts: 64
- Joined: Thu Aug 20, 2015 11:08 am
Re: ProcNote Stored Historical Change
Are you looking for a query?
SELECT *
FROM procnote pn
WHERE pn.EntryDateTime=(SELECT MAX(pn2.EntryDateTime) FROM procnote pn2 WHERE pn.ProcNum=pn2.ProcNum)
GROUP BY pn.ProcNum
ORDER BY pn.PatNum
I wouldn't recommend running this without additional WHERE clauses if you have a large procNote table. But this will return the most recent notes due the the subquery logic.
SELECT *
FROM procnote pn
WHERE pn.EntryDateTime=(SELECT MAX(pn2.EntryDateTime) FROM procnote pn2 WHERE pn.ProcNum=pn2.ProcNum)
GROUP BY pn.ProcNum
ORDER BY pn.PatNum
I wouldn't recommend running this without additional WHERE clauses if you have a large procNote table. But this will return the most recent notes due the the subquery logic.
Re: ProcNote Stored Historical Change
Thank you for your help! I do looking for a query that can shown the active note in the chart module. Just discovered that MAX(EntryDateTime) is not accurate if there are more than one note in the same procedure. Is there a field that should help identify whether it is active or not?
-
- Posts: 64
- Joined: Thu Aug 20, 2015 11:08 am
Re: ProcNote Stored Historical Change
If you are looking for a query I'd recommend searching our query page:
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
If you can not find something that works you can request your own query here:
http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx
If you go with the request route we will contact you to clarify everything about your request and quote you soon there after.
I am curious as to how MAX(EntryDateTime) does not work for you in terms of finding the 'active' procNote. The WHERE clause in the pn2 sub query requires we look at proc notes associated to the pn1 outer queries procedure and by pn.EntryDateTime = ... MAX(pn2.EntryDateTime) we are only looking at active procNotes.
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
If you can not find something that works you can request your own query here:
http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx
If you go with the request route we will contact you to clarify everything about your request and quote you soon there after.
I am curious as to how MAX(EntryDateTime) does not work for you in terms of finding the 'active' procNote. The WHERE clause in the pn2 sub query requires we look at proc notes associated to the pn1 outer queries procedure and by pn.EntryDateTime = ... MAX(pn2.EntryDateTime) we are only looking at active procNotes.