Posted by Erland Sommarskog on 02/08/06 01:35

(paulspratley@yahoo.co.uk) writes:
> 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.

It's very difficult to suggest a query, when the sample data does not
really match the description, and when there is not really any any
good description of the business problems.

In the sample data, the result for scriptid = 6 is from an occassion
when no thread was running, not even the thread that was said to be
running.

Why the two measurements for threadid = 1 should count for one user
is beyond me, as when the second measurement is record, there is another
thread.

At no occassion there are three threads running what I can see.

I composed this query, but it does not give the desired result.

SELECT cnt, avg(summeasurement)
FROM (SELECT cnt, threadid, summeasurement = sum(measurement)
FROM (SELECT r.measurement, r.threadid,
cnt = (SELECT COUNT(*)
FROM threadstart t
WHERE r.startstamp BETWEEN t.startstamp AND
coalesce(t.stopstamp,
'99991231'))
FROM result r) AS x
GROUP BY threadid, cnt) AS b
GROUP BY cnt
ORDER BY cnt


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]