| Author |
 Topic  |
|
|
shiyam198
Yak Posting Veteran
82 Posts |
 Posted - 01/23/2013 : 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
)
WebRequests
group by LocalDate
order by LocalDate desc
|
|
|
James K
Flowing Fount of Yak Knowledge
1527 Posts |
Posted - 01/23/2013 : 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
)
WebRequests
group by LocalDate, LocalHour
order by LocalDate desc |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
|
|
shiyam198
Yak Posting Veteran
82 Posts |
Posted - 01/23/2013 : 13:58:41
|
THANKS A MILLION GUYS !!
I will give these a whirl. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/23/2013 : 14:02:34
|
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)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
shiyam198
Yak Posting Veteran
82 Posts |
Posted - 01/23/2013 : 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
)
WebRequests
group by LocalDate, LocalHour
order by LocalDate desc
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1527 Posts |
Posted - 01/23/2013 : 15:32:25
|
| You are very welcome - glad to be of help. |
|
|
| |
Topic |
|
|
|
Datetime
