Finding Nursing Data in MEDITECH 6.0 or 6.1 Environments
Data Repository Talk

Finding Nursing Data in MEDITECH 6.0 or 6.1 Environments

Finding Data in the MEDITECH DR can be challenging. In this post, we will show you SQL code to begin getting data from the MEDITECH 6.0 or 6.1 PCS module. This is part 1 of 3.


MEDITECH’s MAT update brought lots of changes to the Data Repository Environment.  If you were using the DR on a previous platform then those changes were drastic.  The new data structure contains less redundant data and more of a normalized database than what we’ve had with MAGIC and Client Server (still no foreign keys - formally).  Nursing is one of the most complex and challenging areas to query data, and get it right.  

In this three part series I’ll teach you how to retrieve data from the nursing interventions, assessments and queries. Part 1 will focus on retrieving data in the easiest possible way with using the least amount of tables.  In Part 2, I’ll walk through group responses along with multiple responses and the use of RegAcctQuery_Results_Mult.  In Part 3, I’ll cover incorporating some of the intervention and assessment nursing tables that will allow you to print or filter on the RecordDateTime (Entered Date) and the InterventionID.

It’s easy to get overwhelmed when viewing the 100’s of PCS tables but rest assured help is here!  There is an awesome table that makes things much easier.  It’s easier because this table contains all responses in one place, RegAcctQuery_Results.  Most likely this table will satisfy 70-80% of your needs.  It has all of the important fields you typically need such as activity date and time, user, query, response, along with a field that shouldn’t be ignored InstanceID.  

It’s easy to take a wrong turn and end up with some crazy output.  There are a number of nursing tables and knowing which ones to use makes all the difference. It’s my goal to make this as painless and as easy as possible.  I’ve thought through the various scenarios and will show which tables to include and when.

-------------------------------------------------------------------------------------------------------------------------------------

This query is returning all of the basic fields needed, and I do mean basic fields needed.  The custom field in the case statement (Responses) is removing the pipes and brackets that you typically don’t want to display.

SELECT

      VisitID,

      DateTime,

      Query_MisQryID,

      InstanceID,

      ValueInfo,

 CASE WHEN Substring(RAQ_R.ValueInfo,1,1) = '{'

      THEN Replace(Replace(Replace(Replace(Replace(RAQ_R.ValueInfo , '{|', ''), '{', ''), '|}', ''), '}', ''), '|', ', ')

      ELSE RAQ_R.ValueInfo END AS Responses,

      ValueInternal,

      User_UnvUserID

FROM Testfdb.dbo.RegAcctQuery_Result RAQ_R

WHERE VisitID = 'C0-20101123101028763'

ORDER BY 1,4

PCS basic fields

-------------------------------------------------------------------------------------------------------------------------------

Sometimes you may want to display the names/description of the query and instance

SELECT

      RAQ_R.VisitID,

      RAQ_R.DateTime,

      RAQ_R.Query_MisQryID,

      MQ_M.Text,

      RAQ_R.InstanceID,

      PAI_I.Instance AS InstanceName,

      RAQ_R.ValueInfo,

 CASE WHEN Substring(RAQ_R.ValueInfo,1,1) = '{'

      THEN Replace(Replace(Replace(Replace(Replace(RAQ_R.ValueInfo , '{|', ''), '{', ''), '|}', ''), '}', ''), '|', ', ')

      ELSE RAQ_R.ValueInfo END AS Responses,

      ValueInternal,

      User_UnvUserID

FROM Testfdb.dbo.RegAcctQuery_Result RAQ_R

LEFT JOIN Testfdb.dbo.MisQry_Main MQ_M

     ON RAQ_R.SourceID = MQ_M.SourceID

     AND RAQ_R.Query_MisQryID = MQ_M.MisQryID

LEFT JOIN Testfdb.dbo.PcsAcctInst_Instances PAI_I  WITH (NOLOCK)

     ON RAQ_R.SourceID = PAI_I.SourceID

     AND RAQ_R.VisitID = PAI_I.VisitID

     AND RAQ_R.InstanceID = '{'+PAI_I.InstanceType_MisInstTypeID+'|'+PAI_I.InstanceSourceID+'|'+PAI_I.InstanceUrnID+'}'

WHERE RAQ_R.VisitID = 'C0-20101123101028763'

PCS Data with name and description

-------------------------------------------------------------------------------------------------------------------------------------

Thus far we’ve not filtered on the date and time.  Many times you’ll need the latest response or sometimes the first response. Here we are getting the latest response for each query (ignoring Instance in this case).

SELECT

      RAQ_R.VisitID,

      RAQ_R.DateTime,

      RAQ_R.Query_MisQryID,

      MQ_M.Text,

      RAQ_R.InstanceID,

      RAQ_R.ValueInfo,

 CASE WHEN Substring(RAQ_R.ValueInfo,1,1) = '{'

      THEN Replace(Replace(Replace(Replace(Replace(RAQ_R.ValueInfo , '{|', ''), '{', ''), '|}', ''), '}', ''), '|', ', ')

      ELSE RAQ_R.ValueInfo END AS Responses,

      ValueInternal,

      User_UnvUserID

FROM Testfdb.dbo.RegAcctQuery_Result RAQ_R

LEFT JOIN Testfdb.dbo.MisQry_Main MQ_M

     ON RAQ_R.SourceID = MQ_M.SourceID

     AND RAQ_R.Query_MisQryID = MQ_M.MisQryID

WHERE VisitID = 'C0-20101123101028763'

AND RAQ_R.DateTime =(SELECT MAX(RAQ_R2.DateTime) -- Gets the most recent Date for each query and patient

                                 FROM Testfdb.dbo.RegAcctQuery_Result RAQ_R2

                                 WHERE RAQ_R.SourceID = RAQ_R2.SourceID

                                 AND RAQ_R.VisitID = RAQ_R2.VisitID

                                 AND RAQ_R.Query_MisQryID = RAQ_R2.Query_MisQryID)

ORDER BY 1,4

PCS Data by latest time

It may be necessary to get the latest date time activity per Instance in which case you would add InstanceID in your subquery when returning the Max DateTime.

AND RAQ_R.DateTime =(SELECT MAX(RAQ_R2.DateTime) -- Gets the most recent Date for each query and patient

                                 FROM Testfdb.dbo.RegAcctQuery_Result RAQ_R2

                                 WHERE RAQ_R.SourceID = RAQ_R2.SourceID

                                 AND RAQ_R.VisitID = RAQ_R2.VisitID

                                 AND RAQ_R.Query_MisQryID = RAQ_R2.Query_MisQryID

                                 AND RAQ_R.InstanceID = RAQ_R2.InstanceID)

PCS Data with instance id by latest time

Because each facility does something a little different, you really need to have some understanding of how interventions and assessments are built to be able to effectively query the data.  How to get the Max date really depends on what you’re trying to accomplish in combination of understanding the data itself.

The examples that I’ve covered thus far are filtered on the VisitID.  You can easily add a filter for a query mnemonic/QueryID or a date range as well, and this same code will work.  We’ve covered the basics with retrieving PCS documentation. In the next session, we’ll delve into group responses as well as returning multiple values. I’ll include various options for displaying your final output.


By Jamie Gerardo at 4 May 2015, 11:20 AM
Share this post via

Comments

 

Post a comment

Please correct the following:
Tags
Latest Comments
By Bill Presley at 4 May 2015, 17:09 PM

Authors
Glen D'Abate
Ian Proffer
Bill Presley
Jamie Gerardo
Geoff Grouten
Ken MacPherson
Alexis Donnaruma
Taylor Solari
Theresa Jasset
Categories
Tutorial
Project
Company Information
Reports
News
Event
Subscribe






rss feedRSS