sql group by time periods 10 mins -


i change time periods '1 hour' '10 mins'.

and change display time '10' '10:00'

declare @periodstart datetime declare @periodend datetime  set @periodstart =  convert(varchar(10), getdate() - 1, 120) set @periodend = convert(varchar(10), getdate() , 120)   set @periodstart = dateadd(hh, datepart(hh,@periodstart), convert(varchar(12),@periodstart,112)) set @periodend = dateadd(hh, datepart(hh,@periodend), convert(varchar(12),@periodend,112))   ;with dh ( select top 144  dateadd(hour,row_number() on (order [object_id])-1,convert(varchar(12),@periodstart,112)) hodstart, dateadd(hour,row_number() on (order [object_id]),convert(varchar(12),@periodstart,112)) hodend, row_number() on (order object_id)-1 dayhour   sys.columns  ) select d.dayhour, count(f.hostname) 'counter' dh d   left join filebackup f on    f.starttime < d.hodend  , f.endtime >= d.hodstart   d.hodstart between @periodstart , @periodend group d.dayhour order d.dayhour 

have @ post..

http://social.technet.microsoft.com/wiki/contents/articles/17976.t-sql-group-by-time-interval.aspx

i think group 1/6 th of hour - every sixth of hour every 10 minutes.

re-writing query, should see last answer:

select  datepart(minute, logtime)/6 [sixthhour],            count(loginid) [logins completed]    somelog group datepart(minute, logtime)/6 

this should - http://sqlfiddle.com/#!3/f60f3/1


Comments

Popular posts from this blog

java.util.scanner - How to read and add only numbers to array from a text file -

rewrite - Trouble with Wordpress multiple custom querystrings -