SQL Tip: Managing Diagnosis Codes (5.67 edition)

A common challenge in reporting (or extracting data, in this case) that we see over and over again is the need to transform one data structure into another. The best arrangement of data for the programmer writing the online application is not necessarily the best arrangement for the person writing a report, or an extract.

The diagnosis codes (or procedure codes) in MEDITECH’s ABS module are a perfect example of this. For the programmer, you want a list of codes (and attendant extra fields) per abstracted case, and that looks like this:

Where we see that the list of diagnosis codes is stored, and sorted, in dx.seq.no order and is a number field that can take up to 99 codes. If we turn around and look at the matching DR table (AbsDrgDiagnoses) with a query like this:

We see…

Which looks great…until the report writer building an extract gets a request to show the data like this instead:

In this view of the data, we want one row per abstracted case, with the diagnosis codes “flattened” up into that single row, for up to 24 codes. Which is perfectly possible in SQL, like this:

 Which produces what we want:

We get one row per abstracted case, and the DX codes folded up into discrete columns, because we JOIN’ed in the AbsDrgDiagnoses table three times…and if we wanted 24 discrete columns, we would have to join that table in 24 times! And if we needed all possible DX codes – up to that 99 we mentioned before – that is 99 JOIN’s that we have to write out.

When you hit this problem, you start thinking about…a CURSOR, or a TABLE-VALUED FUNCTION, or a PIVOT…but the easiest and fastest way to address this problem is to build a VIEW.

In the past, we’ve talked about code reuse and Lego^® building blocks. This is the perfect place to build a VIEW that flattens down all 99 possible diagnosis codes into one line per abstracted case. “But!” you say…“That is a lot of work!”

Yes…but you only have to do it once. And even better, click here for a VIEW that does it for you! Well, actually, it brings in the first 50 diagnosis codes. Which should cover you for 99% of all cases. But if you need the other 49…they are easy to add.

So you can just install that view in your zcus database instance (after updating the database references for your DR databases), and then you can bring in the data from the VIEW all day long, like this:

Which gets you:

You can also just JOIN to the VIEW as well, as part of the code for your export:

(Note that in MT 6.1 the AbstractID field goes away, and is replaced by VisitID)

What about performance using this VIEW? The key for good performance is to JOIN to the VIEW via a primary key, or other indexed field, in the top table in the VIEW, which in this case is AbstractData. If you try doing a JOIN or a WHERE to a specific DX code in one of the Dx??Code fields, then performance will be slower.

In this case, that means JOIN’ing to the View via SourceID and AbstractID for optimal performance.

With this in your toolkit, you can easily pull in up to 50 (or more) DX codes flattened down to the abstracted case level with as little effort as possible!

If you need more help…

Visit our report library at: http://www.iatric.com/Information/NPRReportLibrarySearch.aspx.

To subscribe and receive email notifications for new Report Writing classes, please follow this link:

http://www.iatric.com/Information/Classes.aspx.

Our Report Writing team can help you fix reports, create new ones, and make old ones faster. Simply reach out to your Iatric Systems Account Executive our NPR report writing team at reportwriting@iatric.com to discuss how we can help support your team!