|start date||end date||id||value|
I think this might help you. May be some may give you even more easy solution..)
sel id, val, st,max(est) from (
sel id, val, min(cold) over(partition by id order by cold reset when val<> min(val) over(partition by id order by id, cold rows between 1 preceding and 1 preceding)) as st,
max(cold) over(partition by id order by cold reset when val<> max(val) over(partition by id order by id, cold rows between 1 preceding and 1 preceding)) as est
from abc) xy
group by id, val, st
Please test it thoroughly as I am not sure for all cases.