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

[graham.parsons]

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_measure ment
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

[hpuxrac]

Why don't you include some sample data so that people here don't have
to do that part also?

[markc600]

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_measure ment 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_measu rement BETWEEN
t.start_time and t.end_time
GROUP BY t.unique_id

[Aaron Bertrand [SQL Server MVP]]

> 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

[hpuxrac]

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?"

[Aaron Bertrand [SQL Server MVP]]

> What exactly is a "SQL Server MVP?"

http://mvp.support.microsoft.com/mvpexecsum

http://tinyurl.com/79hu8

[paulspratley]

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

[Alexander Kuznetsov]

Please explain how an average responce time may be 50 when none of the
measurements exceed 40

[paulspratley]

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.

[Ed Prochak]

pa**********@ya hoo.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