SQL Tip — Making Your Own Legos

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:

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:

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:

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)