SQL Cumulative recursive sum (Backtracking)

This problem had me spinning around for a week.

Here is the scenario: In a factory there are many factory areas, and each factory area can be a subsection of a larger factory area. Users can capture quality data in any small factory area. What happens then is that the data they capture can be rolled up to every upper level. Essentially this is a rollup problem where the sum of each child's data adds to the data captured in the parent.

The solution to this can be done by backtracking, where we navigate to the bottom of the tree. We return the values associated with the leaf level nodes, and then we work our way back up the tree, summing all the subtrees until we get the cumulative sumof the whole tree.

See the following link for the full development of this case:

http://www.vbforums.com/showthread.php?p=3877621#post3877621

USE Sysprodb

GO

FUNCTION fnRollUp

GO

CREATE FUNCTION fnRollUp(@Area nvarchar(20),@lev int)

RETURNS @data TABLE(Area nvarchar(20),Qty int,lev int)

AS

BEGIN

DECLARE @tmp TABLE (Area nvarchar(20), row int)

DECLARE @rows int;

DECLARE @childid nvarchar(20);

DECLARE @qty int;

SET @qty=0;

--search for child nodes

INSERT INTO @tmp

SELECT AreaID,ROW_NUMBER() OVER(ORDER BY AreaID)

FROM Areas JOIN Movements

ON Areas.AreaID = Movements.Area

WHERE ParentID=@Area

GROUP BY ParentID,AreaID;

SET @rows=@@rowcount;

--if child nodes exist

IF @rows>0

BEGIN

--for each child node

WHILE @rows>0

BEGIN

--get child node

SELECT @childid=Area FROM @tmp

WHERE row=@rows;

--get next child

SET @rows=@rows-1;

---------------------------------------------- A1, B2, C3, D4

--go down the tree here

----------------------------------------------

---- List the nodes on the way down (commented out-not needed)

-- INSERT INTO @data

-- SELECT @Area,Qty FROM @tmp; --debug

----------------------------------------------

INSERT INTO @data

SELECT Area,Qty,lev

FROM fnRollUp(@childid,@lev+1) -- this is the entry and exit point for the recursion!

-- only one child node returned at a time

---------------------------------------------- E5, D9, C12,B14,A15

--go up the tree here

----------------------------------------------

END

----------------------------------------------

SELECT @qty = SUM(Qty)

FROM Movements

WHERE Area=@Area;

--add the qty of children to the qty of the parent

INSERT INTO @data

SELECT @Area,SUM(Qty)+@qty,@lev

FROM @data

WHERE lev=@lev+1

END

ELSE

BEGIN

--stop going down the tree and start going back up the tree

INSERT INTO @data

SELECT @Area,SUM(Qty),@lev

FROM Movements

WHERE Area=@Area

END

RETURN

END

GO

SELECT * FROM fnRollUp('A',1)

Along the way I developed a new technique for looking at inputs and outputs to and from recursive functions. See the diagram attached!

Update: I have now extended the example above to include the accumulated quantity of stock movements per factory area by day of month: Note the easy use of matrices:

DROP