Hierarchy level delay calculation

[shiveshsingh]

Hi All, 

Please help in sorting out one logic.

Input data

Doc Id	Finished Good	Component	Level	Category	sort Order	Delay
1	167	167	0	Main	1	10
1	167	168	1	Child	2	12
1	167	169	1	Child	3	15
1	167	170	2	Child	4	14
1	167	171	2	Child	5	9
1	167	172	1	Child	6	8
1	167	173	2	Child	7	7
1	167	174	2	Child	8	8
2	167	167	0	Main	10	3
2	167	190	1	Child	11	4
2	167	191	1	Child	12	7
2	167	192	1	Child	13	9
2	167	193	2	Child	14	2
2	167	194	2	Child	15	4

Out Put Data

Doc Id	Finished Good	Component	Level	Category	sort Order	Delay	Level Delay	Total Delay
1	167	167	0	Main	1	10	29	39
1	167	168	1	Child	2	12	0	12
1	167	169	1	Child	3	15	14	29
1	167	170	2	Child	4	14	0	14
1	167	171	2	Child	5	9	0	9
1	167	172	1	Child	6	8	8	16
1	167	173	2	Child	7	7	0	7
1	167	174	2	Child	8	8	0	8
2	167	167	0	Main	10	3	13	16
2	167	190	1	Child	11	4	0	4
2	167	191	1	Child	12	7	0	7
2	167	192	1	Child	13	9	4	13
2	167	193	2	Child	14	2	0	2
2	167	194	2	Child	15	4	0	4

Data Understanding:

Data is doc id and Finished good wise and sorted based on parent child relation ship.

if you look on the levels, you can see the parent child relationship.

we have to start from the lowest level of child for a particular doc id and Finished good combination. So sorting is important

Let's check the i/p data.

for doc id = 1 and finished good = 167, lowest sort order is 8, that means it is the lowest level of child.

Now start calculating Level Delay, if there is no level below , it means it is 0. and total delay will be Delay + Level Delay.

This is for child.

If you move, one level up - you can see level as 2, so it is again child and not parent - so total delay = Delay + Level Delay.

moving one level up , level is 1. So this is parent for below 2 levels. Now for this, Level delay will be maximum of Total delay from it's child.

this way, we have to move up in the calculation.

Any help would be appreciated.

[shiveshsingh]

@sunny_talwar , @Anil_Babu_Samineni  - Please help