Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: one for the SQL experts - dare I say TRICKY SQL!

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

From: Erland Sommarskog <esquel_at_sommarskog.se>
Date: Tue, 7 Feb 2006 23:35:56 +0000 (UTC)
Message-ID: <Xns976461813922Yazorman@127.0.0.1>


 (paulspratley_at_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_at_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
Received on Tue Feb 07 2006 - 17:35:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US