Calculate agent activity for eventtype with datetime field.

[javedi]

Hi

I have one table with three fields required from it.

agent (number) - Identifier of the agent whose state has changed.
eventdatetime (datetime) - Date and time that the agent state changed.
eventtype (number) - Eventtype shows events that triggered the agent state change:
1—Log In
2—Not Ready
3—Ready
4—Reserved
5—Talking
6—Work
7—Log Out

How can I calculate activity for each agent as in event type to show in HH:MM:SS how long they spent in each state?

Agent - log in - Not ready - Ready - etc..
Smith - 12/12/2011 12:45 - 1:14:24 2:34:11

Any help would be appreciated.

Using CR v11.5

Javedi

 

[Madawc]

Group by agent and order by eventtype. Add a running total that is cleared by a change of group (agent). (If you're not already familiar with Crystal's automated totals, see FAQ767-6524.)

If there will only ever be one occurance of an eventtype, it is quite easy. Create a formula field that ignores the first record and then use DateDiff to find the difference from the previous record. Something like:

If @RecordCount = 1 then 0
else DateDiff ("n", Previous{eventdatetime },  {eventdatetime })

This will give you the difference in minutes. For hours and minutes, do

ToText((FindDiff/60), "00") & ":" & ToText(Remainder(@FindDiff, 60), "00")

Even easier if it always begins with Log in, just don't look for a date difference for type 1.

If there are multiple occurences it gets harder, you might need to group by type within agent.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP