Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Question Unanswered: Need help on SQL query

    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

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Posts
    7

    Thumbs up reply to r123456

    Thank you very much !

    You are a genius !

    Hope I could help you in the future.

    Best Regards,

    Laurent

  4. #4
    Join Date
    Feb 2004
    Posts
    7

    One more thing

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •