;WITH CTE_SUM (MinID, MaxID, MySum)
AS
(
SELECT ID, ID, Number1
FROM #Example
WHERE ID = 1
UNION ALL
SELECT
CASE
WHEN E.Number1+S.MySum > 3000 THEN E.ID
ELSE S.MinID
END,
E.ID,
CASE
WHEN E.Number1+S.MySum > 3000 THEN E.Number1
ELSE E.Number1+S.MySum
END
FROM #Example E
INNER JOIN CTE_SUM S ON E.ID=S.MaxID+1
)
SELECT MinId, MAX(MaxId) MaxId, MAX(MySum) MySum
FROM CTE_SUM
GROUP BY MinId
ORDER BY MinId
Id Number 1
1 1000
2 1500
3 3000
4 50
5 100
6 2500
7 350
8 500
9 750
10 2300
11 900
Now, which results should be expected?