question

Basilicoq13 avatar image
Basilicoq13 asked

Calculating through hierarchy

Hi All

I have two tables with production informations. One table with the Main item, bomlist ID and hours and another table with the items that goes in to the bom list and hours.

The Main items might go in to another Main Item which means that it will be a sub item on a bom list for a main item.

I what to calculate how many hours an Item takes to produce through the whole network of 10 levels.

It means that one item might have a bomlist containing items that have another bomlist containing other items ect.

How can this be done with SQL?

hierarchy
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It might help if you could post some table definitions and example data

0 Likes 0 ·
Hi Kev,

I have tried to make a sample and description - Hope this help?

0 Likes 0 ·

1 Answer

·
Basilicoq13 avatar image
Basilicoq13 answered

An example is here - Item 120000000007 has BOMID 61381008501 which contains sub item 120000000025 and 120000000038. But the last item (120000000038) does also has a BOMID which is D120000000038. This BOMID contains sub item 130007000034 and 160007100193.


So I will like to sum up the hours from the lowest level and up to top level. In the real life case I have 10 levels. In this sample there are only 3 levels.


see sample here:

https://skioldgroup-my.sharepoint.com/:x:/g/personal/jen_skiold_com/EeCnaMOLxKFMkJHlKVGQRz8BFGheMYvrqW8jJJMnbec3bQ?e=eGRuVI

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What would be the total hours then for item 120000000007 ?
I'm confused by the hours on both main items and sub items

0 Likes 0 ·

Item 120000000025 has 0.3 hours when it's a main item, 0.225 Hours when it's part of BOMID 61381008501, but 0.15 hours when it's part of BOMID 61381008502




0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.