Summarizing elapsed time data from a multidimensional log file

[dirtdog]

I have a data set that logs multiple individual's activities in a sequential chronological order. Each individual has a number of activity codes with a date/time stamp attached. I need to summarize time spent in each activity and calculate the total time spent at each activity during any given time period.

The data would look something like this:

occ_date unit_id activity_code activity_time
2001-01-01 3412 OS 11:23:02
2001-01-01 10613 IS 11:23:03
2001-01-01 9734 IS 11:23:04
2001-01-01 3412 ER 11:23:05
2001-01-01 3412 ER 11:23:06
.
.
.
2001-01-01 3412 OS 11:27:00

And the desired output would be, for all unit types, over any given time period, would be:

Activity Time Summary

Unit Activity

OS IS ER
3412 00:00:03 00:00:00 00:03:55
9734 ... ... ...
10613 ... ... ...

So, I need to calculate an elapsed time for each unit_id number on any change in activity_code - summarized daily/hourly so that higher aggregations of information can be produced. I have been doing this in a spreadsheet, but the process is starting to get unweildy and am wondering if Crystal can handle summarizing a file like this in the fashion...

Any ideas?

Fraser Moffatt
Ottawa Police Service

http://www.ottawapolice.ca

 

[kenhamady]

You should be able to do this, but it will require some knowledge of Crystal Variables. Have you worked with CR variables? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[dirtdog]

Ken,

Yes, I've worked with variables - moreso in VB and not so much in CR, but I understand the concepts. I wouldn't call myself a programmer, but I can hack my way through an algorithm...

What have you got in mind??

Fraser

 

[kenhamady]

Is there a finite number of columns?
What version of CR?

The general idea is to group by unit and sort by activity time, and convert the activity time to a seconds value, I will use "secs".

Now you create a formula and variable for each column you need. Something like this for ER:

WhilePrintingRecords;
NumberVar AccumER;
If {code}="ER"
then AccumER := AccumER + {@secs}-Previous(@secs)

See the FAQ on running totals so that you can see how to Reset the variable on each Group Header and display it on each Group Footer.

You will also have to deal with the first record which doesn't have a previous value. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[dirtdog]

OK, now you've got me thinking!

I'm using CR8.

I only need to summarize on two columns (unit_id and activity_code)- both columns have multiple value possibilities as indicated in the first post. I would have to sort on activity time and calculate the elapsed time any time the activity_code changes for any give unit_id.

I guess I'll have to handle the first record in an if-then-else statement. Hopefully there'll be a way to identify the first record after sorting the

I'll take a closer look at running totals FAQ.

Thanks!