scott wrote:

> OK so as an SQL beginner let me ask if this is possible.
> 
> Say you have lots of experiments, and each experiment is made up of 
> several events repeated some number of times (like over-temperature, 
> switched-off, switched-on etc).
> 
> I'm thinking that I make a table for events first, like:
> 
> EventID, Description
> 0, Switched on
> 1, Switched off
> 2, Over-temperature warning
> ...
> 
> Then I make another table for experiments
> 
> ExperimentID, StartDateTime, DeviceNumberTested, Description
> 0,blahblah,53535,"Test 1"
> 1,blahblah,23112,"Test 2"
> ...
> 
> You get the idea.
> 
> Then I should make another table to hold all the events that occur in 
> all the experiments.
> 
> ExperimentID,EventDateTime,EventID
> 0,blah,1
> 0,blah,0
> 0,blah,2
> 0,blah,1
> 1,blah,1
> ...
> 
> OK.  That all seems quite basic to me so far and seems the best way to 
> organise things.
> 
> Now, is it possible to do a search using SQL that does something like 
> this: Find all the times that an experiment has gone over temperature 
> while switched on (ie after a switched on event but not after a switched 
> off event)?  This is beyond my basic knowledge how I would do such a 
> thing without writing some code to manually search the results and 
> checking times.

Mmm, difficult to see, the Dark Side is...

I would suggest a 3-way self-join of the event table with itself. Find 
all occurrances of event #0, event #1 and event #2 in the same 
experiment, with constraints on the time orderings therein.

If that makes sense?

Post a reply to this message