BLOGS
EASY SUBSCRIBE


Report Writing

Interoperability and EHR Optimization

Privacy and Security

SQL Tip - Managing Diagnosis Codes

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

(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:

sql image

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…

AbstractID

Seq No

Diagnosis

Diagnosis Name
100002

1

276.1

HYPOSMOLALITY
100002

2

331.9

CEREB DEGENERATION NOS
100002

7

442.83

SPLENIC ARTERY ANEURYSM
100002

5

562.10

DIVERTICULOSIS COLON (W/O MENT OF HEMORRHAGE)
100002

4

573.8

LIVER DISORDERS NEC
100002

3

793.0

NOSP (ABN) FINDINGS ON RADIOLOGICAL &

OTH EXAM SKULL & HEAD

100002

6

737.30

IDIOPATHIC 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:

AbstractID

Dx1

Dx2

Dx3

Dx4

100002

276.1

331.9

793.0

573.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, attached to this tip is 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 Code

Dx 02 Code

Dx 03 Code

Dx 04 Code

Dx 05 Code

Dx 06 Code

V57.89

555.9

276.1

263.9

599.0

799.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 Number

Dx 01 Code

Dx 02 Code

Dx 03 Code

Dx 04 Code

Dx 05 Code

Dx 06 Code

A0914869722

774.6

         

A0203879655

648.93

789.00

784.0

     

A0970887105

789.00

496

250.00

401.9

   

A0150873503

V57.89

555.9

276.1

263.9

599.0

799.3

A0303888598

174.9

         

A0104865878

847.0

338.29

784.0

E826.1

E006.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)

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

You can find additional Report Writing Tips on our website at http://www.iatric.com/Information/NPRTips.aspx, as well as information about our on-site Report Writer Training and Report Writing Services.

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

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

For more information, please contact Karen Roemer at 978.805.3142 or email
karen.roemer@iatric.com
.

Topics: Managing Diagnosis Codes, SQL Tip, MEDITECH, DR Tip, Report Writing Tips and Tricks

Subscribe to the Report Writing blog.

Receive the latest articles directly in your inbox.
Enter your email address and click SUBSCRIBE:

Comments

2 COMMENTS