Routine help and suggestions required (Span of control)
I require some help regarding a routine coding.
I have created a hierarchy of employees. The users now require a span of control key figure base on a unique pattern within this hierarchy...
UserName Span of control
ABC is the main manager. ABC heads PQR, XYZ & DEF. PQR is the manager of STU. XYZ is the manager of 123 & 456. DEF is the manager of GHI.
1. JKL is the subordinate of GHI. This is 1st level of subordinates for ABC. GHI is the subordinate of DEF. This is 2nd level of subordinates for ABC. Then DEF is also the subordinate of ABC. So ABC gets a 1+1+1 = 3.
2. 123,456 are 2 subordinates of XYZ. This is 1st level. Then XYZ is the 2nd level for ABC. So ABC gets 1+1=2.
3. STU is the 1st level of subordinates. Then PQR is 2nd level of subordinates for ABC. So ABC gets 1+1 = 2.
Since 3 is the biggest count, the loop should populate 3 against ABC's span of control.
PQR, XYZ & GHI have only 1 level of subordinates so gets 1 span of control.
DEF has GHI & GHI has JKL as subordinates so gets a 2 span of control.
STU,123,456,JKL dont have any subordinates so gets 0 span of control.
I tried hard but was unable to code a loop that could populate such data in the DSO. This is different from the regular count of subordinates.
Is this logic even logical to code for?