Posted by paulspratley on 02/07/06 17:41

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

• Next in forum: Re: Why does SQL2005 automatically insert TOP 100 PERCENT in views?
• Prev in forum: Re: Views vs Stored Procedures, whats the difference?
• Thread view: Re: one for the SQL experts - dare I say TRICKY SQL!

[Reply to this message]