Time Query - 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 2008 Forums

Transact-SQL (2008)

Time Query

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-06-24 : 13:06:55

I dont even know how to get started on this query. I have a column of datetime values. I need to return the Top time for every 30 minute interval for a full 24 hours. So, for example, there may be 245 records that fall between 10am - 10:30am. I need the TOP time in that time frame, which may be 2014-06-23 10:30:59.557 or it may be 2014-06-23 10:10:25.557. I know how to get the Top Time. I don't know how to return the one record I need for every 30 minutes. I cannot even wrap my head around how to start and what is the most efficient. Thanks!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-24 : 13:20:04

Group by half hour intervals - as in the example below:

SELECT
	DATEADD(mi,DATEDIFF(mi,0,YourTimeColumn)/30*30,0) AS HalfHourStart,
	MAX(YourTimeColumn) AS MaxTime
FROM
	YourTable
GROUP BY
	DATEADD(mi,DATEDIFF(mi,0,YourTimeColumn)/30*30,0)
ORDER BY
	1;

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-06-24 : 14:45:35

You rock!!! Thanks James!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources