Elapsed time using value from only one field 1

[RachaelD]

I have a date/time field "Timestamp" that is a date/time an agent changes an Event Type (values are login, ready, not ready, logout).

I group on the Agent Name, then on @Date - which is a formula datetimetodate({eAgentLoginStat.Timestamp}). Then I group on time - datetimetotime({eAgentLoginStat.Timestamp}).

I need an elapsed time between each Event Type. So, if an agent has an Event Type of Login at 8:03:12AM and then a Ready Event Type at 8:04:25AM, I want to show that the Login elapsed time was 0:01:13.

The date/time is only one field, Timestamp.

Thanks very much in advance.

 

[lbass]

You should be able to use a formula like this:

datediff("s",{eAgentLoginStat.Timestamp}, next({eAgentLoginStat.Timestamp}))

...assuming you are sorting in ascending order by datetime. Then you can format the seconds into the string format by using the formula in faq767-3543.

-LB

 

[RachaelD]

Thank you VERY MUCH lbass. That did the trick.

 

[RachaelD]

There seems to be a small problem. I used the formula above for the elapsed time in seconds, but it's giving a negative number for some of the breaks. Here is an example:

Timestamp Event Type Elapsed Time Formatted Time
8:29:06am Not Ready 327 00:05:27
8:34:33am Ready -83,711 Less than 0
9:19:22am Not Ready 505 00:08:25
9:27:47am Ready 70 00:01:10
9:28:57am Not Ready 86,875 24:07:55

Any ideas?

 

[lbass]

Please post the exact formula you used. Can you also verify that you grouped first by {@date} and then by {@time}?

-LB

 

[RachaelD]

The first group is @fullname: {eAgentLoginStat.AgentGivenName} + " " + {eAgentLoginStat.AgentSurName}

The second group is @date:
datetimetodate({eAgentLoginStat.Timestamp})

The third group is @time:
datetimetotime({eAgentLoginStat.Timestamp})

I used your formula for @elapsedtime:
datediff("s",{eAgentLoginStat.Timestamp}, next({eAgentLoginStat.Timestamp}))

And that formula shows a negative value. I then used a formula I commonly use for formatting the elapsed time called @elapsedtimeformatted:

WhilePrintingRecords;
StringVar Hours1;
StringVar Minutes1;
StringVar Seconds1;
NumberVar talktime;

talktime:={@Elapsed Time};

If talktime < 0 Then
"Cannot have a time less than zero"

Else
(Hours1:=ToText(Truncate(talktime/3600),0);
Minutes1:=ToText(Truncate(Remainder(talktime,3600)/60),0);
Seconds1:=ToText(Remainder(Remainder(talktime,3600),60),0);

//Display the time formated.
(if length(Hours1) < 2 then '0') + Hours1 + ":" +

 

[lbass]

In the sample where you showed the negative numbers, are there suppressed group headers? Try changing the formula to this:

if {@fullname} = next({@fullname}) then
datediff("s",datediff("s",{eAgentLoginStat.Timestamp}, next({eAgentLoginStat.Timestamp}))

-LB

 

[RachaelD]

No suppressed group headers.

I changed the @elapsedtime formula to what you said above, and when I checked the formula I got the error "Not enough arguments have been given to this function."

I changed the @elapsedtime formula to:
if {@Full Name} = next({@Full Name}) then
datediff("s",{eAgentLoginStat.Timestamp}, next({eAgentLoginStat.Timestamp}))

I got the same negative numbers.

They look like they might be correct if they weren't negative. Is there a way to call every number a positive, even if it computes a negative? Would that work?

 

[RachaelD]

I figured it out. In messing with the groupings, the default for @date went to look at each week, instead of each day.

It's working well now. Thanks so much for all your help!