>> One thing I thought about your code which might not work, is if you 
>> have a sequence of events stored in the table like this:
>>
>> EvenDateTime,EventType
>> 0,Turn on
>> 1,Turn off
>> 2,Overheat
>> 3,Turn on
>> 4,Turn off
>>
>> Then wouldn't your query pick up event 2 as being between an "on" and 
>> "off" event, but obviously the system is in the "off" state when it 
>> overheats.
> 
> Hmm, good point. I had assumed a sequence like that wouldn't appear.
> 
> I guess you'd need to find all the overhead events, and for each one, 
> perform a sub-select to determine the latest turn-on or turn-off event 
> preceeding it, and filter it out if it's a turn-off event.
> 
> I'll see if I can figure out the correct syntax for this. (Gail probably 
> knows in a heartbeat...)

OK, here we go. Using a sub-select, you can find the "previous" event in 
the log something like this:

   SELECT *
   FROM EventLog X, EventLog Y
   WHERE
     X.ExperimentID = Y.ExperimentID AND
     Y.Timestamp =
     (
       SELECT MAX(Timestamp)
       FROM EventLog Z
       WHERE
       Z.ExperimentID = X.ExperimentID AND
       Z.Timestamp < X.Timestamp
     )

In other words, for each row in X, perform a sub-query where you find 
all the rows belonging to the same experiment as X, with timestamps 
earlier than X, and then find the latest timestamp. Return that, and 
find the row in Y belonging to the same experiment, having the same 
timestamp.

If you *now* apply some filtering conditions and you're golden. 
Specifically, X must be an overheat event, and Y must be a power-on 
event (but Z is still unconstrained to be *any* event). If the previous 
event was a power-off event, Z will find it, and the "Y=power-on" 
condition will fail, so that row will be excluses. :-D

The trouble starts if there are many possible events, not just on, off 
and overheat. But all you really need to do is constrain Z to match only 
power-on and power-off events, but nothing else. Then Z (and hence Y) 
will end up being the most recent power-on or power-off, and you can put 
a condition on Y demanding that it be a power-on.

...if that makes *any* sense at all?

Post a reply to this message