I have a SQL Table which trace the agent states. Each time an agent change of state, a record is added.
I want to know how long an agent spent in a specific state.

Code of the main states :
0 : logged off
1 : logged on
2: Not Ready
3 : Ready

Column Reason is significant only when the agent start to be not ready (state = 2)

This give the following table :
Hour, State, Reason
10:25, 3, 0
10:28, 2, 10
10:42, 3, 0
10:44, 8, 0
10:45, 3, 0
10:56, 2, 11
10:57, 4, 0
11:01, 5, 0
11:02, 2, 0 (no reason here, because already in not ready state)
11:03, 3, 0

Which SQL Query should I create, to know the duration between the states 2 and 3 (the time spent in Not Ready state) ?

Here, I should have 2 durations :
4 minutes (between 10:28 and 10:42), for reason 10
7 minutes (between 10:56 and 11:03), for reason 11

Any help would be appriciated.

Regards,
Laurent

Select to_char(v.A, 'HH24:MI') As "T1",
to_char(v.B, 'HH24:MI') As "T2",
v.reason,
TRUNC(((v.B - v.A)*24*60),1) As "DIFF(Min)"
from
(select t.time As A, t.state, t.reason,
(Select min(time) from table where state = 3 AND time > t.time) As B
from table t
where t.state = 2 AND (NOT t.reason = 0)) v;

Thank you very much !

You are a genius !

Hope I could help you in the future.

Best Regards,

Laurent

Hello r123456,

Can I ask one more thing ?
How could I agregate on the column Reason ?
that is to say, How long an agent spent in Reason 10, 11, ...
"Group by" seems to be forbidden with subqueries.

Thanks,

Laurent