>> 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.

Mmm, yum. :-S

> 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...)

> 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.

Yeah, probably not such a hot idea to denormalise the data like that...

Post a reply to this message