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

Why 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:
> > 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

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

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.

paulspratley@yahoo.co.uk wrote:
> 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.

Then the column name in your sample report is misleading:
Expected Results

Count of Users Avg Rsp Time
1 50
2 30
3 40