Luis Cazares - Tuesday, February 6, 2018 11:27 AM

I actually used the Quirky Update which is great for this sort of things. I would test it as I'm not sure about updating 2 columns at once. Check the full explanation (with tests and alternatives) in here: Solving the Running Total and Ordinal Rank Problems (Rewritten) - SQLServerCentral

SELECT IDENTITY(INT,1,1) AS RowID,
   v.OrderID,
   v.items,
   v.groupID,
   0 AS RunningTotalInGroup,
   0 AS BasketID
INTO #TestTable
FROM (VALUES
  ('Order1',4,3)
  ,('Order2',3,3)
  ,('Order3',4,3)
  ,('Order4',5,7)
  ,('Order5',6,8)
  ,('Order6',4,8)
  ,('Order7',5,8)
  )v(OrderID,items,groupID)
ORDER BY v.groupID, v.OrderID
;

CREATE CLUSTERED INDEX ci_testTable ON #TestTable (groupID, OrderID)

DECLARE @RunningTotal INT = 0, @GroupID INT = 0, @BasketID INT = 0;

UPDATE t WITH(TABLOCKX)
SET @RunningTotal = RunningTotalInGroup = items + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN @RunningTotal ELSE 0 END,
  @BasketID = BasketID = @BasketID + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN 0 ELSE 1 END,
  @GroupID = groupID
FROM #TestTable AS t
OPTION (MAXDOP 1);

SELECT *
FROM #TestTable AS tt

GO
DROP TABLE #TestTable