• Thanks livingstone :-), this one is perfect .. meanwhile I have done like this

    SELECT *,LEAD(Amount,1,0)OVER (ORDER BY date) nextv,lag(Amount,1,0)OVER (ORDER BY date) PRevv

    FROM #tmp

    ),CTE1 as (

    SELECT CASE WHEN PRevv<>Amount THEN Date END startdate,CASE WHEN nextv<>amount THEN Date END enddate, Amount

    FROM CTE

    )

    SELECT startdate,(SELECT MIN(enddate) FROM CTE1 tt WHERE amount=t.amount AND tt.enddate>=t.startdate) ,Amount

    FROM CTE1 t

    WHERE startdate IS NOT NULL