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

Finding Nursing Data in MEDITECH 6.0/6.1 Environments - Part 2

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 2 of 3.


Welcome to part 2 of our 3 part series in the exciting world of nursing data.  In this segment I’ll focus on querying for a specific assessment, getting the assessment name and querying for multiple responses.

The assessment is available in RegAcctQuery_Results which we’ve already been working with so we’ll tackle this first.  The field Source is a concatenated field containing information about the activity which is key to getting additional data needed for filtering on a specific assessment or for displaying the name of the assessment.

Let’s take a look at a couple of example field outputs for Source.  Perhaps in another blog (or you can email me if interested), I can breakdown each item within the Source field.  For our purposes we’ll stick with the third item, the assessment mnemonic.  The assessment is always just after the caret. We need to parse out that item by getting the characters just to the right of the caret and then just to the left of the close squiggly bracket.

SUBSTRING(Source,CHARINDEX('^',Source)+1,(CHARINDEX('}',Source)-1) -(CHARINDEX('^',Source,+1)))

Finding Assessment ID in Nursing

______________________________________________________________________________________

Building on that, we’re going to take a look at obtaining the assessment name and review the output of a group response multiple using RegAcctQuery_Results.  Once you have the MisDocSectID (AssessmentID) you can filter, display and join to other tables as needed.  In this example I’m displaying and joining to another table.  There are a couple of items I’d like to explain in this query.

1.       Nursing documentation has a source type of ‘PA’ in the RegAcctQuery_Result table. You’ll need to filter on that otherwise you’ll get an error on the substring because it only works for this particular type.

2.             The CASE statement that parses out the result value replaces the pipes within the standard concatenated field and replaces it with a comma to display as a list of responses in a more useable format (which was also used in Part 1).

This method and table work great if you want all of your responses in this format for a particular group response.  Here you can see in the highlighted Responses below that the group responses are in a list separated by commas, rather than the pipes and brackets. Next we’ll look at multiple responses using a different table.  

SELECT
     RAQ_R.VisitID,
    SUBSTRING(RAQ_R.Source, CHARINDEX('^', RAQ_R.Source)+1,
    (CHARINDEX('}', RAQ_R.Source)-1)
    -(CHARINDEX('^', RAQ_R.Source,+1)))
AS AssessmentID,  
     MDSM.Name,
     RAQ_R.SourceType,
     RAQ_R.DateTime,
     RAQ_R.Query_MisQryID,
     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,
     RAQ_R.ValueInternal,
     RAQ_R.User_UnvUserID
FROM Testfdb.dbo.RegAcctQuery_Result RAQ_R
INNER JOIN Testfdb.dbo.MisDocSect_Main MDSM
     ON RAQ_R.SourceID = MDSM.SourceID
AND  SUBSTRING(Source,CHARINDEX('^',Source)+1,(CHARINDEX('}',Source)-1) -(CHARINDEX('^',Source,+1)))  = MDSM.MisDocSectID

WHERE RAQ_R.VisitID = 'V0-B20131206084129722'
     AND RAQ_R.SourceType = 'PA'
ORDER BY 3,5

Nursing Assessment ID Group Responses

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

Looking at this table by itself, this may look closer to what you would expect to see.  One row per group response value rather than one row with all group responses.  We also get some additional fields in separate columns such as the GroupResponseMnemonic and the ElementMnemonicID.

SELECT *
FROM Testfdb.dbo.RegAcctQuery_Result_Value_Mult RAQ_R_V_M

WHERE RAQ_R_V_M.VisitID = 'V0-B20131206084129722'
AND RAQ_R_V_M.Query_MisQryID IN('NEURO.ORIE','SS.PREIND2')

Nursing Assessment ID with Element ID

Whether you use the RegAccountQuery_Result (RAQ_R) or RegAcctQuery_Result_Value_Mult (RAQ_R_V_M) simply depends on your needs at the time, either for layout or other programming needs.  If you need the RAQ_R_V_M table and you also need the user (which many times you do) then you will need both tables.   Be sure to join on all the primary keys!

SELECT
     RAQ_R_V_M.VisitID,
     RAQ_R.DateTime,
     RAQ_R_V_M.Query_MisQryID,
     RAQ_R_V_M.InstanceID,
     RAQ_R_V_M.SeqID,
     RAQ_R_V_M.ValueInfo,
     RAQ_R.ValueInternal,
     RAQ_R.User_UnvUserID,
     RAQ_R_V_M.GroupResponseMnemonic,
     RAQ_R_V_M.ElementMnemonicID

FROM Testfdb.dbo.RegAcctQuery_Result_Value_Mult RAQ_R_V_M
INNER JOIN Testfdb.dbo.RegAcctQuery_Result RAQ_R
     ON RAQ_R_V_M.SourceID = RAQ_R.SourceID
     AND RAQ_R_V_M.VisitID = RAQ_R.VisitID
     AND RAQ_R_V_M.Query_MisQryID = RAQ_R.Query_MisQryID
     AND RAQ_R_V_M.InstanceID = RAQ_R.InstanceID
     AND RAQ_R_V_M.UrnID = RAQ_R.UrnID
WHERE RAQ_R_V_M.VisitID = 'V0-B20131206084129722'
     AND RAQ_R_V_M.Query_MisQryID IN('NEURO.ORIE','SS.PREIND2')
ORDER BY 3,5

 

You would also use both tables if you need either the Intervention or an Assessment for a filter, or to print the description.  The assessment was covered already and I’ll cover the Intervention piece in Part 3.

You may be wondering how you would write the code if you needed both single responses and multiple responses.  You would use something very similar to the above.
ResponseOption_1 – You would query only the RegAcctQuery_Result table using the CASE statement we’ve used several times now.  You would remove the join to RAQ_R_V_M as it’s not needed in this case.

ResponseOption_2 – Here you need both tables with a LEFT JOIN to RAQ_R_V_M.  We replace RAQ_R.ValueInfo with ISNULL(RAQ_R_V_M.ValueInfo, RAQ_R.ValueInfo). This gets the value from the multiple table first if there is one else it uses the RAQ_R value.

SELECT
     RAQ_R.VisitID,
     RAQ_R.DateTime,
     RAQ_R.Query_MisQryID,
     RAQ_R_V_M.SeqID,
     RAQ_R.ValueInfo,
     RAQ_R_V_M.ValueInfo,
     RAQ_R_V_M.ElementMnemonicID,
     CASE WHEN Substring(RAQ_R.ValueInfo,1,1) = '{'
      THEN Replace(Replace(Replace(Replace(Replace(RAQ_R.ValueInfo , '{|', ''), '{', ''), '|}', ''), '}', ''), '|', ', ')
      ELSE RAQ_R.ValueInfo END AS ResponseOption_1, -- single row with all values - multiple or single responses (Remove RAQ_R_V_M table)

   CASE WHEN Substring(ISNULL(RAQ_R_V_M.ValueInfo,RAQ_R.ValueInfo),1,1) = '{'
        THEN Replace(Replace(Replace(Replace(Replace(RAQ_R.ValueInfo , '{|', ''), '{', ''), '|}', ''), '}', ''), '|', ', ')
        ELSE ISNULL(RAQ_R_V_M.ValueInfo,RAQ_R.ValueInfo) END AS ResponseOption_2

FROM Testfdb.dbo.RegAcctQuery_Result RAQ_R
LEFT JOIN Testfdb.dbo.RegAcctQuery_Result_Value_Mult RAQ_R_V_M
     ON RAQ_R_V_M.SourceID = RAQ_R.SourceID
     AND RAQ_R_V_M.VisitID = RAQ_R.VisitID
     AND RAQ_R_V_M.Query_MisQryID = RAQ_R.Query_MisQryID
     AND RAQ_R_V_M.InstanceID = RAQ_R.InstanceID
     AND RAQ_R_V_M.UrnID = RAQ_R.UrnID
WHERE RAQ_R.VisitID = 'V0-531094457937'
AND RAQ_R.Query_MisQryID IN('MS.EXLE','RESP.MINV','RESP.OXPD')

ORDER BY 3,5

Data from PCS/Nursing

I’ve covered the basics and given you sample code.  In practice you’ll most likely join to several other tables.  If you run into any problems or have questions, feel free to email me at jgerardo@acmeware.com.  Also if you are a beginner and need more of explanation on any part of the code you can contact me.  In part 3, I’ll focus on filtering and printing the Intervention name and using the Record date.  This will take us into several of the nursing tables.  


By Jamie Gerardo at 1 Jul 2015, 10:06 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