SQL Tip – Managing Diagnosis Codes

(NOTE: This post only applies to hospitals operating MEDITECH 5.66 or lower. For hospitals operating MEDITECH 5.67 or higher, please refer to Thomas’ March, 2016 post. Thank you.)

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 on-line application is not necessarily the best arrangement for the person writing a report or an extract.

The diagnosis (or procedure) codes in ABS 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 that 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:

SELECT ADX.AbstractID AS AbstractID

,ADX.DiagnosisSeqID AS DiagnosisSeqID

,COALESCE(ADX.Diagnosis,'(?)’) AS Diagnosis

,COALESCE(ADI.[Name],'(No Diagnosis Entered)’) AS DiagnosisName

FROM AbsDrgDiagnoses ADX

LEFT JOIN DAbsDiagnoses ADI ON ( ADI.DiagnosisCodeID = ADX.Diagnosis

AND ADI.SourceID = ADX.SourceID )

We see…

AbstractIDSeq NoDiagnosisDiagnosis Name
1000021276.1HYPOSMOLALITY
1000022331.9CEREB DEGENERATION NOS
1000027442.83SPLENIC ARTERY ANEURYSM
1000025562.10DIVERTICULOSIS COLON (W/O MENT OF HEMORRHAGE)
1000024573.8LIVER DISORDERS NEC
1000023793.0NOSP (ABN) FINDINGS ON RADIOLOGICAL & OTH EXAM SKULL & HEAD
1000026737.30IDIOPATHIC SCOLIOSIS

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

dx1|dx2|dx3|dx4 … |dx24

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:

SELECT ASD.AbstractID AS AbstractID

,DX1.Diagnosis AS Dx1

,DX2.Diagnosis AS Dx2

,DX3.Diagnosis AS Dx3

,DX4.Diagnosis AS Dx4

FROM AbstractData ASD

LEFT JOIN AbsDrgDiagnoses DX1 ON ( DX1.AbstractID = ASD.AbstractID

AND DX1.SourceID = ASD.SourceID

AND DX1.DiagnosisSeqID = 1 )

LEFT JOIN AbsDrgDiagnoses DX2 ON ( DX2.AbstractID = ASD.AbstractID

AND DX2.SourceID = ASD.SourceID

AND DX2.DiagnosisSeqID = 2 )

LEFT JOIN AbsDrgDiagnoses DX3 ON ( DX3.AbstractID = ASD.AbstractID

AND DX3.SourceID = ASD.SourceID

AND DX3.DiagnosisSeqID = 3 )

LEFT JOIN AbsDrgDiagnoses DX4 ON ( DX4.AbstractID = ASD.AbstractID

AND DX4.SourceID = ASD.SourceID

AND DX4.DiagnosisSeqID = 4 )

Which produces what we want:

AbstractIDDx1Dx2Dx3Dx4
100002276.1331.9793.0573.8

We get one row per abstracted case, and the DX codes folded up into discrete columns, because we JOIN’ed in the AbsDrgDiagnoses table four 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, we have 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:

SELECT ADF.Dx01Code,ADF.Dx02Code,ADF.Dx03Code,ADF.Dx04Code,ADF.Dx05Code,ADF.Dx06Code

FROM dbo.IatricAbsDxCodesFlattened ADF

Which gets you:

Dx 01 CodeDx 02 CodeDx 03 CodeDx 04 CodeDx 05 CodeDx 06 Code
V57.89555.9276.1263.9599.0799.3

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

SELECT ASD.AccountNumber, ADF.Dx01Code, ADF.Dx02Code, ADF.Dx03Code, ADF.Dx04Code,

ADF.Dx05Code, ADF.Dx06Code

FROM AbstractData ASD

LEFT JOIN zcus.dbo.IatricAbsDxCodesFlattened ADF ON ( ADF.AbstractID = ASD.AbstractID AND ADF.SourceID = ASD.SourceID )

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

Account NumberDx 01 CodeDx 02 CodeDx 03 CodeDx 04 CodeDx 05 CodeDx 06 Code
A0914869722774.6     
A0203879655648.93789.00784.0   
A0970887105789.00496250.00401.9  
A0150873503V57.89555.9276.1263.9599.0799.3
A0303888598174.9     
A0104865878847.0338.29784.0E826.1E006.4 

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.

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!

(end)

Learn more by clicking on the button below:

Written by Thomas Harlan, Jim McGrath, Reporting Services Team - iatricSystems

Request a Meeting

Let’s connect! Schedule a meeting to explore how SQL Tip – Managing Diagnosis Codes can work for you.

"*" indicates required fields

This field is for validation purposes and should be left unchanged.
Name*