| Author |
 Topic  |
|
|
sergeant_time
Yak Posting Veteran
68 Posts |
 Posted - 04/16/2012 : 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
India
47069 Posts |
Posted - 04/16/2012 : 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
Sweden
29138 Posts |
Posted - 04/16/2012 : 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
India
47069 Posts |
|
|
sergeant_time
Yak Posting Veteran
68 Posts |
Posted - 04/17/2012 : 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
Sweden
29138 Posts |
Posted - 04/17/2012 : 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
68 Posts |
Posted - 04/17/2012 : 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
Flowing Fount of Yak Knowledge
3826 Posts |
Posted - 04/17/2012 : 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
68 Posts |
Posted - 04/17/2012 : 11:58:45
|
Disreguard! Silly me. The code works find and it matches the data I am comparing it to. Thanks everyone
|
|
|
| |
Topic |
|
15 Minuter Intervals
