Post reply

group by minutes help

  • June 27, 2005 at 1:20 pm

    #86660

    I have an audit log table and I need to display the results grouped by the minute of insert of event. I use datepart, but then it groups them wrong as there are inserts for more then 1 day, when I add a day then it groups them by seconds. What I'm doing wrong?

    select( datepart(mm,rcv_dt)+datepart(dd,rcv_dt)+ datepart(hh,rcv_dt)+datepart(mi,rcv_dt)) AS MINUTE,count (*) AS ERPM

    from audit_LOG1

    group by (datepart(mm,rcv_dt)+datepart(dd,rcv_dt)+ datepart(hh,rcv_dt)+datepart(mi,rcv_dt))

    ORDER BY 1 ASC;

    Thanks for the help.MJ

  • June 27, 2005 at 1:50 pm

    #568746

    select convert(varchar(14),rcv_dt,20) AS MINUTE,count (*) AS ERPM

    from audit_LOG1

    group by  convert(varchar(14),rcv_dt,20)

    ORDER BY 1


    * Noel

  • June 27, 2005 at 2:04 pm

    #568748

    ORDER BY 1 is not gonna be supported in Yukon... just to make sure you knew about this .

  • June 27, 2005 at 2:21 pm

    #568753

    I have tried that in Yukon, Apparently you just  saved me time

     


    * Noel

  • June 27, 2005 at 2:49 pm

    #568759

    If ORDER BY 1 is not supported, what replaced it?  Specifying the column, (or in this case, the display) data directly?  

    I wasn't born stupid - I had to study.

  • June 27, 2005 at 2:51 pm

    #568760

    Thanks a lot, guys, you saved me so much time.

  • June 27, 2005 at 2:58 pm

    #568762

    OK, but this doesn't work the way I need it:

    it groups by seconds not by minutes...

    How can I make it group by minutes?

    Thanks,mj

  • June 27, 2005 at 3:11 pm

    #568764

    Oops its 16

    select convert(varchar(16),rcv_dt,20) AS MINUTE,count (*) AS ERPM

    from audit_LOG1

    group by  convert(varchar(16),rcv_dt,20)

    ORDER BY 1

     


    * Noel

  • June 28, 2005 at 6:22 am

    #568881

    Good thing you tested those results Noeld .

  • June 28, 2005 at 6:46 am

    #568890

    If you use CAST(rcv_dt as smalldatetime) it will group by minutes without resorting to string manipulation.

  • June 28, 2005 at 9:34 am

    #568986

    it's a good idea but it doesn't seem to work :

    Select

    cast('2005-05-02 14:56:52.493' as smalldatetime)

    , cast ('2005-05-02 14:57:03.447' as smalldatetime)

    , convert(varchar(16),'2005-05-02 14:56:52.493',20)

    , convert(varchar(16),'2005-05-02 14:57:03.447',20)

    2005-05-02 14:57:00

    2005-05-02 14:57:00

    2005-05-02 14:56

    2005-05-02 14:57

  • June 28, 2005 at 9:46 am

    #568997

    It rounds instead of truncates.  You can make it work the way you want by subtracting 30 seconds.

    Select

    cast(dateadd(s,-30,'2005-05-02 14:56:52.493') as smalldatetime)

    , cast (dateadd(s,-30,'2005-05-02 14:57:03.447') as smalldatetime)

    , convert(varchar(16),'2005-05-02 14:56:52.493',20)

    , convert(varchar(16),'2005-05-02 14:57:03.447',20)

    2005-05-02 14:56:00

    2005-05-02 14:57:00

    2005-05-02 14:56

    2005-05-02 14:57

  • June 28, 2005 at 9:52 am

    #569004

    Thanx. Looks like an index seek is just out of the question here, so the simplest conversion wins .

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply