Patient Re-admission Measure (count)

Dear DM Experts,

I am ponding this interesting scenario. What is the best way to resolve this requirement?

The simple is requirement is to track the patient re-admission rate in a hospital. For e.g., if patient A was admitted on 1st March 2009, 20th March 2009 and 12th May 2009. I will have 3 records in the fact table. To constitute a "re-admission" criteria, users can define a date range. Let's assume the users decide 30 days. Hence, between 1st March and 20th March, it is considered as 1 count of re-admission. But if the users decide to 60 days is more appropriate, it will be 2 count.

The key challenge here is that the users want to keep the data range "open" and not fixed. What would be the best way to tackle such unique requirement.

There are some BI tools that can dynamically do that, but it will mean crunching the numbers in the front end application and performance can be low. Is there a design that can elegantly store these numbers and yet fulfilled the requirement?

Suggestions and comments are welcomed... Cheers!

Are you not simply counting the number of admissions between a date range and subtracting 1? Unless I'm missing something, there shouldn't be any need to change your current data structures.

ngalemmo wrote:Are you not simply counting the number of admissions between a date range and subtracting 1? Unless I'm missing something, there shouldn't be any need to change your current data structures.

I think I am not being specific. Maybe the following example will help. Assume these admission records and this could be the fact table.

Record Patient Admission Date Interval (between previous visit)
===== ===== ============ =====
1 A 1-Mar-2009 0
2 A 20-Mar-2009 20
3 A 12-May-2009 53

What the users want to define as parameter is to input an interval of 20 days for a patient to be consider an re-admission. If that is the case, the above is consider as 1 count of re-admission (because record 1 and 2 are within 20 days, while 2 and 3 are more than 20 day apart). However, if the users input 60 days, then the above scenario will result in 2 count of re-admission.

I know that this is crazy, and there probably is no other ways to design this elegantly, but maybe I am wrong. Thing can get even nasty if users want re-admission rate. And that means 50% if interval is set at 20 days, but 100% if the interval is set at 60 days.

Seems like your design answers that question. What's the issue?

The interval in the fact table is not paticularly useful since it is only zero the first time someone is admitted, ever. If a year goes by and there are then 3 admissions in 2 months, you would not be able to tell looking at the raw value.

It is a two pass query. You need to identify the earliest admission within the dates of interest and then, for those admitted, locate other admissions in the time period.

select a.patient, count(*)
from admissions a,
(select patient, min(admitdate) earlyDate from admissions
where admitdate between #lowdate and #highdate) b
where a.patient = b.patient
and a.admitdate > b.earlydate
and a.admitdate between #lowdate and #highdate

ngalemmo wrote:The interval in the fact table is not paticularly useful since it is only zero the first time someone is admitted, ever. If a year goes by and there are then 3 admissions in 2 months, you would not be able to tell looking at the raw value.

Actually, I beg to differ about this interval field. It can be useful, as I can create "bands" (in some BI reporting tools). For example:
Interval 1 : 20 > interval >= 0
Interval 2 : 40 > interval >= 20
etc.

It can eliminate some processing time to compute the interval on the fly... anyway, just my thoughts...

It is useful if the interval is based on some date. If you were to load interval in the fact table and base it on days since the last admission, the interval is based on a moving target. So, the first admission has an interval value of 0. If the next admission is a year later, the interval would be 365. If the person is readmitted 20 days later, the interval is 385. How do you identify the readmission of interest if the intervals are 0, 365 and 385?

The way to do it is to track interval using a date dimension attribute, day count. You set it by numbering the dates from the earliest date in the dimension and incrementing it chronologically. But even with this, it is still a two pass query. You find the first admission of interest (f) and compare date counts with subsequent admissions (s). Your banding is based on s.daycount - f.daycount.