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

If you had posted DDL, would it look like this?

Since thread_id might actually be a key instead of a non-relational physical sequence number.

CREATE TABLE Threads
(thread_id INTEGER NOT NULL PRIMARY KEY,
 start_stamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,  stop_stamp DATETIME NULL,
CHECK(start_stamp < stop_stamp));

INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07 02:07:00');
INSERT INTO Threads VALUES(2, '2006-02-07 02:04:00', '2006-02-07 02:05:00');
INSERT INTO Threads VALUES(3, '2006-02-07 02:06:00', '2006-02-07 02:07:00');
INSERT INTO Threads VALUES(4, '2006-02-07 02:08:00', '2006-02-07 02:10:00');

The measurements clearly have a key in their time stamp.

CREATE TABLE Measurements
(meas_stamp DATETIME NOT NULL PRIMARY KEY,
 meas_value DECIMAL (5,2) NOT NULL);

INSERT INTO Measurements VALUES('2006-02-07 02:03:44', 10.0);
INSERT INTO Measurements VALUES('2006-02-07 02:04:44', 10.0);
INSERT INTO Measurements VALUES('2006-02-07 02:04:45', 20.0);
INSERT INTO Measurements VALUES('2006-02-07 02:04:54', 20.0);
INSERT INTO Measurements VALUES('2006-02-07 02:06:44', 30.0);
INSERT INTO Measurements VALUES('2006-02-07 02:07:44', 30.0);
INSERT INTO Measurements VALUES('2006-02-07 02:08:44', 40.0);
INSERT INTO Measurements VALUES('2006-02-07 02:09:44', 40.0);

Now you can use a between preidcate to place each measurement inside an on-going event.

CREATE VIEW Summary (meas_stamp, active, meas_tot) AS
SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally, SUM(M.meas_value) AS meas_tot
  FROM Threads AS T, Measurements AS M
 WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp  GROUP BY M.meas_stamp;

meas_stamp                  active_tally   meas_tot
=================================
2006-02-07 02:03:44.000	1	10.00
2006-02-07 02:04:44.000	2	20.00
2006-02-07 02:04:45.000	2	40.00
2006-02-07 02:04:54.000	2	40.00
2006-02-07 02:06:44.000	2	60.00
2006-02-07 02:08:44.000	1	40.00
2006-02-07 02:09:44.000	1	40.00

Put this VIEW (or derived table or CTE) into a another query:

 SELECT active_tally, SUM(meas_tot / COUNT(*)) AS meas_avg    FROM Summary
GROUP BY active_tally; Received on Tue Feb 07 2006 - 13:33:04 CST