Skip to Content
0
May 18, 2015 at 06:41 PM

Employee Hierarchy

38 Views

Hi All,

I am looking for SQL Script code(HANA) to take the input “EMPLoyee table available in the below format and give me the Desired output table .

Input Table EMP_MGR_HOD: All EMPLoyees with their immediate supervisors and HODs

“EMPL”_ID

EMP NAME

(All “EMPL”oyees in Org)

IMMEDIATE SUPERVISOR NAME

Dept Head

101

JOHN

JACK

Dept head 1

102

ANIL

JACOB

Dept head 1

103

CHRIS

Mike

Dept head 1

104

JACK

JOE

Dept head 1

105

JACOB

JOE

Dept head 1

106

JOE

JIM

Dept head 1

107

JIM

Jolly

Dept head 1

108

JOLLY

Dept head 1

Dept head 1

109

Dept head 1

CEO

Dept head 1

110

Dept head 2

CEO

Dept head 2

DESIRED OUTPUT: Traverse Employee Hierarchy from Top to Bottom

Dept Head(L1)

L2 Supervisor

L3 Supervisor

L4 Supervisor

L5..L6…

LOWEST LEVEL (All “EMPL”oyees in Org)

“EMPL”_ID

Dept head 1

JOLLY

JIM

JOE

JACK

JOHN

101

Dept head 1

JOLLY

JIM

JOE

JACOB

ANIL

102

Dept head 1

Mike

CHRIS

103

Dept head 1

JOLLY

JIM

JOE

JACK

104

Dept head 1

JOLLY

JIM

JOE

JACOB

105

Dept head 1

JOLLY

JIM

JOE

106

Dept head 1

JOLLY

JIM

107

Dept head 1

Dept head 1

JOLLY

108

Dept head 1

CEO

Dept head 1

109

Dept head 2

CEO

Dept head 2

110

Could you please help me with the SQL Script HANA CODE to achieve the above desired output.

I tried with the following code to trace the EMPLoyee hierarchy from lowest level to higher supervisor levels. But now I want to traverse the EMPLoyee hierarchy levels from TOP (Dept head level) to BOTTOM (EMPLoyee level).

SELECT AE.* ,

EL2.SUPERVISOR_NAME as LVL2_Supervisor,

EL3.SUPERVISOR_NAME as LVL3_Supervisor,

EL4.SUPERVISOR_NAME as LVL4_Supervisor

FROM “EMPL”.EMP_MGR_HOD as AE LEFT OUTER JOIN “EMPL”.EMP_MGR_HOD EL2

ON (AE. Supervisor_id = EL2.”EMPL”ID) LEFT OUTER JOIN “EMPL”.EMP_MGR_HOD EL3

ON (EL2.Supervisor_id = EL3.”EMPL”ID) LEFT OUTER JOIN “EMPL”.EMP_MGR_HOD EL4

ON (EL3.Supervisor_id = EL4.”EMPL”ID);

/* In the above code Employee is L0 , Immediate Supervisor is L1 which are directly above from input table EMP_MGR_HOD table */

The above mentioned script will work for 3 levels (L2,L3,L4) bottom up and we're trying to get all levels between dept head and employee (top down)

Please help us with the SQL Script HANA CODE to achieve the above desired output.