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
How would you restart the basketID after each group?
I added an outer case statement which achieves the result!
from:
@BasketID = BasketID = @BasketID + CASE WHEN items + @RunningTotal < 10 AND groupID = @GroupID THEN 0 ELSE 1 END,
to:
@BasketID = BasketID = CASE WHEN groupID = @GroupID THEN (@BasketID + CASE WHEN item + @RunningTotal < 10 AND groupID = @GroupID THEN 0 ELSE 1 END) ELSE 1 END,
Many thanks