|
|
Posted by Tony Rogerson on 10/01/24 11:39
> 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@earthlink.net> wrote in message
news:1139340784.592141.244420@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;
>
Navigation:
[Reply to this message]
|