SQL Server Performance Forum – Threads Archive

Suming by hour

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

---

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.

---

]]>