average time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

average time

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-07-05 : 02:47:31

Hi

I have a table which hold information on uploaded files, the table have a datetime value that I would like to use to get information on how often files where uploaded in average under 2 dates. For example the result should be ..

"There where files uploaded and inserted into the database every 15 seconds"


CREATE TABLE [dbo].[MyFiles](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FileName] [nvarchar](255) NULL,
	[DateAdded] [datetime] NULL

Can someone show how this can be done?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-05 : 03:16:44

select avg(datediff(second,0,t2.DateAdded - t1.DateAdded)) as AVGelapsedSeconds
from
(
select
row_number() over (order by id asc) as seq,*
from MyFiles
)t1
join
(
select
row_number() over (order by id asc) as seq,*
from MyFiles
)t2
on t2.seq = t1.seq+1

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-05 : 03:28:19

This?

DECLARE	@FromDate DATETIME = '20110101 15:14:56',
	@ToDate DATETIME = '20110630 03:17:22'

SELECT	CASE COUNT(*)
		WHEN NULL THEN 'There where no files uploaded and inserted into the database'
		ELSE 'There where files uploaded and inserted into the database every ' + LTRIM(STR(1E * DATEDIFF(SECOND, @FromDate, @ToDate) / COUNT(*), 10, 1)) + 'seconds'
FROM	dbo.MyFiles
WHERE	DateAdded >= @FromDate
	AND DateAdded < @ToDate

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-05 : 03:31:55

Your nick should be MasterPeso

No, you're never too old to Yak'n'Roll if you're too young to die.

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-07-05 : 03:40:07

Hi

Thanks but it does not seem to be correct, try insert theese values..


INSERT INTO MyFiles (DateAdded) VALUES ('2011-07-03 23:55:56.790')
INSERT INTO MyFiles (DateAdded) VALUES ('2011-07-03 23:56:05.383')
INSERT INTO MyFiles (DateAdded) VALUES ('2011-07-03 23:56:55.133')
INSERT INTO MyFiles (DateAdded) VALUES ('2011-07-03 23:59:59.743')

If I run that query I get the result "80" which isn't correct.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-05 : 04:17:01

80 IS correct

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-05 : 04:17:51

A slight tweak, maybe?

SELECT	*
FROM	MyFiles

DECLARE	@FromDate DATETIME = '20110701 15:14:56',
	@ToDate DATETIME = '20110730 03:17:22'

-- Solution starts here
SELECT	CASE COUNT(*)
		WHEN 0 THEN 'There where no files uploaded and inserted into the database'
		ELSE 'There where files uploaded and inserted into the database every ' + LTRIM(STR(1E * DATEDIFF(SECOND, MIN(DateAdded), MAX(DateAdded)) / COUNT(*), 10, 1)) + ' seconds'
	END
FROM	dbo.MyFiles
WHERE	DateAdded >= @FromDate
	AND DateAdded < @ToDate

N 56°04'39.26"
E 12°55'05.63"

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-07-05 : 04:37:19

Hi Peso

When I run that query I get this error..

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable "@FromDate".

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-07-05 : 06:40:35

Got it now, Thanks Peso!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-05 : 06:58:10

quote:
Originally posted by webfred

Your nick should be MasterPeso

My Midi-chlorians doesn't allow that.

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-05 : 07:48:02

quote:
Originally posted by SwePeso

quote:
Originally posted by webfred

Your nick should be MasterPeso

My Midi-chlorians doesn't allow that.

N 56°04'39.26"
E 12°55'05.63"

Haha, Jedi Peso. That's not bad

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources