Results 1 to 8 of 8
  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 appreciated.
    Regards,

    Laurent

  2. #2
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76

    Re: Need help on SQL query

    Hi Laurent,

    A few questions to clarify your situation...

    -- which database platform are you querying against (MS SQL Server, Access, etc.?)

    -- in your agent state table, is the data only for one day and then cleared out? ...or is it a historical table and has a date field, or other field to differentiate if a particular batch of agent state records have been analyzed?

    -- do you (or can you) have some type of primary key (autonumber) in the table?

    -- how would you like the output? Just as it is below in your post in 4 fields? (NumMinutes, NotReadyTime, ReadyTime, Reason)

    4 minutes (between 10:28 and 10:42), for reason 10
    7 minutes (between 10:56 and 11:03), for reason 11

    -- is the time field stored in such a way to differentiate 9:00 am from 9:00 pm?

    -- what if you have the same reason code in the same day (or time period)? In the final output, do you want to rollup/aggregate multiple occurences of the same reason code, or have a separate row for each? Ex: if reason 10 happens in the same day...

    -- are you locked in for some reason to do this in a series of queries, or are you free to do this in code (ex: loop through Recordset object in ADO and use variables)?

    Anyway, I'll wait to come up with a suggestion until I know a little more about the circumstances b/c this can be done many ways depending on your environment.

    Kael MCSD, A+

  3. #3
    Join Date
    Feb 2004
    Posts
    7

    Reply to kaeldowdy

    Hy Kael,

    First, I would like to thank you for your quick answer.
    About your questions :
    1: I am using MS SQL Server
    2 : In fact the table is historical with the following date format : 2004-01-20 10:52:46.000
    3 : there is only one record for one datetime. So it can be considered as a primary Key
    4 : the outpout shoud be Duration(HH:mm:ss), Reason(int)
    5 : I do not want to agregate the row on the reason.
    6 : I need one query. I am using Sybase Infomaker and I can only use one query. But It can be complicated and not optimized. No Pb with select overlaped.

    Hope this will helps you.

    Laurent

  4. #4
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76

    Re: Reply to kaeldowdy

    Sure, no problem...

    Let me first say YIKES!!! You have to do it in just one SELECT query? That's a toughie! It's sure a lot simpler if you could do it in multiple steps/queries or in a stored procedure or in some code loop. But I guess you are tied in to the limitations of Infomaker, huh?

    OH! I just realized something...since you are hitting a SQL Server with your Sybase Infomaker (not familiar with that product btw), are you able to create stored procs. on the server? All the processing could be done in a loop or cursor and then only one recordset would be returned to Infomaker. I'm guessing you already thought of these types of things, but just wanted to ask. If it has to be a query b/c of how Infomaker likes it, then I'll have to think and tinker with it a bit longer...

    BTW: what does "No Pb with select overlaped." mean?

    Kael

  5. #5
    Join Date
    Feb 2004
    Posts
    7
    Kael,

    I am not very familiar with MS-SQL Server nor SQL queries.
    In Infomaker, I can only insert one request but it can be with several select in the main select (that's what i ment with overlaped select).
    like this :
    select
    data = sum(select GGG in XXXX where YYY)
    from
    TTT
    where
    TTT.skillgroup IN (select zzz from ddd where fff)
    ...

    If you think it's impossinble in one request, can you detail the procs I could store on the Server ?

    Thanks,
    Laurent

  6. #6
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76
    Hey...

    I got cha with the overlapping queries. They are usually called subqueries.

    Anyway, I was playing with it a little bit trying a few different things in one query, although it's actually a bunch of nested subqueries. I've started trying the "crosstab query" route and got pretty good results, but it's not quite there yet.

    Attached is an Access database where I'm tinkering around with it. I know in the end it'll be MS SQL Server syntax, but I have it in Access first to use the crosstab query builder capabilities since it's really fast to make them. If the final solution requires some type of Xtab, the syntax will have to be converted to something SQL Server likes.

    Check out in the attached database two queries (qryMain and qryXtabMain). The first query gets rid of everything except state=2 and reason is != 0 or state=3. That way you get rid majority of the records you don't want to look at. The second query is the crosstab which uses the first query (simulating a subquery for the time being). You'll see where the reason code = 2, the next record is the time the agent comes back online (code = 3). It's just that the time is in a different field in the next record. That's gonna be the challenging part to grab in one query, but I don't think it's impossible yet. I will look at the stored proc thing too (which is a lot simpler to do compared to doing it in one query).

    I'll look back at it in more depth tonight, but maybe the Xtab thing will spark a few ideas on your end...

    Kael
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2004
    Posts
    7
    Hy Kael,

    Thank you very much for your involment.
    I appriciate a lot.
    Someone gave me this query and it works !

    Select
    v.A, v.B, v.state, v.reason
    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;

    Regards,
    Laurent

  8. #8
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76
    Sure thing Laurent...

    I started looking at the st. proc. thing yesterday, but didn't get to finish it. I'll have to tell you, that is a pretty ingenious query. Especially with the "AND time > t.time" part...that's where I was wanting to go with my little crosstab thing and I figured in the end it would end up being a correlated subquery.

    Glad you got it to work...I'll remember this little trick!

    Kael

Posting Permissions

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