Advanced Queries: Querying the Document Table
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 TouchWorks EHR. 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.
Originally aired: Wednesday, December 4, 2013 Presenters: Fallon Hartford
Webcast materials
Download slideshow Presented 12/4/2013
Q&A
Q. Why can the EIEID in the Document Table be either zero or null? What is the difference?
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.
Q. Can a document be amended after it’s finalized?
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'
Q. My organization is going through an upgrade – does that mean our version of note is changing?
A. The version of Notes isn’t dependent on the version of Allscripts. Some of you may be on v11.4 of TouchWorks, but have v10 note.
Sample Queries Reviewed:
<source lang=tsql> --To find all of the most recent versions of a document not entered in error SELECT * FROM dbo.Document WHERE NextVersionID = 0 AND (EIEID = 0 OR EIEID IS NULL)
--To find the Patient, Author, Recorded Date and Encounter Date of all finalized Ambulatory Followup Documents SELECT Document.DocumentID, dbo.Document_Type_de.EntryName, per.lastname+', '+per.FirstName AS 'PatientName', prov.lastname+', '+prov.firstname AS 'Author Name', Document.RecordedDTTM, Encounter.DTTM , Document_event_DE.EntryName
FROM document
INNER JOIN dbo.Document_Type_de ON Document_Type_DE.id = Document.DocumentTypeDE INNER JOIN person per ON per.id = Document.PatientID INNER JOIN person prov ON prov.id = Document.AuthorID INNER JOIN encounter ON Encounter.id = Document.EncounterID INNER JOIN dbo.DocumentEvent ON DocumentEvent.DocumentID = Document.DocumentID INNER JOIN dbo.Document_Event_DE ON dbo.Document_Event_DE.ID = documentevent.DocumentEventDE WHERE NextVersionID = 0 AND ISNULL(EIEID,0) = 0 AND Document.status Like '%Final%' AND Document_Type_DE.EntryName = 'Amb Followup'
--To find a list of all appointments that do not have a note associated with them SELECT e.id AS EncounterID, per.LastName+', '+per.FirstName AS 'PatientName', app.StartDTTM AS ApptDTTM FROM Encounter e 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 INNER JOIN Appointment_Status_DE AS asd ON asd.id = app.AppointmentStatusDE INNER JOIN Person AS per ON per.ID = e.PatientID /*** This nested join creates a list of documentIDs and their associated encounterIDs for all notes that are final. We can left join this to a query for encounters to see if an encounter has a note.
- /
LEFT JOIN ( SELECT d.documentID, d.encounterID FROM document d INNER JOIN dbo.Document_Type_de AS dtd ON dtd.id = d.DocumentTypeDE WHERE 1=1 AND d.NextVersionID= 0 AND ISNULL(d.EIEID,0) = 0 AND d.Status LIKE '%final%' -- if you want to see any type of note, final or not, then remove this field ) docs ON docs.EncounterID = e.ID WHERE asd.EntryName = 'Arrived' AND docs.DocumentID IS NULL -- No Final note exists