Calculate TimePeriode Intervall

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)

Calculate TimePeriode Intervall

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-03-27 : 07:28:29

How do i take control of this query ?
I mean, im little confused how it really works.
What should i change if we want to, lets say take the average on every hours for instance?

(note that this shows 10 minutes value) but i cant resolve it
how to fix for example hours value or show the average of a periode over a mounth and so on.

How could i use this nice formula so it can be reused on differents intervals?


SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', [Date]) / 10 * 10, '19000101 00:10'),
AVG(1.0E0 * Val) AS Average
FROM @Sample
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', [Date]) / 10 * 10, '19000101 00:10'

Thanks

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-27 : 07:31:34

declare @dt datetime
set @dt=DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', [Date]) / 10 * 10, '19000101 00:10'),
then replace @dt,where ever u need it and see it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-27 : 07:47:30

why are you taking the time different in minutes with '00:02' ?


declare @Sample table
(
	[Date]	datetime,
	Val	int
)

insert into @Sample([Date], Val)
select	dateadd(minute, v.number, 0), abs(checksum(newid())) % 20
from	master..spt_values v
where	v.type = 'P'
and	v.number	between 0 and 200

declare	@interval	int

select	@interval	= 10	-- in minutes

SELECT 	DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [Date]) / @interval * @interval, 0),
	AVG(1.0 * Val) AS Average
FROM 	@Sample
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [Date]) / @interval * @interval, 0)

KH
[spoiler]Time is always against us[/spoiler]

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-03-27 : 07:53:05

Hi both of you!

@haroon2k9:
Unfortunate i cant still see it

@khtan
Excellent question.
This was the query i got from another thread, so i dont know for sure.
Only that i now this can "handle" that if you have irregular timestamps it still can caluclate the average for 10 minutes period.
Exactly what i needed than, but now i also need to modify it so it can be reused for different interval like for 1 hour, 1 week or perhaps a mounth interval.

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-03-27 : 08:05:00

@Khtan:

It seems to work now thanks!
But i test and see whats happening if i use irregular timestamps.
In this fine modify example u giving there have regular timestamps.

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-03-27 : 08:53:44

hello there

@Khtan:
Now i really understand it more clearly.
Why use of 00:02 is because it is the so call offset on which timestamplabel i will put the value on.
In this case above i use the first datetimestamp and calculate the sum of the values belows it.
But if i correct like this:

SELECT DATEADD(HOUR, DATEDIFF(HOUR, '1900-01-01 00:00:00', [Date]) / @interval * @interval,'1900-01-01 01:00:00'),
SUM(1.0 * Val) AS [Sum]
FROM @Sample
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, '1900-01-01 00:00:00', [Date]) / @interval * @interval, '1900-01-01 01:00:00')

Than we would put the timestamp 1 hour forward instead.

Thanks for helping me in the process i have full control of the situation now.
Exactly what i search for!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources