It's slightly more complicated than just using a CTE...

DECLARE @tbl TABLE

(

ckid INT,stat VARCHAR(10)

)

INSERT INTO @tbl

SELECT 101 ,'Open' UNION ALL

SELECT 102 ,'Open' UNION ALL

SELECT 103 ,'Open' UNION ALL

SELECT 104 ,'Void' UNION ALL

SELECT 105 ,'Void' UNION ALL

SELECT 106 ,'Open' UNION ALL

SELECT 107 ,'Open' UNION ALL

SELECT 108 ,'Open' UNION ALL

SELECT 109 ,'Void' UNION ALL

SELECT 110 ,'Open'

SELECT *

FROM @tbl

;WITH cte AS -- row number per group

(

SELECT

*,

ROW_NUMBER() OVER(ORDER BY ckid) r1,

ROW_NUMBER() OVER(ORDER BY ckid)-

ROW_NUMBER() OVER(PARTITION BY stat ORDER BY ckid ) AS ROW

FROM @tbl

),cte2 AS -- row number per group range, ordered by ckid

(

SELECT MIN(r1) mi,MAX(r1) ma,stat,ROW, ROW_NUMBER() OVER(ORDER BY MIN(r1) ) r2

FROM cte

GROUP BY stat,ROW

)

SELECT ckid ,cte.stat,cte2.r2

FROM cte

INNER JOIN cte2 ON r1>=mi AND r1<=ma

ORDER BY r1