Blogs Home
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:

  1. 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.
  2. 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.
  3. 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:
                        ,[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)
                        BAT.TransactionType_BarTxnTypeID = 'CHG'
               ) -- Using a CTE to pre-calculate the ServiceDateTime field
        ,[AbsAcct_LocID] = AAS.SpecialCareUnit_MisSpecCareUnitID
        ,[BarAcct_LocID] = BTX.MisLocID
        ,[RegAcct_LocID] = RPL.MisLocID
FROM    cteChg CHG
        -- Using the ABS care unit table:
                      FROM    livefocdb.dbo.AbsAcct_SpecialCareUnits SCU
                      WHERE   SCU.SourceID = CHG.SourceID
                              SCU.VisitID = CHG.VisitID
                              CHG.ServiceDateTime BETWEEN SCU.SpecialCareUnitStartDateTime
                                                  AND     COALESCE(SCU.SpecialCareUnitEndDateTime,GETDATE())
                      ORDER BY SCU.SortOrder ASC
                    ) AAS
        -- Using the BAR Account Type History view:
                      FROM    zcus.dbo.IatricBarAcctTypeHxVw THX
                      WHERE   THX.SourceID = CHG.SourceID
                              THX.VisitID = CHG.VisitID
                              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:
                             ,REE.Location_MisLocID             AS MisLocID
                             ,REE.RegistrationType_MisRegTypeID AS MisRegTypeID
                      FROM   livefocdb.dbo.RegEvents_Events REE
                      WHERE  REE.VisitID = CHG.VisitID
                             REE.SourceID = CHG.SourceID
                             REE.Undone IS NULL
                             REE.NonMnrEvent IS NULL
                             REE.Location_MisLocID IS NOT NULL
                             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.


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 to discuss how we can help support your team!