Wednesday, December 30, 2020 1:00 PM

T-SQL + MEDITECH DR – Patient Location at a Point in Time

Written by Thomas Harlan, Reporting Services Team - iatricSystems

Patient Location - Blog Header

There is lots of data stored in MEDITECH data, but invariably you are going to encounter that one bit of data that you need, that is not helpfully stored in the tables you are using.

And one of the biggest missing pieces of the data puzzle is the patient’s location at an instant in time. You might need that from a BAR transaction, or PCS\NUR documentation, or anywhere…

And that information is not on the BAR txn record, which is a great sadness. (Cue… “MEDITECH, why do you make us cry so?” on TikTok)

Then, to make things more interesting, there are (for example, in MT 6.1x) three different places that track the patient’s location throughout their stay:

In REG Events
In ABS Special Care Units
In BAR Account Type History

Here is how that works:

As the patient is admitted and moves from unit to unit and room to room and eventually to discharge, REG events are captured and stored in the RegEvents_Events table.
That data then flows into ABS Special Care Units – if sufficient parameters are set to allow the capture of that location data. Sometimes you only get entries for the patients’ time in ICU.
And another copy of that data flows into BAR Account Type History.

But then things get more complicated.

First off, the quality of this data is entirely dependent on how rigorous Registration, Nursing and Transport are about changing the patient’s location in MEDITECH as they move from place to place.

Sometimes you might get a very clear picture of the patient’s journey through your care system – and other times you might not. Commonly, if the patient is in med-surg, and they go to an imaging appointment for a couple hours, the nurses will not change their location in REG.

It is extra work and they may not have time to do that… but that is sad, sad for you, because then you are missing part of the picture and you cannot do everything you might want to do with the data. For example:

You cannot do any infection tracing if the patient (or a provider) is later found positive for an infectious disease
You cannot allocate supply charges based on the areas the patient was in at a specific time
You cannot calculate a daily unit census due to patients moving in and out during the day
And so on…

However, you can still get good use out of the data, but you need to consider the different ways each module edits their copy of the data.

The version of the data that flows into ABS may be, as we note above, incomplete. This data may also be edited in ABS, and those changes do not flow back into REG.

Similarly, the data that flows into BAR in 6.1x, may then be changed in BAR if the account changes its type. Specifically, one day the Patient Class may be INO, then – after the 3-day rule kicks in – it may become IN. For BAR, however, the Location usually does not change.

So consider those scenarios when you are using the data. To help you see how that plays out, here is the location-in-time code, showing all three modules versions of the data:

-- Now let's use three different ways to find the patient location at that charge instant:

WITH cteChg AS ( SELECT BAT.SourceID

,BAT.VisitID

,BAT.TransactionNumberID

,BAT.TransactionBatchDate

,BAT.TransactionServiceDate

,BAT.TransactionServiceTime

,BAT.TransactionChargeCode_BarCdmID

,[ServiceDateTime] = TRY_CONVERT(DATETIME,

TRY_CONVERT(VARCHAR(10),BAT.TransactionServiceDate,120)+' '+COALESCE(STUFF(BAT.TransactionServiceTime,3,0,':'),'00:00')+':00'

,120) -- *** No TRY_CONVERT() in your SQL? Change to just CONVERT() instead

FROM livefocdb.dbo.BarAcct_Txns BAT

WHERE BAT.TransactionBatchDate = CAST(CAST( DATEADD(day,-1,GETDATE()) AS DATE) AS DATETIME)

AND

BAT.TransactionType_BarTxnTypeID = 'CHG'

) -- Using a CTE to pre-calculate the ServiceDateTime field

SELECT CHG.*

,[AbsAcct_LocID] = AAS.SpecialCareUnit_MisSpecCareUnitID

,[BarAcct_LocID] = BTX.MisLocID

,[RegAcct_LocID] = RPL.MisLocID

FROM cteChg CHG

-- Using the ABS care unit table:

OUTER APPLY ( SELECT TOP 1 SCU.*

FROM livefocdb.dbo.AbsAcct_SpecialCareUnits SCU

WHERE SCU.SourceID = CHG.SourceID

AND

SCU.VisitID = CHG.VisitID

AND

CHG.ServiceDateTime BETWEEN SCU.SpecialCareUnitStartDateTime

AND COALESCE(SCU.SpecialCareUnitEndDateTime,GETDATE())

ORDER BY SCU.SortOrder ASC

) AAS

-- Using the BAR Account Type History view:

OUTER APPLY ( SELECT TOP 1 THX.*

FROM zcus.dbo.IatricBarAcctTypeHxVw THX

WHERE THX.SourceID = CHG.SourceID

AND

THX.VisitID = CHG.VisitID

AND

CHG.ServiceDateTime BETWEEN THX.EffectiveFromDateTime

AND COALESCE(THX.EffectiveThruDateTime,GETDATE())

ORDER BY THX.EffectiveFromDateTime ASC

) BTX -- Find the row just before (or equal) to the time of service

-- Using the REG Events table:

OUTER APPLY ( SELECT TOP 1

REE.SourceID

,REE.Location_MisLocID AS MisLocID

,REE.RegistrationType_MisRegTypeID AS MisRegTypeID

FROM livefocdb.dbo.RegEvents_Events REE

WHERE REE.VisitID = CHG.VisitID

AND

REE.SourceID = CHG.SourceID

AND

REE.Undone IS NULL

AND

REE.NonMnrEvent IS NULL

AND

REE.Location_MisLocID IS NOT NULL

AND

REE.EffectiveDateTime <= CHG.ServiceDateTime

ORDER BY REE.EffectiveDateTime DESC, REE.DateID DESC, REE.SequenceNumberID DESC

) RPL-- Get the row from just before the Service Date Time

;

Using a day’s worth of charges, and the service date/time stamped on the CHG record, we look up the patient location in each of the three ways. Run this yourself and then you can drill-down into what happened in each module to give you variant results, if you see any.

Oh, what about that IatricBarAcctTypeHxVw VIEW we are using? You will need the [ source code ] for that…

Happy Holidays!

Other Platforms!

Equivalent code can be written in Magic and C/S DR though on those platforms you only have:

REG Events (CS = AdmVisitEvents, Magic = AdmVisitEvents)
ABS Special Care Units (CS = AbsSpecialCareUnits, Magic = AbsAdmLocationsAndScus )

And the same caveats about data quality apply…

Extra Credit

It’s easy enough to OUTER APPLY to these other structures, as we showed above, and get the patient location at the moment you need. However, you may be tempted to try the same approach by creating a FUNCTION to do the location lookup.

Try that. And then compare the performance of the SELECT with the OUTER APPLY(s) vs. the FUNCTION.

And…

As ever, if you need help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!