https://wiki.galenhealthcare.com/index.php?title=Advanced_Queries:_Querying_the_Document_Table&feed=atom&action=historyAdvanced Queries: Querying the Document Table - Revision history2024-03-28T16:35:47ZRevision history for this page on the wikiMediaWiki 1.35.1https://wiki.galenhealthcare.com/index.php?title=Advanced_Queries:_Querying_the_Document_Table&diff=17558&oldid=prevMelanie.Rudd at 20:43, 14 February 20142014-02-14T20:43:16Z<p></p>
<table class="diff diff-contentalign-left diff-editfont-monospace" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 20:43, 14 February 2014</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l3" >Line 3:</td>
<td colspan="2" class="diff-lineno">Line 3:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Webcast details==</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Webcast details==</div></td></tr>
<tr><td class='diff-marker'>−</td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>We will be reviewing the basics of documents and visit notes, and an overview of how they are set up and used in <del class="diffchange diffchange-inline">Enterprise EEHR</del>. We will go over the Document table in Works as well as some of the other tables needed for document reporting. We will review some best practices for querying the Works database and then we will go over some sample queries in SQL that can be used as building blocks for future queries.</div></td><td class='diff-marker'>+</td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>We will be reviewing the basics of documents and visit notes, and an overview of how they are set up and used in <ins class="diffchange diffchange-inline">TouchWorks EHR</ins>. We will go over the Document table in Works as well as some of the other tables needed for document reporting. We will review some best practices for querying the Works database and then we will go over some sample queries in SQL that can be used as building blocks for future queries.</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Originally aired: Wednesday, December 4, 2013</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Originally aired: Wednesday, December 4, 2013</div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l26" >Line 26:</td>
<td colspan="2" class="diff-lineno">Line 26:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>'''Q. My organization is going through an upgrade – does that mean our version of note is changing?'''</div></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>'''Q. My organization is going through an upgrade – does that mean our version of note is changing?'''</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'>−</td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>'''A.''' The version of Notes isn’t dependent on the version of Allscripts. Some of you may be on v11.4 of <del class="diffchange diffchange-inline">Enterprise</del>, but have v10 note.</div></td><td class='diff-marker'>+</td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>'''A.''' The version of Notes isn’t dependent on the version of Allscripts. Some of you may be on v11.4 of <ins class="diffchange diffchange-inline">TouchWorks</ins>, but have v10 note.</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"></td></tr>
</table>Melanie.Ruddhttps://wiki.galenhealthcare.com/index.php?title=Advanced_Queries:_Querying_the_Document_Table&diff=17139&oldid=prevFallon.Hartford: Created page with "Category:Webcasts __TOC__ ==Webcast details== We will be reviewing the basics of documents and visit notes, and an overview of how they are set up and used in Enterprise ..."2014-01-02T14:51:59Z<p>Created page with "<a href="/index.php/Category:Webcasts" title="Category:Webcasts">Category:Webcasts</a> __TOC__ ==Webcast details== We will be reviewing the basics of documents and visit notes, and an overview of how they are set up and used in Enterprise ..."</p>
<p><b>New page</b></p><div>[[Category:Webcasts]]<br />
__TOC__<br />
<br />
==Webcast details==<br />
We will be reviewing the basics of documents and visit notes, and an overview of how they are set up and used in Enterprise EEHR. We will go over the Document table in Works as well as some of the other tables needed for document reporting. We will review some best practices for querying the Works database and then we will go over some sample queries in SQL that can be used as building blocks for future queries.<br />
<br />
Originally aired: Wednesday, December 4, 2013<br />
Presenters: Fallon Hartford<br />
<br />
==Webcast materials==<br />
[[Media:Advanced_Queries_Querying_the_Document_Table.pdf|Download slideshow]]<br />
Presented 12/4/2013<br />
<br />
==Q&A==<br />
<br />
'''Q. Why can the EIEID in the Document Table be either zero or null? What is the difference?''' <br />
<br />
'''A.''' When the EIEID is zero, entered through application, so that would be like a visit note. If it is null, then it was an inbound document through the interface. <br />
<br />
<br />
'''Q. Can a document be amended after it’s finalized?'''<br />
<br />
'''A.''' Yes, a provider can amend a note after it is been signed in the application. If a note has been amended, the Status in the Document Table becomes 'Amended,Finalized'<br />
<br />
<br />
'''Q. My organization is going through an upgrade – does that mean our version of note is changing?'''<br />
<br />
'''A.''' The version of Notes isn’t dependent on the version of Allscripts. Some of you may be on v11.4 of Enterprise, but have v10 note.<br />
<br />
<br />
<br />
<br />
Sample Queries Reviewed:<br />
<br />
<source lang=tsql><br />
--To find all of the most recent versions of a document not entered in error<br />
SELECT * FROM dbo.Document<br />
WHERE NextVersionID = 0 <br />
AND (EIEID = 0 OR EIEID IS NULL)<br />
<br />
<br />
<br />
<br />
<br />
--To find the Patient, Author, Recorded Date and Encounter Date of all finalized Ambulatory Followup Documents<br />
SELECT <br />
Document.DocumentID,<br />
dbo.Document_Type_de.EntryName,<br />
per.lastname+', '+per.FirstName AS 'PatientName',<br />
prov.lastname+', '+prov.firstname AS 'Author Name',<br />
Document.RecordedDTTM,<br />
Encounter.DTTM ,<br />
Document_event_DE.EntryName<br />
FROM document <br />
INNER JOIN dbo.Document_Type_de ON Document_Type_DE.id = Document.DocumentTypeDE<br />
INNER JOIN person per ON per.id = Document.PatientID<br />
INNER JOIN person prov ON prov.id = Document.AuthorID<br />
INNER JOIN encounter ON Encounter.id = Document.EncounterID<br />
INNER JOIN dbo.DocumentEvent ON DocumentEvent.DocumentID = Document.DocumentID<br />
INNER JOIN dbo.Document_Event_DE ON dbo.Document_Event_DE.ID = documentevent.DocumentEventDE<br />
WHERE NextVersionID = 0<br />
AND ISNULL(EIEID,0) = 0<br />
AND Document.status Like '%Final%'<br />
AND Document_Type_DE.EntryName = 'Amb Followup'<br />
<br />
<br />
<br />
<br />
<br />
--To find a list of all appointments that do not have a note associated with them<br />
SELECT <br />
e.id AS EncounterID,<br />
per.LastName+', '+per.FirstName AS 'PatientName',<br />
app.StartDTTM AS ApptDTTM<br />
FROM Encounter e<br />
INNER JOIN Appointment app ON app.EncounterID = e.ID -- joining to an appointment allows me to see encounters associated with a face to face appointment<br />
INNER JOIN Appointment_Status_DE AS asd ON asd.id = app.AppointmentStatusDE<br />
INNER JOIN Person AS per ON per.ID = e.PatientID<br />
/***<br />
This nested join creates a list of documentIDs and their associated encounterIDs for all<br />
notes that are final. We can left join this to a query for encounters to see if an encounter <br />
has a note.<br />
***/<br />
LEFT JOIN (<br />
SELECT <br />
d.documentID, <br />
d.encounterID<br />
FROM document d <br />
INNER JOIN dbo.Document_Type_de AS dtd ON dtd.id = d.DocumentTypeDE<br />
WHERE 1=1<br />
AND d.NextVersionID= 0 <br />
AND ISNULL(d.EIEID,0) = 0 <br />
AND d.Status LIKE '%final%' -- if you want to see any type of note, final or not, then remove this field<br />
) docs ON docs.EncounterID = e.ID<br />
WHERE asd.EntryName = 'Arrived'<br />
AND docs.DocumentID IS NULL -- No Final note exists</div>Fallon.Hartford