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

> INSERT INTO Threads VALUES(1, '2006-02-07 02:03:00', '2006-02-07
> 02:07:00');

This is very dangerous code, its worse than SELECT * and relies columns being in order which we know in a set is just not the case.

ALWAYS specify the columns on your INSERT...

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp ) VALUES(1, '2006-02-07 02:03:00', '2006-02-07 02:07:00')

Also, use standard formatting for the dates - '2006-02-07T02:07:00'

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

Stop using that outdated column syntax nobody except oldbies unwilling to change use.

SELECT M.meas_stamp, COUNT(T.thread_id) AS active_tally, SUM(M.meas_value) AS meas_tot
FROM Threads AS T

    CROSS JOIN Measurements AS M
WHERE M.meas_stamp BETWEEN T.start_stamp AND T.stop_stamp GROUP BY M.meas_stamp;

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212_at_earthlink.net> wrote in message 
news:1139340784.592141.244420_at_o13g2000cwo.googlegroups.com...


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

>