Time Spent Formula (Oracle 9i-HPUX) 1

[cathey04]

For the last two days, I have been searching for help on a formula for a field that collects activity time on an incident. The field is number and is not linked to another table. I need the data returned as dd:hh:mm:ss. In the apps manual, the data type is listed as duration, but when I look at the field in Crystal, it says number. I am using Table Views as opposed to Tables (I am not sure if this makes a difference).

The activity time is generated when an analyst updates an incident with the amount of time they spent trying to fix the problem. The application is CA's ServicePlus. The table is call_req and the field is time_spent_sum.

When I try using this one "CTime({call_req.time_spent_sum})" I get 0:00:00.

I am really new at this. Any help or suggestions would be great.

Thanks!

 

[lbass]

Did you notice the thread immediately preceding yours (thread767-832373)?

First, you need to know what the number in the field represents. If it is seconds, then the following (an adaptation of synapsevampire's FAQ) should work:

numberVar dur := {call_req.time_spent_sum};
numberVar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar ddhhmmss;

days := Truncate(dur/86400);
hrs := truncate(remainder(dur,86400)/3600);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

ddhhmmss := totext(days,"00")+ ":"+totext(hrs, "00") + ":" +
totext(min, "00") + ":" + totext(sec, "00");

ddhhmmss;

-LB

 

[cathey04]

Thanks for the formula! I noticed the previous thread after the fact. I will give it a shot and let you know if it worked!