How would you calculate "bed days"?

[Former Member]

Hey everyone,

This is the first discussion I've ever posted in this forum, so if I am asking the wrong type of question, please just let me know! This is more of a methodology question than a "how-to" question.

I need to create a report that calculates "bed days", a metric typically used in hospitals and correctional facilities. In this case, the definition of a "bed day" is the following:

A patron who has stayed in one of our facilities for at least 6 hours of a 24-hour day.

Here is an example of the data that I need to use:

Patron #      In Date                       Out Date                   Facility

22

     22         11/19/2013 10:46         11/20/2013  9:24        Building 1

     22         11/20/2013  9:24          11/25/2013 16:34       Building 2

     22         11/25/2013 16:34         11/25/2013 16:34       Building 1

     22         11/25/2013 16:34         11/26/2013 18:23       Building 1

     22         11/26/2013 18:23         12/09/2013  7:44        Building 2

This is the housing record for fictional patron #22. The data is grouped by Patron #, and shows the patron's In Date (when he/she arrived), Out Date (when he/she left), and Facility (which building he/she was in).

What I need to do is look at each day stayed, individually (e.g. 11/19/2013, 11/20/2013, 11/21/2013), and figure out how many hours he/she stayed with us on each individual day. If he/she stayed at least 6 hours on any one day, that counts as a "bed day", and so one "bed day" is added to the bed day counter of whichever building he/she stayed at the longest for that day.

Essentially, we want a count of bed days for each Facility.

How would you go about this?

[DellSC · Accepted solution]

I don't think this would actually be all that difficult to create.  Here's the logic that I think it will use:

If In and Out are the same day AND they are 6 or more hours apart, return 1.

Else, if In and Out are the same day and they are less than 6 hours apart, return 0.

Else, get the date difference in days between In and Out. If In is less than 6 hours from the end of the day, subtract 1 day.  If Out is less than 6 hours from the beginning of the day, subtract 1 day.

It might look like this in Crystal:

WhilePrintingRecords;

NumberVar result := 0;

If Date(OutDate) = Date(InDate) then

(

  If DateDiff('h', InDate, OutDate) >= 6 then result := 1;

)

Else //InDate and OutDate are different days.

(

result := DateDiff('d', InDate, OutDate);

  If Date(DateAdd('h', 6, InDate)) > Date(InDate) then result := result - 1;

  If Date(DateAdd('h', -6, OutDate)) < Date(OutDate) then result := result - 1;

)

result

-Dell

[Former Member · Accepted solution]

Dell,

Thank you for your extensive answer. I appreciate you taking the time to work through this with me.

I think the logic you have arrived at is great. There are some difficult nuances to this problem, though. We need to account for the situation when Date(OutDate) = Next(Date(InDate)). For example:

Patron #      In Date                       Out Date                   Facility

22

     22         11/19/2013 10:46         11/20/2013  9:24        Building 1

     22         11/20/2013  9:24          11/25/2013 16:34       Building 2

The date of the first OutDate in this case = the date of the next InDate. For any given date, we can have at most 1 bed day. In this case, we are counting 1 bed day for 11/20/2013 in the first record and another 1 bed day for 11/20/2013 in the second record. So we are getting 2 bed days from 11/20/2013, which we can't do.

You are getting me thinking in the right direction, so thank you!

[DellSC · Accepted solution]

The problem you're going to run into is that you can't do a summary (to add all of the bed days) on a formula where you've used Next() or Previous().  You may, however, be able to use a running total to calculate the summary.  Just be aware that if you use running totals, you'll need to have one for each group level that you want the summary for - for example, if you need the bed days for each patient and then the total bed days for all patients at the end of the report, you'll have to have two running totals.  Also, unlike summaries, running totals don't have the correct value until you get to the end of whatever you're totaling.

-Dell

[Former Member · Accepted solution]

Dell and everyone interested,

I just want to give you an update on this problem. I have figured out a solution for calculating bed days: a stored procedure that I have created in our database management system.

The stored procedure uses a cursor to iterate through each record's date range (Indate to Outdate) and tallies up the minutes spent in facility for each individual date.

Once the data is reformatted by this stored procedure, I can then pull the reformatted data into Crystal Reports to make the report.

Thank you, Dell, and everyone who has spent time thinking about this problem!