Calculating Number in Department

[asmith6]

Hello All,

Sorry, this is probably quite a novice question but I just can't figure it out. I work in an emergency department of a hospital and we are trying to write a report to pull back the number of patients in the department at the top of each hour i.e. a snapshot at 13:00:00, 14:00:00, 15:00:00 etc. This report will also include other hourly summaries but I've done these mostly as running totals based on a unique ID grouping of each individual hour of each individual day within the parameters e.g. 30/01/2014-13, 30/01/2014-14, 30/01/2014-15 etc. I'm happy for the snapshot to fall within these hourly periods.

For the number in department, the logic is fairly simple, we have a date/time stamp in the db for arrival and a date/time stamp for departure, if looking in retrospect patients in the department at a certain time are those with an arrival time less than the snapshot time and those with a departure greater than the snapshot time. Or if looking in real time those with a null departure time. The only way I can think of doing this is by creating multiple formula for each time category but obviously this isn't practical, does anyone have any suggestions?

 

[Charliy]

Are your times actual Time fields or are they character in the 24 hour format?

If it's the latter, then create a formula: left({time},2) and group on that. Then you can create comparison fields by appending ":00" and ":59" to that formula to see if the patient was there during that hour.