Here's the solution I came up with.
I'm using the "quirky update" method with safety check as described here. Please follow the link mentioned in that post and read the related article by Jeff Moden (including the posts in the discussion). The quirky update is a powerful method but there are some rules to follow.
Please note that the article is currently rewritten to reflect the latest improvementes.
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_Linenum
ON #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 #tbl
WHERE STUFF(itemcode,1,1,'')='100'
ORDER BY linenum;