Declare and populate testdata:

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

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
    ( -- add a rownumber
    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:
loading Hold tight while we fetch your results
:records returned in :time ms:cached