• Well your sample data doesn't seem to match the data you described in your explanation order 5 is actually a new group and doesn't sum out to more than 10 until order 7.  But based on your sample data this might work, although it's fairly messy and i'm sure someone can do better.  Also what do you expect to happen if you jump more than 10 in between 2 orders, is that two baskets?


    WITH TEMP_CTE AS(
    SELECT *, (SUM(items) OVER(PARTITION BY groupID ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / 10) + 1 AS BASKET_ID
    FROM #TestTable
    ), TEMP_CTE_TWO AS(
    SELECT groupID, MAX(BASKET_ID) AS MAX_BASKET FROM TEMP_CTE
    GROUP BY groupID
    )
    SELECT *, TEMP_CTE.BASKET_ID + PREV_BASKET_ID AS RUNNING_BUCKET FROM TEMP_CTE
    CROSS APPLY(
    SELECT groupID AS groupID_SUM, SUM(MAX_BASKET) OVER(ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - MAX_BASKET AS PREV_BASKET_ID FROM TEMP_CTE_TWO
    ) PREVIOUS_BASKET
    WHERE
    TEMP_CTE.groupID = PREVIOUS_BASKET.groupID_SUM