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
Post a Comment