Formula For who is in a bed at a certain time

[mcallaghan]

My table has a start time and a end time. I need to find out a grand total of how many patients are in a bed at a certain hour. Paitient A can be in a bed for 1 hour and 11 while patient b can be in a bed for 2 hours at 11 and 12. The table is one row per record.

I need the results to be

11:00 hour we had 2 people in the room
12:00 hour we only had 1 person in the bed

If anyone has anything that can help thanks

 

[chelseatech]

Your data is in the wrong structure to do this. You have a status table when you really need a table of times and what is happening at each time.

Create an Hour table, and then select the records of everyone in bed in a subreport or with a Sub SELECT in a SQL command.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lbass]

You could potentially do this with running totals, if you insert a group on room, and then add one running total per hour. The rt should do a distinctcount of patientID, evaluate using a formula:

{table.start} <= time(11,0,0) and
{table.end} >= time(11,0,0)

Reset on change of group: room # (or reset never for a grand total).

This assumes you have an outer group on date or that you limit the report to a certain date.

-LB

 

[kenhamady]

>>The table is one row per record.

ALL tables are one row per record so you probably meant to say per patient. If it is something odd like one row per hour then that makes a big difference.

>>My table has a start time and a end time.
You don't say for sure, but in most cases a patient may have no end time meaning they are still in the bed. If that is possible you have to check for Nulls. If not then Lbass formula is fine.

>>how many patients are in a bed at a certain hour
This sounds like you only need ONE hour but your example shows two different hours as results. If you are trying to show multiple hour results on the same report then you will need a separate formula for each hour.

Here is what I would use for 1/25/2010 at 11am:

{table.start} <= Datetime(2010,1,25,11,0,0) and
(IsNull ({table.end})
or {table.end} >= Datetime(2010,1,25,11,0,0))
then 1
else 0

Then simply do a sum of this formula to see how many ones there are.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[chelseatech]

You still come back to the problem that you only have arrival and depart date/times for all your patients. If no-one arrives on one hourly block, then you can't count who is in bed at that time, and when some patients depart, you can't reset a running total for only some selected record values.

However, I do have a solution for you. I've built a simple COM driver to generate a set of records for a specific date/time range by hour/day/week/etc.

Use that as a main report and then fetch the data you want for each time record via a subreport.

COM driver is written and working nicely. I need to wrire some documentation and then it will be on my web site at

http://www.crystalkiwi.com

- look for cViewCALENDAR. If you need something sooner, email me and I can make a preliminary version available.

I'll write about this in more detail in the next issue of Crystal Clear.


Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[kenhamady]

chelseatech,

As long as you aren't trying to GROUP by hour you don't need someone to be in or out that hour. That is why I suggested a separate formula for each hour he needs to report on. A grand total of each hour formula should work just fine.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[lbass]

As would running totals as I suggested, although conditional formulas like Ken suggested are preferable.

-LB

 

[chelseatech]

If you are grouping by hour, for a day, then it's 24 running total fields for each column you want to calculate.

If you extend the date range to several days, a week or even a month, you increase the number of running totals required, so while running totals will work, its not a scaleable solution.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lbass]

Well, not necessarily. If you group on date, you would still only need 24 running totals. The OP didn't indicate the need to evaluate more than one column either.

I like Ken's solution because of the ease of working with conditional formulas (inserting sums). He also appropriately checked for people who were still IN bed (null check for end date).

-LB