6

I'd appreciate some help with something I am trying to achieve please.

The parts that my company make are built from sub assemblies and sub assemblies of sub assemblies. This looks like:

             1
         /   \  \
        /     |   \
       2      3    7
      / \    / \
     4   5  4   6

Where 4 & 5 come together to make 2, 4 & 6 make 3, then assemblies 2, 3 & 7 make 1.

My table looks like this;

+------+-----+-------+----------+----------------+
| ROOT | SUB | LEVEL | LEADTIME | TOP LEVEL PART |
+------+-----+-------+----------+----------------+
|    1 |   1 |     0 |        4 |              1 |
|    1 |   2 |     1 |        2 |              1 |
|    1 |   3 |     1 |        5 |              1 |
|    1 |   7 |     1 |       20 |              1 |
|    2 |   4 |     1 |        4 |              1 |
|    2 |   5 |     1 |        1 |              1 |
|    3 |   4 |     2 |        4 |              1 |
|    3 |   6 |     2 |        5 |              1 |
+------+-----+-------+----------+----------------+

Each part has a lead time of how long it takes to make it. What I want to do is pass a top level part number and it calculate the overall lead time for the part.

Originally I thought I could just group by the Level and take the maximum lead time per level and add them together but that wouldn't be accurate. Level 1 Part 7 takes 20 days to make so we would be able to be making all of the other parts whilst 7 was being made. The calculation needs to be based on the part that consumes its parent (for want of a better phrase).

What I need to do is calculate the lead time for every route through the tree and add up the lead times as it goes. So I would like to return a table something like;

+----+----------------+-------------------------+------+------------+
| ID | Top Level Part | Parts Nodes Combination | Days | [BASED ON] |
+----+----------------+-------------------------+------+------------+
| 1  | 1              | 1 >> 2 >> 4             | 10   | 4 + 2 + 4  |
| 2  | 1              | 1 >> 2 >> 5             | 7    | 4 + 2 + 1  |
| 3  | 1              | 1 >> 3 >> 4             | 13   | 4 + 5 + 4  |
| 4  | 1              | 1 >> 3 >> 6             | 14   | 4 + 5 + 5  |
| 5  | 1              | 1 >> 7                  | 24   | 4 + 20     |
+----+----------------+-------------------------+------+------------+

I can then find the MAX of the days column which will tell me that the overall lead time for Part 1 is 24 days.

I really hope I've made my post understandable and all is formatted correctly. I've tried to read up on recursive SQL queries but I'm struggling to get my head around them. The most number of levels I am ever likely to have is 4 so I'd hope it wouldn't be too pc intensive. If so, I'd much rather be able pass all of my parts through, rather than doing them one part at a time (hence why I've got a top level part column in my table).

Any help or advice would be greatly appreciated.

  • 3
    Is there any ID field and is there any PARENT_ID? – McNets Jul 11 at 13:15
  • Each part has a lead time of how long it takes to make it. Looking your data I see that leadtime is referenced to SUBpart, not to part. – Akina Jul 11 at 13:30
  • The SUB number is the part and the ROOT is the parent that it belongs to. The same sub part can belong to several different ROOTs but it will be referenced separately (same sub, different root). – David Jul 11 at 13:31
  • Akina, yes - each sub (or sub assembly) part has a lead time, of how long it takes to make. I'm wanting to get a lead time for the top level part based on the lead times of all of the sub assembly parts but it is not linear, it is tree like so certain parts can be being made at the same time as others. – David Jul 11 at 13:35
  • I can then find the MAX of the days column which will tell me that the overall lead time for Part 1 is 24 days. I doubt. Change leadtime for subpart 2 from 4 to 15. Your calculations will give 24 again. Whereas you need 2 pieces of subpart 2... or one piece may work twice in parallel? – Akina Jul 11 at 13:36
3

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]+ ' >> ' + 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] > 0
)
SELECT * FROM RecursiveCTE
WHERE [LEVEL] != 0;

I did add a different level column as I don't see why the level for SUB = 4 would be 1. Should it not be 2?

Result

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

DB<>Fiddle

If you want to then 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]+ ' >> ' + 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] > 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 >> 7           1      24      4 + 20
4           1               1 >> 3 >> 4      2      13      4 + 5 + 4
6           1               1 >> 3 >> 6      2      14      4 + 5 + 5
4           1               1 >> 2 >> 4      2      10      4 + 2 + 4
5           1               1 >> 2 >> 5      2      7       4 + 2 + 1

DB<>Fiddle

Table & Data

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 );
  • Thanks Randi, will have a look at this today. The 'level' just represents how many nodes down the tree the part is. So a part could have multiple levels depending on which top level part it is belonging to. It might be on level 1 on one particular assembly but it could appear on level 3 on another. – David Jul 12 at 7:42
  • @David Great! So the level does not matter for the calculation right? Thanks for the feedback :) – Randi Vertongen Jul 12 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.