BLOGS
EASY SUBSCRIBE


Report Writing

Interoperability and EHR Optimization

Privacy and Security

SQL Tip — Making Your Own Legos

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

Today we play with Lego, or rather we make our own Lego. No, not this way, but in SQL. One of the tremendous things about SQL and the Data Repository is that you can roll your own building blocks and use them over and over again, easily, saving yourself a lot of work down the road — and also making your reports more consistent.

Our goal is to make a SQL function that returns a "stacked" data item — one that reflects the hierarchy of the patient’s progress through the facility, to discharge, and then abstracting. In this case we’re looking at the Primary Diagnosis of the visit.

Simply put, what we know about the patient primary diagnosis (or complaint) advances through the following progression:

Visit Stage

Data Item

DR Field

Quality

ER Presentation

Complaint

EdmAcct_Main.StatedComplaint

Poor

Admitted

Reason for Visit

AdmVisits.ReasonForVisit

Better

Initial Abstraction

Reason for Visit

AbstractData.ReasonForVisit

Good

Final Coding

Primary Diagnosis

AbsDrgDiagnoses.DiagnosisName

Best

Our little chart makes it clear our understanding of the patient changes over time, and is refined as they progress through the care cycle. On our reports, we want to reflect that, and reflect it easily, in a consistent way.

Thus, a custom function! We’ll call it IatricPrimaryAdmitDiagnosis (which you will find attached to this tip). We call it from a query showing patient visits where we have access to the SourceID and VisitID fields, like so:

SELECT ADV.SourceID
,ADV.VisitID
,ADV.ServiceDateTime
,AdmitDX = zcus.dbo.IatricPrimaryAdmitDiagnosis( ADV.SourceID,ADV.VisitID )
FROM Livendb.dbo.AdmVisits ADV
WHERE ADV.ServiceDateTime BETWEEN GETDATE()-30 AND GETDATE()
;

Executing that code gets us a wide variety of results… let’s look at the ones from today:

2014-09-05 21:33:00

TOOTH PAIN

2014-09-05 21:35:00

APNEA

2014-09-05 21:40:00

COUGH,RUNNY NOSE

2014-09-05 21:45:00

EXTREMITY PROBLEM

Some of these patients are in the ED right now – and no one has had a chance to do more than capture the patient’s impression of their issue. If we’re lucky, a triage nurse is taking down this documentation. Now if we look at the visits from 30 days back — patients who have likely been discharged, abstracted, and final coded:

2014-08-06 22:51:00

FX DISTAL RADIUS NEC-CL [813.42]

2014-08-06 23:36:00

TOXIC EFFECT VENOM [989.5]

2014-08-07 00:46:00

ACUTE CHOLECYSTITIS

2014-08-07 03:31:00

FX FEMUR, MIDCERVIC-CLOS [820.02]

Now we have something more solid! Real ICD-9 diagnosis codes and names. Except for one poor soul who is still an Inpatient… When you review the code for the function, you’ll see it uses a COALESCE() (a standard SQL function that returns the first non-NULL value it finds in the list of parameters) to represent this hierarchy:

COALESCE( ADX.DiagnosisName+' ['+ADX.Diagnosis+']' -- Final Coded diagnosis #1
,ASD.ReasonForVisit -- Abstracted Reason for visit
,ADV.ReasonForVisit -- ADM.PAT.reason.for.visit (cleaned up from EDM complaint)
,EAM.StatedComplaint -- EDM Stated Complaint (pretty rough)
) AS AdmitDiagnosis

In this structure, we’ll get back the abstracted diagnosis first (if it exists), then the abstracted reason for visit (often filled in while the patient is still in-house), then the admitting reason for visit and finally the ED stated complaint if nothing else exists.

Warning!

This is a super-useful function for displaying reason for visit, on a report. But you should NOT try and use it as part of a WHERE clause to look for kinds of reasons of visit, or for primary diagnoses. Performance, in that scenario, will be quite poor.

Extra Credit

There are other data fields which might, depending on how your enterprise handles pre-abstracting documentation, have the admitting diagnosis as well:

AdmittingData.AdmitDiagnosis
AdmittingData.MedNecDiagnosis
AdmDischarge.ErDiagnosis

If these fields are populated in your MT environment – add them to your copy of the function!

(end)

Topics: Data Repository, SQL Tip, Making your own Legos, 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

0 COMMENTS