one for the SQL experts - dare I say TRICKY SQL!

Guys,

Hopefully someone can help.

We have a monitoring program that has threads which start and stop monitoring at various times. There are two tables:

THREADLIFECYCLE unique_id
start_time (always populated)
end_time (not populated until the thread ends)

MONITORRESULTS unique_id
time_of_measurement
value

What I am trying to do is find the average value for each of the numbers of running threads. To explain further, threads will start, stop independently and overlap each other.

I want an output that says:

When 1 thread was running: average value was x When 3 threads were running: average value was y

Due to the start and stop nature there could be 1 thread running at the beginning of the test, mid way through, a number of occassions, etc.

Also, the number of threads does not necessarily ramp sequantially - the number running at any time could be like this sequence: 1, 5, 10, 7, 12, 4, 2

ANY help would be much appreciated - it really has stumped me but looks like it should be so simple .... But aren't they always the hard ones ;-(

Thanks

Graham Received on Tue Feb 07 2006 - 06:31:09 CST