How to find the find the 3 highest designation for a particular employee.
I am working on certain requirement, where we need to so all the employees directly or indirectly reporting to him. The very 1st step, i have created a hierarchical structure displaying the employee to till its highest level(in our case VP is the highest level).
Re: How to find the find the 3 highest designation for a particular employee.
This is usually the kind of problem that Recursive SQL was made for, but with your already flattened structure, that becomes no longer works. I guess you'd need to use a CASE statement.
CASE WHEN L7_mgr IS NOT NULL THEN L7_mgr WHEN L6_mgr IS NOT NULL THEN L6_mgr WHEN L5_mgr IS NOT NULL THEN L5_mgr WHEN L4_mgr IS NOT NULL THEN L4_mgr WHEN L3_mgr IS NOT NULL THEN L3_mgr WHEN L2_mgr IS NOT NULL THEN L2_mgr WHEN L1_mgr IS NOT NULL THEN L1_mgr END AS VP, CASE WHEN L7_mgr IS NOT NULL THEN L6_mgr WHEN L6_mgr IS NOT NULL THEN L5_mgr WHEN L5_mgr IS NOT NULL THEN L4_mgr ... END AS GM , ...