select
OrderID,
Items,
GroupID,
RunningTotalInGroup,
SUM(IncrBasket) OVER(ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as BasketID
from (
select
OrderID,
Items,
GroupID,
case lag(groupid) over (order by groupid)
when GroupID then
case
when SUM(items) OVER(PARTITION BY groupID ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) >= 10 then 1
else 0
end
else 1
end as IncrBasket,
SUM(items) OVER(PARTITION BY groupID ORDER BY groupID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RunningTotalInGroup
from #TestTable
) Rslt