By David


2019-07-11 12:45:38 8 Comments

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.

1 comments

@Randi Vertongen 2019-07-11 14:00:25

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 );

@David 2019-07-12 07:42:34

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.

@Randi Vertongen 2019-07-12 11:51:11

@David Great! So the level does not matter for the calculation right? Thanks for the feedback :)

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Why low cpu usage on sql server developer edition 2014

  • 2018-10-19 07:07:12
  • user979899
  • 172 View
  • 1 Score
  • 1 Answer
  • Tags:   sql-server

2 Answered Questions

[SOLVED] Merging continous date range using T-SQL

1 Answered Questions

[SOLVED] Help with CTE Aggregating Children Recursively

3 Answered Questions

[SOLVED] Huge slowdown to SQL Server query on adding wildcard (or top)

0 Answered Questions

SQL Server : implicit calculation of precision/scale for datetime scalar

  • 2015-11-18 04:37:02
  • Peter Vandivier
  • 113 View
  • 1 Score
  • 0 Answer
  • Tags:   sql-server datetime

2 Answered Questions

[SOLVED] Queries are fast separtely, but slow when joined via subquery

  • 2015-09-28 21:14:12
  • Konraden
  • 3652 View
  • 2 Score
  • 2 Answer
  • Tags:   sql-server

0 Answered Questions

How to group SQL Server files for restoring database

0 Answered Questions

Problem with Database for survey Questionnaires

3 Answered Questions

[SOLVED] SQL Server - Multiple running totals

Sponsored Content