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);
@Sequence INT = 0,
@runtotal INT = 0,
@grpchange CHAR(1) = ' '
;WITH safetycheck AS
SEQUENCE = ROW_NUMBER() OVER (ORDER BY linenum DESC)
SET @Sequence = CASE WHEN SEQUENCE = @Sequence + 1 THEN @Sequence + 1
ELSE 1/0 END,
WHEN @grpchange = LEFT(ItemCode,1)
THEN @runtotal +
WHEN treetype ='S'
THEN 0 ELSE qty
@grpchange = LEFT(ItemCode,1) -- ANCHOR COLUMN
FROM SafetyCheck t WITH (TABLOCKX)
OPTION (MAXDOP 1);
ORDER BY linenum;
LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questions
Links for Tally Table
, Cross Tabs
and Dynamic Cross Tabs
, Delimited Split Function