CREATE TABLE #tbl ( ItemCode CHAR(4),Qty INT,TreeType CHAR(1),Linenum INT,run_total INT)INSERT INTO #tbl (itemcode,qty,treetype,linenum)VALUES('A100', 1,'S', 0),('A101', 2 , 'I', 1),('A102', 3 , 'I', 2),('B100', 3 , 'S', 3),('B101', 2 , 'I', 4),('B102', 2 , 'I', 5),('C100', 5, 'N', 6)CREATE CLUSTERED INDEX IX_#tbl_LinenumON #tbl (Linenum DESC);DECLARE @Sequence INT = 0, @runtotal INT = 0, @grpchange CHAR(1) = ' ' ;WITH safetycheck AS( SELECT itemcode, qty, treetype, run_total, SEQUENCE = ROW_NUMBER() OVER (ORDER BY linenum DESC) FROM #tbl)UPDATE t SET @Sequence = CASE WHEN SEQUENCE = @Sequence + 1 THEN @Sequence + 1 ELSE 1/0 END, @runtotal = run_total = CASE WHEN @grpchange = LEFT(ItemCode,1) THEN @runtotal + CASE WHEN treetype ='S' THEN 0 ELSE qty END ELSE qty END, @grpchange = LEFT(ItemCode,1) -- ANCHOR COLUMN FROM SafetyCheck t WITH (TABLOCKX)OPTION (MAXDOP 1);SELECT itemcode,run_total,treetype,linenum FROM #tblWHERE STUFF(itemcode,1,1,'')='100'ORDER BY linenum;