Grouping - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping

Author

Topic

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2013-09-20 : 10:34:42

Hi All,

I have this script that runs on a LOGGING database and find hourly requests for a particular firm.
the date format is "2013-08-19 13:44:50.177"

How can I group it by every 15 mins?

Thanks for your help


select 
	LocalDate [date],
	LocalHour [Hour],
	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] wrcc
	where 
		wrcc.firmid = 9
		and wrcc.web_request_ip <> '206.191.112.40'
) 
cc_web_requests
group by LocalDate, LocalHour
order by LocalDate desc, LocalHour desc

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-20 : 11:12:21

Group also by DATEDIFF(mi,0,LocalTime)/15*15

select 
	LocalDate [date],
	LocalHour [Hour],
	DATEDIFF(mi,0,LocalTime)/15*15 as [15MinInterval],
	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,
		LocalTime		
	from
	[WebRequest] wrcc
	where 
		wrcc.firmid = 9
		and wrcc.web_request_ip <> '206.191.112.40'
) 
cc_web_requests
group by LocalDate, LocalHour, DATEDIFF(mi,0,LocalTime)/15*15
order by LocalDate desc, LocalHour desc

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-20 : 11:13:56

Actually by DATEADD(mi,DATEDIFF(mi,0,t)/15*15,0) rather than DATEDIFF(mi,0,LocalTime)/15*15 if you want to show the beginning of the 15 minute interval

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2013-09-20 : 11:20:35

Brilliant. Thanks a lot

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-20 : 11:59:29

Alternative:


select convert(varchar,localtime,111) as localdate
      ,datepart(hour,localtime) as localhour
      ,datepart(minute,localtime)/15*15 as localmin
      ,count(*) as requests
      ,avg(web_request_duration) as avg_response_time
      ,min(web_request_duration) as min_response_time
      ,max(web_request_duration) as max_response_time
  from webrequest
 where firmid=9
   and web_request_ip<>'206.191.112.40'
 group by convert(varchar,localtime,111)
         ,datepart(hour,localtime)
         ,datepart(minute,localtime)/15*15
 order by localdate desc
         ,localhour desc
         ,localmin desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources