SQL Tip: Using a Date Dimension Table to Calculate Patient Days by Location

A very useful tool to have in your Data Repository toolbox is a Date Dimension table — which is a physical database table with one row per date from, say, 1900 through the end of 2499, and key elements pre-calculated about that date.

This is useful for many data scenarios, for example:

• When you create a calendar, then report on things happening within a given day, and you need a row where nothing happens
• When you want to span out an event, like an ADT admit/discharge pair of datetimes, to include all of the days between those events

Having attributes of the day pre-calculated and stored in the dimension table also saves time coding, since you can fetch all sorts of elements in your query without having to calculate them on the fly:

So, that is very useful, and even better — you don’t have to find or create a script to create that structure, as it’s attached to this tip.

Then…

…You can use the date dimension table to calculate things like the number of patient days per unit for a range of time. In this scenario, we want to count up the number of visit records where the patient was in a given MEDITECH MIS location on a given day. There are two general ways to figure this out:

1. Process the Patient Events table in ADM or REG, and create a census snapshot or a patient flow table which has one row per visit, per day, per unit.

2. Use a pre-populated MEDITECH table like:

MAGIC   AbsAdmLocationsAndScus
CS         AbsSpecialCareUnits
MT61     AbsAcct_SpecialCareUnits

Which has visits by location, by a start/end date. Note! In C/S and MT61, the standard table already has a calculated field which stores the number of days in the unit for that visit. So, we don’t have to use the date dimension approach to figure that out, unless you’re on MAGIC, in which case the calculated field isn’t present OR you want to validate the calculated field(s).

If we’re doing it ourselves, then we want to LEFT JOIN from the MT standard table to the date dimension table in such a way that we get one row per day that the patient was in the unit:

Consider the LEFT JOIN to the date dimension table. In this case, we get each IDD.[DATE] that falls between the beginning of the stay in the unit and the end of the stay.

IDD.[DATE] is only the date component, however, and StartDateTime and EndDateTime both include HH:MM:SS. When we cast IDD.[DATE] into a comparable format, we get a time component added to it, but that defaults to 00:00:00, like so:

5/25/2018 > 5/25/2018 00:00:00

With this specific JOIN, if you arrived in the unit on 5/25/2018 at 10am, you would not appear, because 5/25/2018 at midnight is not within the time range of your stay. Similarly, if you departed at 1am on 5/29/2018, you would count as a patient on that day because the IDD.[DATE] value fell within the range.

This version of the query gets you a date dimension row (and thus a Patient Day) for each day at midnight. Generally, this will even out with the patient possibly shorted a day at the beginning of the range but picking it up at the end.

If, however, the first day is the end of the month, and you’re rolling up by month, then potentially you could short one month a patient day. In that case, you can revise the JOIN to include whole days at either end:

But this approach may get you an extra day, depending on how you want to handle partial day stays. Time to make a business logic decision!

Extra Credit

Update the Date Dimension creation code to add fiscal year components:

If you need more help…

Our Report Writing team can help with Data Repository reporting, NPR report writing, data extract or analysis, and much more. Simply reach out to your Iatric Systems Account Executive or our NPR report writing team at reportwriting@iatric.com to discuss how we can help support your team!