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: <paulspratley_at_yahoo.co.uk>
Date: 7 Feb 2006 07:41:34 -0800
Message-ID: <1139326894.333204.10470@g44g2000cwa.googlegroups.com>


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
Received on Tue Feb 07 2006 - 09:41:34 CST

Original text of this message

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