use date formats with if then else or case when

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • dragrid
    New Member
    • Jan 2009
    • 29

    use date formats with if then else or case when

    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
Working...