>>> SELECT *
>>> FROM EventRecord AS X, EventRecord AS Y, EventRecord AS Z
>>> WHERE
>>>   X.ExperimentID = Y.ExperimentID AND
>>>   Y.ExperimentID = Z.ExperimentID AND
>>>   X.EventID = 0 AND
>>>   Y.EventID = 2 AND
>>>   Z.EventID = 1 AND
>>>   X.EventDateTime < Y.EventDateTime AND
>>>   Y.EventDateTime < Z.EventDateTime;
>>>
>>> Or similar. (It's been a while since I did SQL...)
>>
>> Oh cool that helps a lot, I'm copying and pasting that to a file 
>> somewhere for reference, thanks.  I still need to order myself an SQL 
>> book...
>
> Out of curiosity... does this work?

Hehe haven't got anywhere near needing to implement that algorithm yet, I'm 
still working on parsing text files to populate the database automatically 
(lots of RegExs).  But I am getting the hang of how the SQL stuff works now.

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.

Maybe I need to store the state of the equipment (on/off) in each row to 
make things easier, but there are more states than just on/off (more 
speicifcally, there are several different sub-parts than can be on/off) so 
hmmmmmm.

Post a reply to this message