formula for lenght of time

[Quda]

I am trying to create a formula that will give me the lenght of time. Here is what I have
ID Seq_num Name Timestamp Tech
747905 1 Case is New 9/3/2003 23:44 1BKR
747905 2 Status Changed 9/3/2003 23:44 1BKR
747905 6 Status Changed 9/4/2003 3:10 8MBM
747918 1 Case is New 9/3/2003 23:34 9JXV
747918 2 Status Changed 9/3/2003 23:34 9JXV
747923 1 Case is New 9/3/2003 23:37 1HNC
747923 2 Status Changed 9/3/2003 23:37 1HNC
747923 5 Status Changed 9/5/2003 1:53 1HNC
747931 1 Case is New 9/4/2003 0:18 1HNC
747931 2 Status Changed 9/4/2003 0:18 1HNC
747931 5 Status Changed 9/4/2003 0:19 1HNC

And I want to group by ID, and be able to calculate the length of time from one detail to another. Like for ID:
747905 Tech 8MBM had the case for 3 hours and 26 min. Is there any way to use row num or anything like that? Help, and thank you.

 

[synapsevampire]

It sounds like you want the difference of the minimum time to the maximum time, not the difference from one detail to the next.

Group by ID and place a formula in the group header:

whileprintingrecords;
datetimevar Starttime := {table.timestamp}

In the Group Footer, use a different formula:
whileprintingrecords;
datetimevar Starttime;
Numbervar timediff: datediff("s",Starttime,{table.timestamp})

So this will show it in seconds, you can change the parm to "n" for minutes (check the help file).

I also have a FAQ which shows how to convert seconds to HH:MM:SS format in the Crystal Formulas forum, so you can add that code here if you'd like.

-k

 

[Quda]

I had thought about using the Min, and Max dates, but the problem would be if there was more then one tech. I would want a time for each.