Count function within date/time

[Former Member]

Using Crystal Reports X, I need to calculate how many people are in a building at any given hour over rolling 24 hour periods. Each person entering is recorded by dd/mm/yyyy hh:mm:ss and the same method is used when they leave thus each person has a time span in the building. I know the date/time of leaving is critical to this in terms of whether it's populated or is null but I'm getting caught on the calculation in between. The two date/time fields should allow their presence in the building to be recorded and counted each hour. The critical point will come at midnight where the DateDiff function will need to apply. Does anyone have a solution that doesn't involve multiple formulae or stored procedures?

[Former Member · Accepted solution]

you will need to create a datediff formula between start time and end time.

then most likely calculate the hours by divding or multiply by 60 (forget-i havent done report like this in awhile)

then group by the hour

then group the people by the hour

then create manual running totals to calc the number of people by hour

RESET

The reset formula is placed in a group header report header to reset the summary to zero for each unique record it groups by.

whileprintingrecords;

Numbervar X := 0;

CALCULATION

The calculation is placed adjacent to the field or formula that is being calculated.

(if there are duplicate values; create a group on the field that is being calculated on. If there are not duplicate records, the detail section is used.

whileprintingrecords;

Numbervar X := x + ; ( or formula)

DISPLAY

The display is the sum of what is being calculated. This is placed in a group, page or report footer. (gererally placed in the group footer of the group header where the reset is placed.)

whileprintingrecords;

Numbervar X;

X

[Former Member · Accepted solution]

Assuming your base data is some sort of log, with one record for an entry and one for an exit, this is really quite simple. No DateDiff needed!

Pull all of the logs for the time frame that you want to report, then keep track of the ins and outs with a formula something like (basic syntax):

global inBuilding as number

if {table.action} = "IN" then
  inBuilding = inBuilding + 1
else
  inBuilding = inBuilding - 1  ' assuming action is only IN or OUT
end if
formula = ''

Place this on the detail format. Group your records by hour, and on the group footer, place this:

global inBuilding as number
formula = inBuilding

Which will show the number of people in the building at the end of that hour.

There are two issues with this, both of which can be gotten around:

1) The number of people in the building at the start of the reporting period is not taken into account. For that, you might want to create a summary table by hour (a datetime; or at midnight by date) and the number of people in the building, then add that into your data. (Or you could go through all of the records from the beginning of time, but in a few years, that'll be really, really slow!)

2) It does not necessarily show every hour of every day (if there are no log entries for the hour). In order to do that, you'd need to have a "master calendar" type table that does have every hour of every day (don't forget about Daylight Savings Time!) and use that on the left side of an Left outer Join to your other data.

HTH,

Carl