C/S NPR Tip & DR/SQL Tip

This month we have a quick tip for NPR fans (Client/Server only) and a SQL /DR tip written by Thomas Harlan my DR Technical Team Lead. If only we had a RD tip, we’d have a trifecta.

C/S NPR Tip: Page 1 of N via MEDITECH utility program

We recently found out that MEDITECH has a utility you can call from the footnote of a report and print a Page X of Y message on your NPR reports. The utility is called:

%Z.rw.page.count(XPOS,YPOS,STRING) where

XPOS = KB article and program say this is X dot position @300DPI, but I think it is COL

YPOS = KB article and program say this is Y dot position, but I find it to be line of page header with first line =0

STRING is an option string to use for message, default is Page XX of YY, but you could send "Pg XX with exciting conclusion on YY" if you wanted a different message.

MEDITECH has a KB article on the utility: 47296. This utility is very handy, although it is not sufficient compensation for other sins of the Print Manager in C/S (no trays, no legal size paper, no print direction, no overlay macros, and so on).

Neither MAGIC nor RD has this feature. Years ago, we showed a way to do this for MAGIC that involved diverting print output to a file and doing a search/replace and then sending the data out to the printer. Back then (before print on VIEW), this worked even when spooling or downloading, but the technique needs updating for MAGIC to cope with spooling’s multiple SAFs (one per page to support VIEW). Stay tuned for an updated MAGIC report showing how to do Page X of Y including when spooling.

If you just stick a footnote on your report like this:

You'll get this on your page header:

DR/SQL Revamping ADM Patient Events into Patient Flow

One of the challenges in working with data structures developed for NPR (or M/AT) in the DR is that both NPR and M/AT retrieve data via a loop, while DR expects things to be arranged so they can be fetched in a set. A good example of this is AdmVisitEvents.

AdmVisitEvents has multiple kinds of data embedded within one table, including when the patient is bedded, and then moved from room to room, and then discharged.

Inevitably you’ll need to pull an inpatient location at a point in time and there won’t be a handy field with the Location/Room/Bed where the patient was when something occurred. And when you turn to AdmVisitEvents to find that situational information… you realize that the structure of the table is just not what you want.

The immediate way to get the patient Location is to embed a sub-query into your main SQL SELECT to fetch the patient Room:

SELECT TOP 1 AVE.LocationID

FROM Livedb.dbo.AdmVisitEvents AVE

WHERE RX.SourceID = AVE.SourceID

AND RX.VisitID = AVE.VisitID

AND AVE.UndoneSeqID IS NULL

AND AVE.EffectiveDateTime <= RXA.AdministrationDateTime

AND AVE.LocationID IS NOT NULL

ORDER BY AVE.EffectiveDateTime DESC, AVE.EventSeqID DESC

(In this snippet, we’re starting from PhaRx (RX) / PhaRxAdministrations RXA) to find the patient unit at the time of a medication administration.)

This answers the immediate question — where was the patient at the time of the administration. But it has issues… first, with a sub-query structured this way, we can only get one (1) field back (LocationID), and second, we have to create a loop in SQL (which SQL does not perform terribly well) with that ORDER BY and TOP 1 structure.

If we wanted to get LocationID, RoomID, and Bed in three separate fields – we would have to do three sub-queries, each returning one field. And as we add more sub-queries, the performance of our report begins to drop off precipitously!

What we want instead is a way to get at the AdmVisitEvents data so that we can JOIN to a table where each patient flow event (a stay in a location/room/bed) is in a single row, so we can do something like:

LEFT JOIN #tFlow ADT ON ( ADT.VisitID = RX.VisitID

AND ADT.SourceID = RX.SourceID

AND RXA.AdministrationDateTime >= ADT.FromDateTime

AND RXA.AdministrationDateTime <= ADT.ThruDateTime )

That is very clean! And now any field we need from #tFlow, we can reference in our query via the ADT alias — and since no sub-queries are involved, it’s fast.

The challenge now is creating this #tFlow table when we need it. And we only want to write that code once, in a reusable way. We need either a stored procedure or a table-valued function to go process AdmVisitEvents for us and create a nice flattened temporary table we can use.

You can download a copy of IatricPatientFlow.sql from iWeb and implement it on your DR server to get a pre-packaged function to do just this. That table-valued function works like so:

1. First we look for any patient in-house during the date range we are interested in.
2. Then we take that list of patient visits and fetch all of their Events into a temp table. When we do that, we only look for Events with a Location, that are not un-done, are not ‘FIXED’ records, and there are some kind of Event codes that are excluded.
3. We sweep that raw list of Events and look for events where there is a change of location or status, and we create yet another temp table that captures the start and end times of the patient being in a specific place.
4. Finally we create our nice summary table and address some data quirks where the last patient status isn't correct (unless we correct it… ) and extra discharge rows are discarded.

That process takes a set of Event data that looks like this:

And makes it look like this:

At any given point in time during the stay, we can then immediately find which Location, Room, Bed, and Status. We also get a summary of the time spent in the location.

To use this, in your reporting stored procedure (you are using a stored procedure for each report, aren’t you?) you create a little on-the-fly temp table:

SELECT ADT.*

INTO #tFlow

FROM zcus.dbo.IatricPatientFlow( 'IAT' -- Change to your site SourceID to test

,'IATRIC' -- Change to your facility ID to test

,'2014-01-01'

,'2014-01-31' ) ADT

;

And then use the JOIN above to bring that data into your query.

Don’t forget to clean up afterwards at the end of your stored procedure!

DROP TABLE #tFlow ;

Performance Note: The patient flow function runs at a test site, with 1.3 million rows in AdmVisitEvents, for a month of visits, in three seconds. It could have a bit better performance after being recast as a stored procedure using temp tables (rather than variables) inside IatricPatientFlow, but then it would require more setup and cleanup code, and could not be nested as deeply as a function.

Extra Credit! You can adapt the code to break out the patient by the day, calculating their stay in each location by day, which then makes calculating Patient Day Equivalent metrics for Nursing Quality Measures very easy.

The SQL code has been uploaded to our report library as IatricPatientFlow.txt.