15 Minuter Intervals - 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)

15 Minuter Intervals

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-04-16 : 16:43:00

I am trying to write code to group times by 15 minute intervals. I was able to get the followig code to work, but I am stuck trying to group it. Below is a sample code I have.

select
[5min] = dateadd(minute,(datediff(minute,0,a.dt)/5)*5,0),
[10min] = dateadd(minute,(datediff(minute,0,a.dt)/10)*10,0),
[15min] = dateadd(minute,(datediff(minute,0,a.dt)/15)*15,0),
[20min] = dateadd(minute,(datediff(minute,0,a.dt)/20)*20,0),
[30min] = dateadd(minute,(datediff(minute,0,a.dt)/30)*30,0),
a.dt
from
(select dt = convert(datetime,'2005-11-07 19:55:19.757') ) a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 16:47:18

so what should be your output? you want above division to be applied for each datevalue in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-16 : 16:48:05

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101', dt) / 15 * 15, '19000101'), COUNT(*)
FROM dbo.Table
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101', dt) / 15 * 15, '19000101')
ORDER BY DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101', dt) / 15 * 15, '19000101')

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 16:50:48

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-04-17 : 08:39:43

I am trying to group the number of calls that arrived in queue into 15 minute intervals.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-17 : 09:50:09

Yes, we understand. See my solution posted 04/16/2012 : 16:48:05

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

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-04-17 : 11:06:39

@ SwePeso
I was able to get your solution and the code below to work.

DATEADD(MINUTE, DATEDIFF(MINUTE, 0, queued_time) / 15 * 15, 0)

My follow on question is how can both codes to round up to nearest 15 minute.

Thank you!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-17 : 11:42:04

What do you mean by "Round?" Do you mean the ceiling? As in, if the time is 12:16 it should go to 12:30?

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-04-17 : 11:58:45

Disreguard! Silly me. The code works find and it matches the data I am comparing it to. Thanks everyone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources