## Declare and populate testdata:

0

(click on this box to dismiss)

Q&A for programmers

``````--Declare and populate testdata:

set nocount on
declare @result table(mintime datetime, maxtime datetime)
declare @t table(time datetime, value int)

-- variation is how much difference will be allowed from one row to the next
declare @variation int
set @variation = 5

insert @t values('2010-01-01 00:15',3)
insert @t values('2010-01-01 00:30',2)
insert @t values('2010-01-01 00:45',4)
insert @t values('2010-01-01 01:00',5)
insert @t values('2010-01-01 01:15',1)
insert @t values('2010-01-01 01:30',3)
insert @t values('2010-01-01 01:45',4)
insert @t values('2010-01-01 02:00',12)
insert @t values('2010-01-01 02:15',13)
insert @t values('2010-01-01 02:30',12)
insert @t values('2010-01-01 02:45',14)
insert @t values('2010-01-01 03:00',15)
insert @t values('2010-01-01 03:15',3)
insert @t values('2010-01-01 03:30',2)
insert @t values('2010-01-01 03:45',3)
insert @t values('2010-01-01 04:00',5)

--Code:

a:

;with t as
select *, rn = row_number() over(order by time) from @t
), a as
(-- increase group if current row's value varies more than @variation from last row's value
select time, value, rn, 0 grp from t where rn = 1
union all
select t.time, t.value, t.rn, case when t.value between
a.value - @variation and a.value +@variation
then grp else grp+1 end
from t join a on
t.rn = a.rn +1
)
insert @result
select min(time), max(time) from a group by grp

if @@rowcount > 10
begin
-- this will activate if more than 10 groups of numbers are found
-- start over with higher tolerance for variation
set @variation=@variation + 1
delete @result
goto a
end

select convert(char(5), mintime,114) + ' to ' + convert(char(5), maxtime,114)
from @result``````

### Enter Parameters

Options:
-
Switch sites:
Hold tight while we fetch your results
:records rows returned in :time ms:cached