Computing Committed Time by Hour-of-Day 1

[Rooski]

I am using Crystal 10 with an Oracle database for a law enforcement 9-1-1 report. The report needs to accumulate committed time of all officers for area by hour-of-day for a three month period. The chart below shows what the report should look like, except there will be 24 hours going across the report (only four are shown due to space limitations) and 16 areas down the report (only four are shown).

Hour of Day
00 01 02 03
Area
111 30.5 22.4 16.0 12.2
112 16.8 23.2 11.9 33.4
113 44.6 55.4 60.0 42.8
114 25.5 30.3 21.0 39.7

Committed time is the amount of time officers spend on a call for service (a 9-1-1 call) and begins with the time the officer is sent and ends with the time the officer clears the incident. Those start and end times are present in the database along with a field that holds the total amount of time the officer spent on the call for service (start time - end time).

Where things get difficult is properly allocating committed time by hour of day. Commonly, some calls will last for several hours. For instance, assume a call for service begins at 01:45 and ends at 03:45, a total of two hours. The time should be allocated as follows:
15 minutes in hour 01
60 minutes in hour 02
45 minutes in hour 03

I have a working Crosstab, but it looks only at the time the call for service starts and then puts the total committed time in that hour of day. For instance, using the above example, 2.0 hours of committed time would all go into the 01 hour of day column because that is when the call started.

I have a development background in COBOL and know I could do this with COBOL table processing. Do arrays in Crystal serve the same function and could they be used to solve this problem? Or is there some other way to handle it? I have searched the threads thoroughly, but couldn't find anything that seemed to apply to my situation. My Crystal background would best be described as intermediate.

Thanks in advance for any forthcoming help,

Rooski

 

[lbass]

I think you would have to create a manual crosstab where you used formulas (one per hour) like this, where the fields are datetimes:

//{@Hr00}
if {table.start} in datetime(date({table.start}),time(0,0,0)) to datetime(date({table.start}),time(0,59,59)) and
{table.end} in datetime(date({table.start}),time(0,0,0)) to datetime(date({table.start}),time(0,59,59)) then
{table.timespent} else

if {table.start} <= datetime(date({table.start}),time(0,0,0)) and
{table.end} > datetime(date({table.start}),time(0,59,59)) then 60 else

if {table.start} <= datetime(date({table.start}),time(0,0,0)) and
{table.end} < datetime(date({table.start}),time(1,0,0)) then datediff("h",datetime(date({table.start}),time(0,0,0)),{table.end}) else

if {table.start} >= datetime(date({table.start}),time(0,0,0)) and
{table.end} > datetime(date({table.start}),time(0,59,59)) then datediff("h",{table.start}, datetime(date({table.start}),time(1,0,0)))

Create one formula like this for each hour, and then insert sums at the group level (area) and suppress the detail section.

-LB

 

[Rooski]

Thank you for your very quick response. This looks like the breakthrough I needed. I'll let you know how it works after a test. Based on a walk-through of the 00 hour formula using the 01:45 - 03:45 example, I believe time is being added to the 00 hour formula when it shouldn't. But I believe I can figure out how to solve that. Your idea is very solid. I still have to get used to the idea of how formula-driven Crystal is and always think in those terms.

Thanks again,

Rooski