using group with time interval

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)

using group with time interval

Author

Topic

eflat
Starting Member

7 Posts

Posted - 2010-05-28 : 16:45:32

I found this example in an old thread, and I'm having trouble building a group statement around it. I'd like to see each rounded datetime value to appear once with its count next to it.


select
	Round_down_to_5_Minute =
	dateadd(mi,(datepart(mi,TIME_LOCATED)/5)*5,dateadd(hh,datediff(hh,0,TIME_LOCATED),0))
from
	(
	-- Test Date
	select TIME_LOCATED = convert(datetime,'20080305 12:33:32.997') union all
	select TIME_LOCATED = '20080305 12:35:32.997' union all
	select TIME_LOCATED = '20080305 12:35:35.000'
	) a

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-28 : 20:40:14

Is this what you mean?



select
	Round_down_to_5_Minute =
	dateadd(mi,(datepart(mi,TIME_LOCATED)/5)*5,dateadd(hh,datediff(hh,0,TIME_LOCATED),0))
,[Count] = count(*)
from
	(
	-- Test Date
	select TIME_LOCATED = convert(datetime,'20080305 12:33:32.997') union all
	select TIME_LOCATED = '20080305 12:35:32.997' union all
	select TIME_LOCATED = '20080305 12:35:35.000'
	) a

group by
dateadd(mi,(datepart(mi,TIME_LOCATED)/5)*5,dateadd(hh,datediff(hh,0,TIME_LOCATED),0))

Everyday I learn something that somebody else already knew

eflat
Starting Member

7 Posts

Posted - 2010-06-01 : 12:22:49

Exactly! Thanks!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-01 : 12:34:03

You're welcome!

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources