select 0 , 2 union all

select 1 , 1 union all

select 2 , 0 union all

select 3 , -1 union all

select 4 , -2 union all

select 5 , -4 union all

select 6 , -4 union all

select 7 , -5 union all

select 8 , -3 union all

select 9 , -2 union all

select 10 , -1 union all

select 11 , -1 union all

select 12 , 1 union all

select 13 , 1 union all

select 14 , -1 union all

select 15 , -2 union all

select 16 , -1 union all

select 17 , 0 union all

select 18 , 1

)

select ttime, temperature, case when min(temperature) over(partition by grp) =-5 then 1 else 0 end flag

from (

select *, row_number() over(order by ttime) - row_number() over(partition by case when temperature<0 then 1 else 2 end order by ttime) grp

from tbl

) t

order by ttime