Elegant Code to find continuous time period and generate id
You just need to apply DENSE_RANK to the result of the first CTE - Demo

WITH T
     AS (SELECT *,
                [time] - row_number() OVER (ORDER BY [time]) AS group_num
         FROM   #test1
         WHERE  value &gt; 0)
SELECT *,
       DENSE_RANK() OVER (ORDER BY group_num) AS group_id
FROM   T