Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2013-01-23 : 13:32:02
|
Greetings everyone:I use a script to produce reports from performance logs our application logs. As you can see here the logs are logged in SQL server's default format and then I am converting it with 111, so I can group by that, so I can produce results for each day. (e.g. Group by on 2013-01-23)Now I need to produce results for each hour. So I want to be able to get the datetime with the hour. e.g. 2013-01-23 12, so I can do a group by. I am not able to find the right number for it. Is this impossible to do?Thanks for your time !!select LocalDate [date], count(*) [requests], avg(web_request_duration) [avg response time], min(web_request_duration) [min response time], max(web_request_duration) [max response time]from( select *, convert(varchar, LocalTime, 111) LocalDate from [WebRequest] MyApp where MyApp.message = 'MultiQuery Web Request' MyApp.firmid = 16 ) WebRequestsgroup by LocalDateorder by LocalDate desc |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-23 : 13:37:21
|
You could use another format and trim-off everything to the right of the hour part - for example, CONVERT(VARCHAR(13),GETDATE(),120) will do that. I like grouping by the hour also - like shown below:select LocalDate [date], LocalHour, count(*) [requests], avg(web_request_duration) [avg response time], min(web_request_duration) [min response time], max(web_request_duration) [max response time]from( select *, convert(varchar, LocalTime, 111) LocalDate, DATEPART(hour,LocalTime) AS LocalHour from [WebRequest] MyApp where MyApp.message = 'MultiQuery Web Request' AND MyApp.firmid = 16 ) WebRequestsgroup by LocalDate, LocalHourorder by LocalDate desc |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2013-01-23 : 13:58:41
|
THANKS A MILLION GUYS !!I will give these a whirl. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-23 : 14:02:34
|
[code]select DATEADD(hh,DATEDIFF(hh,0,LocalTime),0) AS LocalTime, count(*) [requests], avg(web_request_duration) [avg response time], min(web_request_duration) [min response time], max(web_request_duration) [max response time] from [WebRequest] MyApp where MyApp.message = 'MultiQuery Web Request' AND MyApp.firmid = 16 group by DATEADD(hh,DATEDIFF(hh,0,LocalTime),0)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2013-01-23 : 14:41:28
|
Thanks again buddy. Much appreciated. quote: Originally posted by James K You could use another format and trim-off everything to the right of the hour part - for example, CONVERT(VARCHAR(13),GETDATE(),120) will do that. I like grouping by the hour also - like shown below:select LocalDate [date], LocalHour, count(*) [requests], avg(web_request_duration) [avg response time], min(web_request_duration) [min response time], max(web_request_duration) [max response time]from( select *, convert(varchar, LocalTime, 111) LocalDate, DATEPART(hour,LocalTime) AS LocalHour from [WebRequest] MyApp where MyApp.message = 'MultiQuery Web Request' AND MyApp.firmid = 16 ) WebRequestsgroup by LocalDate, LocalHourorder by LocalDate desc
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-23 : 15:32:25
|
You are very welcome - glad to be of help. |
|
|
|
|
|
|
|