SQL Server Performance

Suming by hour

Discussion in 'SQL Server Reporting Services' started by where_is_my_hat, May 23, 2005.

  1. where_is_my_hat New Member

    I am trying to generate a report that will add NOSESSION for all servers during a 1 hour period per day. This way, I could generate the total number of sessions for a server farm. I am note sure whether to use a function or how to query for data this way.

    RAW DATA EXAMPLE:
    SERVERNAME NOSESSION TIME FARM
    ------------------------------------------------------------
    SERVER1 5 5/5/2005 4:30:25 AM TEST1
    SERVER1 2 5/5/2005 5:30:26 AM TEST1
    SERVER1 7 5/5/2005 6:30:27 AM TEST1
    SERVER1 14 5/5/2005 7:30:28 AM TEST1
    SERVER1 23 5/5/2005 8:30:29 AM TEST1
    SERVER2 2 5/5/2005 4:30:25 AM TEST1
    SERVER2 3 5/5/2005 5:30:26 AM TEST1
    SERVER3 8 5/5/2005 5:30:27 AM TEST1
    SERVER2 11 5/5/2005 6:30:28 AM TEST1
    SERVER2 24 5/5/2005 7:30:29 AM TEST1

    DESIRED RESULTS:
    FARM TIME TOTALSESSIONS
    ----------------------------------------------------------

    TEST1 5/5/2005 4:00:00 AM 7
    TEST1 5/5/2005 5:00:00 AM 11
    TEST1 5/5/2005 6:00:00 AM 18
    TEST1 5/5/2005 6:00:00 AM 18
    TEST1 5/5/2005 7:00:00 AM 38
    TEST1 5/5/2005 8:00:00 AM 23

  2. dineshasanka Moderator


    select cast(cast(dt as int)+ cast(datepart( hh,dt) * 3600 as float) /86400 as smalldatetime) , sum(num) from t7 group by cast(cast(dt as int)+ cast(datepart( hh,dt) * 3600 as float) /86400 as smalldatetime)

    Will give you Time and the sum.



    quote:Originally posted by where_is_my_hat

    I am trying to generate a report that will add NOSESSION for all servers during a 1 hour period per day. This way, I could generate the total number of sessions for a server farm. I am note sure whether to use a function or how to query for data this way.

    RAW DATA EXAMPLE:
    SERVERNAME NOSESSION TIME FARM
    ------------------------------------------------------------
    SERVER1 5 5/5/2005 4:30:25 AM TEST1
    SERVER1 2 5/5/2005 5:30:26 AM TEST1
    SERVER1 7 5/5/2005 6:30:27 AM TEST1
    SERVER1 14 5/5/2005 7:30:28 AM TEST1
    SERVER1 23 5/5/2005 8:30:29 AM TEST1
    SERVER2 2 5/5/2005 4:30:25 AM TEST1
    SERVER2 3 5/5/2005 5:30:26 AM TEST1
    SERVER3 8 5/5/2005 5:30:27 AM TEST1
    SERVER2 11 5/5/2005 6:30:28 AM TEST1
    SERVER2 24 5/5/2005 7:30:29 AM TEST1

    DESIRED RESULTS:
    FARM TIME TOTALSESSIONS
    ----------------------------------------------------------

    TEST1 5/5/2005 4:00:00 AM 7
    TEST1 5/5/2005 5:00:00 AM 11
    TEST1 5/5/2005 6:00:00 AM 18
    TEST1 5/5/2005 6:00:00 AM 18
    TEST1 5/5/2005 7:00:00 AM 38
    TEST1 5/5/2005 8:00:00 AM 23


Share This Page