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
GrahamWhy don't you include some sample data so that people here don't have
to do that part also?|||Some DDL and sample data would be useful. Here's an untested
shot in the dark...
CREATE TABLE THREADLIFECYCLE (unique_id INT,
start_time DATETIME NOT NULL,
end_time DATETIME)
CREATE TABLE MONITORRESULTS(unique_id INT,
time_of_measurement DATETIME NOT NULL,
value DECIMAL(10,2))
SELECT t.unique_id AS ThreadID,AVG(m.value) AS AverageValue
FROM MONITORRESULTS m
INNER JOIN THREADLIFECYCLE t ON m.time_of_measurement BETWEEN
t.start_time and t.end_time
GROUP BY t.unique_id|||> 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.
Please include DDL, sample data, and desired results.
http://www.aspfaq.com/5006|||Aaron Bertrand [SQL Server MVP] wrote:
> Please include DDL, sample data, and desired results.
> http://www.aspfaq.com/5006
OP already described desired results in the original post. DDL while
OK isn't hard to do.
As I already requested, yes sample data is something that most people
won't take the time to fudge up by themselves.
What exactly is a "SQL Server MVP?"|||> What exactly is a "SQL Server MVP?"
http://mvp.support.microsoft.com/mvpexecsum
http://tinyurl.com/79hu8|||just add:
count(m.value) as NumThreads
to the select list and I think you will be all set with the code below..
<markc600@.hotmail.com> wrote in message
news:1139318246.514858.196180@.o13g2000cwo.googlegroups.com...
> Some DDL and sample data would be useful. Here's an untested
> shot in the dark...
>
> CREATE TABLE THREADLIFECYCLE (unique_id INT,
> start_time DATETIME NOT NULL,
> end_time DATETIME)
> CREATE TABLE MONITORRESULTS(unique_id INT,
> time_of_measurement DATETIME NOT NULL,
> value DECIMAL(10,2))
>
> SELECT t.unique_id AS ThreadID,AVG(m.value) AS AverageValue
> FROM MONITORRESULTS m
> INNER JOIN THREADLIFECYCLE t ON m.time_of_measurement BETWEEN
> t.start_time and t.end_time
> GROUP BY t.unique_id
>|||Graham has asked me to post this on his behalf.
CREATE TABLE [dbo].[threadstart] (
[threadid] numeric(20,0) NOT NULL,
[startstamp] datetime NOT NULL,
[stopstamp] datetime NULL
)
INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])
VALUES(1, '2006-2-7 2:3:0.0', '2006-2-7 2:7:0.0')
GO
INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])
VALUES(2, '2006-2-7 2:4:0.0', '2006-2-7 2:5:0.0')
GO
INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])
VALUES(3, '2006-2-7 2:6:0.0', '2006-2-7 2:7:0.0')
GO
INSERT INTO [dbo].[threadstart]( [threadid], [startstamp], [stopstamp])
VALUES(4, '2006-2-7 2:8:0.0', '2006-2-7 2:10:0.0')
GO
CREATE TABLE [dbo].[result] (
[threadid] numeric(20,0) NOT NULL,
[scriptid] numeric(6,0) NOT NULL,
[startstamp] datetime NOT NULL,
[measurement] numeric(38,15) NOT NULL,
[errorcount] numeric(5,0) NOT NULL,
CONSTRAINT [PK_result] PRIMARY KEY([scriptid],[threadid])
)
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 1, 1, '2006-2-7 2:3:44.0', 10, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 1, 2, '2006-2-7 2:4:44.0', 10, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 2, 3, '2006-2-7 2:4:44.0', 20, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 2, 4, '2006-2-7 2:4:54.0', 20, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 3, 5, '2006-2-7 2:6:44.0', 30, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 3, 6, '2006-2-7 2:7:44.0', 30, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 4, 7, '2006-2-7 2:8:44.0', 40, 0)
GO
INSERT INTO [dbo].[result]([threadid],[scriptid], [startstamp],
[measurement], [errorcount])
VALUES( 4, 8, '2006-2-7 2:9:44.0', 40, 0)
GO
Expected Results
Count of Users Avg Rsp Time
1 50
2 30
3 40|||Please explain how an average responce time may be 50 when none of the
measurements exceed 40|||There are 2 occassions when the user count is 1 at the start of
Threadid 1 and when the last thread 4 is kicked off. Threadid has 2 *
10 (measurements) = 20 and Threadid 4 has 2 * 40 (measurements).
Therefore 100 measurements in total. Two threads therefore average =
50.