Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: one for the SQL experts - dare I say TRICKY SQL!
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 40Received on Tue Feb 07 2006 - 09:41:34 CST