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