-- 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.
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.
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?
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 :
data = sum(select GGG in XXXX where YYY)
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 ?
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...
Thank you very much for your involment.
I appriciate a lot.
Someone gave me this query and it works !
v.A, v.B, v.state, v.reason
(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;
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!