Advanced Queries: Querying the Document Table

From Galen Healthcare Solutions - Allscripts TouchWorks EHR Wiki
Jump to: navigation, search

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:

--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