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

[Gert-Jan Strik]

Tony Rogerson wrote:

Standard to which DBMS? Certainly not Oracle. I will submit passing
date strings without a proper format specifier is poor coding:

The ISO standard rather than vendor specific.

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
VALUES(1, convert(datetim e, '2006-02-07 02:03:00', 120),
convert(datetim e, '2006-02-07 02:07:00', 120))

You do not and would not code it like that in SQL Server, you would simply
write...

INSERT INTO Threads ( thread_id, start_stamp, stop_stamp )
VALUES(1, '2006-02-07T02:03:00', '2006-02-07T02:07:00') [snip]

Not sure which difference you mean.

The whole purpose of using the ISO 8601 format is that you don't need to
specify the converting mode. So I would omit it too.

But I would probably also specify the datetime as '2006-02-07 02:07:00'.
I still use SQL 7.0, and this format is upwards compatible. The format
'2006-02-07T02:07:00' is harder to read, and needs at least SQL Server
2000.

Gert-Jan

[Erland Sommarskog]

Gert-Jan Strik (so***@toomuchs pamalready.nl) writes:

But I would probably also specify the datetime as '2006-02-07 02:07:00'.
I still use SQL 7.0, and this format is upwards compatible. The format
'2006-02-07T02:07:00' is harder to read, and needs at least SQL Server
2000.

But '2006-02-07 02:07:00' is subject to different interpretations depending
on dateformat settings. For instance try:

SET LANGUAGE Dutch
go
SELECT convert(datetim e , '2006-02-07 02:07:00')
go
SET LANGUAGE Swedish
go
SELECT convert(datetim e , '2006-02-07 02:07:00')
go

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Gert-Jan Strik]

Erland Sommarskog wrote:

Gert-Jan Strik (so***@toomuchs pamalready.nl) writes:

But I would probably also specify the datetime as '2006-02-07 02:07:00'.
I still use SQL 7.0, and this format is upwards compatible. The format
'2006-02-07T02:07:00' is harder to read, and needs at least SQL Server
2000.

But '2006-02-07 02:07:00' is subject to different interpretations depending
on dateformat settings. For instance try:

SET LANGUAGE Dutch
go
SELECT convert(datetim e , '2006-02-07 02:07:00')
go
SET LANGUAGE Swedish
go
SELECT convert(datetim e , '2006-02-07 02:07:00')
go

You are right, I made a mistake. I would have written '20060207
02:07:00', which has no such side effects. So Tony was right: under
normal circumstances, no one would use "convert(dateti me, '2006-02-07
02:03:00', 120)" on SQL Server.

Gert-Jan