SQL Tip: Wrangling the SurCase_DocQuestions Table in MEDITECH 6.1

If you’ve been working with clinical documentation in the new SUR module in MEDITECH 6.1, you may have found that surgical interventions, assessments, query responses, and some standard fields are captured in this table:

SurCase_DocQuestions

Which you get at from the Visit by:

RegAcct_Main > CwsAppt_Main > SurCase_DocInstances > SurCase_DocQuestions

However, once you’re in the table with the question, you’ve got a variety of problems to overcome:

1. The DocumentQuestionValue field is too short. In many cases it is only a VARCHAR(45). In other DocQuestions tables, this field is a VARCHAR(1250).
2. Some of the “queries” with responses stored in this table aren’t actually queries (and don’t appear in MisQry_Main at all). Instead, they are standard fields that save their responses to this table. An example is “Pre-Op Diagnosis.”
3. But “Pre-Op Diagnosis” is a multi-line field that might contain diagnosis codes, diagnosis text, freeform text, etc. In fact, if a provider had a complex case, even a VARCHAR(1250) might not hold all of the data in this field.
4. So, in this case of “Pre-Op/Post-Op Diagnosis,” you don’t even want this field at all!

What to Do?

Well, the first thing is open a ticket with MEDITECH to get this DTS installed:

• RPT-67564

This will expand the DocumentQuestionValue field to a VARCHAR(1250) like the others. When you open that ticket, make sure to include a note requesting that the table be re-IL’d (if you don’t have SIL capability and can do it yourself).

If you’re trying to get at the Pre-Op/Post-Op Diagnosis, check to see if these tables are populated on your DR instance:

• SurCase_ActualProcs_ActualProcPreOpDx
• SurCase_ActualProcs_ActualProcPostOpDx

As they should be used instead. If they are empty, open a ticket to get them populated.

This structure holds any amount of text data in blocks of 250 characters per row.

If your MT 6.1 instance does have those tables, you can generate a concatenated field to hold the diagnosis responses like so:

Note the CHAR(10) prefixing each line (which the STUFF then strips off from the beginning of the string once the concatenation has finished up) lets you display this field in SSRS with line breaks.

Going back to the information stored in the DocumentQuestionValue field, however, your troubles are only beginning! The field uses PCS-style data capture, which means it can be multi-valued, packed, and sub-divided into elements within a packed section.

If you’re a subscriber to our DR Resource Center, we have a slate of functions like IatricMultiColumnValueBreakdown() that can help you get at the information you want from one of these packed fields. If you don't license DR Resource Center, my first recommendation is that you do, but you can also follow the approach detailed in this Parsing Packed Fields blog post.

None of it is pretty… So, the best approach here is to identify what specific items you are looking for, then construct a parsing pattern (using REPLACE, Split, etc. as needed) to pick out the specific piece of the packed string that you need. On a case by case basis.

An Example

Here’s an example of breaking down the ASA Class documentation from one of these SUR question fields:

If you need more help…

Our Report Writing team can help you fix reports, create new ones, make old ones run faster, and much more. Simply reach out to your Iatric Systems Account Executive, or our NPR report writing team at reportwriting@iatric.com, or click the button below to contact us and learn how we can help support your team!