Tree Node Calculation - SQL Server
This solution also gets the intermediate nodes

;WITH RecursiveCTE
AS
(
SELECT  [SUB] , 
        [SUB] as [Top Level Part],
        CAST([SUB] as varchar(255)) as [Parts Nodes Combination],
        0 AS [LEVEL],
        [LEADTIME]  as [Days],
        CAST([LEADTIME] as varchar(255)) as [BASED ON]
FROM dbo.Levels s1
WHERE [LEVEL] = 0

UNION ALL

SELECT  s1.SUB as [Top Level Part],
        RecursiveCTE.[Top Level Part],
        CAST(RecursiveCTE.[Parts Nodes Combination]+ ' &gt;&gt; ' + cast(s1.[SUB] as varchar(255)) as varchar(255)),
        RecursiveCTE.[LEVEL] + 1 as [LEVEL],
        RecursiveCTE.[Days] + s1.[LEADTIME] as [Days],
        CAST(RecursiveCTE.[BASED ON] + ' + ' + CAST(s1.[LEADTIME] as varchar(255))  as  varchar(255))
FROM dbo.Levels s1
INNER JOIN RecursiveCTE 
ON s1.[ROOT] = RecursiveCTE.[SUB]
WHERE s1.[LEVEL] &gt; 0
)
SELECT * FROM RecursiveCTE
WHERE [LEVEL] != 0;


Result

SUB Top Level Part  Parts Nodes Combination LEVEL   Days    BASED ON
2       1               1 &gt;&gt; 2                  1       6       4 + 2
3       1               1 &gt;&gt; 3                  1       9       4 + 5
7       1               1 &gt;&gt; 7                  1       24      4 + 20
4       1               1 &gt;&gt; 3 &gt;&gt; 4             2       13      4 + 5 + 4
6       1               1 &gt;&gt; 3 &gt;&gt; 6             2       14      4 + 5 + 5
4       1               1 &gt;&gt; 2 &gt;&gt; 4             2       10      4 + 2 + 4
5       1               1 &gt;&gt; 2 &gt;&gt; 5             2       7       4 + 2 + 1


DB&lt;>Fiddle

If you want to filter out the intermediate nodes:

;WITH RecursiveCTE
AS
(
SELECT  [SUB] , 
        [SUB] as [Top Level Part],
        CAST([SUB] as varchar(255)) as [Parts Nodes Combination],
        0 AS [LEVEL],
        [LEADTIME]  as [Days],
        CAST([LEADTIME] as varchar(255)) as [BASED ON]
FROM dbo.Levels s1
WHERE [LEVEL] = 0

UNION ALL

SELECT  s1.SUB as [Top Level Part],
        RecursiveCTE.[Top Level Part],
        CAST(RecursiveCTE.[Parts Nodes Combination]+ ' &gt;&gt; ' + cast(s1.[SUB] as varchar(255)) as varchar(255)),
        RecursiveCTE.[LEVEL] + 1 as [LEVEL],
        RecursiveCTE.[Days] + s1.[LEADTIME] as [Days],
        CAST(RecursiveCTE.[BASED ON] + ' + ' + CAST(s1.[LEADTIME] as varchar(255))  as  varchar(255))
FROM dbo.Levels s1
INNER JOIN RecursiveCTE 
ON s1.[ROOT] = RecursiveCTE.[SUB]

WHERE s1.[LEVEL] &gt; 0

)

SELECT * FROM RecursiveCTE
WHERE [LEVEL] != 0
AND NOT EXISTS 
(
SELECT * FROM
dbo.Levels s2
WHERE RecursiveCTE.SUB = s2.[ROOT])
;


Result

SUB Top Level Part  Parts Nodes Combination LEVEL   Days    BASED ON
7           1               1 &gt;&gt; 7           1      24      4 + 20
4           1               1 &gt;&gt; 3 &gt;&gt; 4      2      13      4 + 5 + 4
6           1               1 &gt;&gt; 3 &gt;&gt; 6      2      14      4 + 5 + 5
4           1               1 &gt;&gt; 2 &gt;&gt; 4      2      10      4 + 2 + 4
5           1               1 &gt;&gt; 2 &gt;&gt; 5      2      7       4 + 2 + 1


DB&lt;>Fiddle

Table &amp; Data used

CREATE TABLE dbo.Levels ([PARTID] INT,[ROOT] int,[SUB] int,[LEVEL] int, [LEADTIME] int,[TOP LEVEL PART] int);
--PartID added
INSERT INTO dbo.Levels
 ([PARTID], [ROOT] ,[SUB] ,[LEVEL] , [LEADTIME] ,[TOP LEVEL PART])

VALUES
(1,1 , 1 ,0 ,4 ,1 ),
(1,1 , 2 ,1 ,2 ,1 ),
(1,1 , 3 ,1 ,5 ,1 ),
(1,1 , 7 ,1 ,20 ,1 ),
(1,2 , 4 ,1 ,4 ,1 ),
(1,2 , 5 ,1 ,1 ,1 ),
(1,3 , 4 ,2 ,4 ,1 ),
(1,3 , 6 ,2 ,5 ,1 );


Edit

Op comments:


  I've noticed the data is repeating a lot (particularly on larger
  assemblies)
  
  Basically I have Sub Router 1691, on top level router 1731. the Parts
  node combination is 1731 -- 1727 -- 1691 and this repeats exactly the
  same columns 8 times. Then, when it introduces another node (so 1731
  -- 1727 -- 1691 -- 1682), this one repeats 168 times. This is the case all of the way through. It appears that any 3 level nodes repeat 8
  times &amp; any 4 level nodes 168 times. 
  
  On a different one it repeats far fewer times. I'm wondering whether
  it has something to do with the number of unique parts in the tree. 
  
  I think I've figured it out! A single sub can appear on several
  different top level parts. On the join to the recursiveCTE table I've
  also added 'and RecursiveCTE.[Top Level Part] = s1.TopLevelRouter' and
  I think it's done the trick.


Part of the query that needed to be changed:

 ...    
FROM dbo.Levels s1
INNER JOIN RecursiveCTE 
ON s1.[ROOT] = RecursiveCTE.[SUB]
and RecursiveCTE.[Top Level Part] = s1.TopLevelRouter
WHERE s1.[LEVEL] &gt; 0
)
SELECT * FROM RecursiveCTE
WHERE [LEVEL] != 0;