hi all any help very much appreciated
given this data - and I want to group and count all the times with in a 2 min time frames so 00:00 00:02 00:04 up to 23:58 - so all times between 00:00 and 00:01 get counted as 00:00 and for 00:02 and 00:03 as occurring at 00:02 so like 03:12 would show a count of 2 for 03:12 and 03:13
times col # time occurred
2010-02-05 00:00 5
2010-02-05 00:03 3
2010-02-05 00:04 2
2010-02-05 01:01 1
2010-02-05 01:02 1
2010-02-05 01:03 1
2010-02-05 01:04 1
2010-02-05 02:10 1
2010-02-05 02:12 1
2010-02-05 03:09 1
2010-02-05 03:11 1
2010-02-05 03:12 1
2010-02-05 03:13 1
2010-02-05 03:14 1
can I do a if then or a case w/ when like this .....
select count(*) as #timeoccur , timescol
if (datepart(mi,ti mecol)%2 <> 0 - saying the mi is a odd number
then dateadd(mi, -1, timecol) if its 13:01 will get counted with 13:00
else dateadd(mi, 0,timecol )
from table
given this data - and I want to group and count all the times with in a 2 min time frames so 00:00 00:02 00:04 up to 23:58 - so all times between 00:00 and 00:01 get counted as 00:00 and for 00:02 and 00:03 as occurring at 00:02 so like 03:12 would show a count of 2 for 03:12 and 03:13
times col # time occurred
2010-02-05 00:00 5
2010-02-05 00:03 3
2010-02-05 00:04 2
2010-02-05 01:01 1
2010-02-05 01:02 1
2010-02-05 01:03 1
2010-02-05 01:04 1
2010-02-05 02:10 1
2010-02-05 02:12 1
2010-02-05 03:09 1
2010-02-05 03:11 1
2010-02-05 03:12 1
2010-02-05 03:13 1
2010-02-05 03:14 1
can I do a if then or a case w/ when like this .....
select count(*) as #timeoccur , timescol
if (datepart(mi,ti mecol)%2 <> 0 - saying the mi is a odd number
then dateadd(mi, -1, timecol) if its 13:01 will get counted with 13:00
else dateadd(mi, 0,timecol )
from table