Help with time buckets - 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

Help with time buckets

Author

Topic

ehcpieterse
Starting Member

2 Posts

Posted - 2011-07-07 : 06:41:46

One of my colleagues helped me with a query to count the number of transactions per hour. Without asking them again, could I please get some advice on how do the exact same thing, but to also break it down into 10 minute intervals? My current code is below:


select INSTR_CODE,
convert(varchar, TRADE_DATE_TIME, 112) as DATE, 
datepart(hh, TRADE_DATE_TIME) as HOUR, 
sum(QUANTITY)as QTY

from HERMES_PUBLIC.PUBLIC_TRADES_HISTORY
where TRADE_DATE_TIME >= '20110601' and INSTR_CODE = 'AGL'
and TRADE_TYPE = 'AT'
and datepart(hh, TRADE_DATE_TIME) >= 9 and datepart(hh, TRADE_DATE_TIME) < 17
group by INSTR_CODE, convert(varchar, TRADE_DATE_TIME, 112), datepart(hh, TRADE_DATE_TIME)
order by INSTR_CODE, DATE, HOUR

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 07:01:22

Add one more condition to your group by clause like this:


group by INSTR_CODE, convert(varchar, TRADE_DATE_TIME, 112), datepart(hh, TRADE_DATE_TIME)
,DATEPART(minute,TRADE_DATE_TIME)/10

If you want to see the ten minute interval to which the group belongs to, add another column in your select list:

,DATEPART(minute,TRADE_DATE_TIME)/10*10

ehcpieterse
Starting Member

2 Posts

Posted - 2011-07-07 : 07:11:40

perfect txs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources