OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query Help [message #656701]

Sat, 15 October 2016 00:01

SADASIVAM
Messages: 16
Registered: October 2016

Junior Member

Hi,
I need to write query to get employee manager relationship Hierarchy.

I have below two tables.

create table test2 (id Number) ;
 
Insert into test2 values(1); --Top parent
Insert into test2 values(5); --Top parent
Insert into test2 values(2);
Insert into test2 values(3);
Insert into test2 values(4);
 
create table test3 (id Number, parent_id Number,effective_From date,effective_to date);
 
Insert into test3 values(2,1,'01-JAN-2016','31-MAR-2016');
Insert into test3 values(2,5,'01-APR-2016','31-DEC-2016');
Insert into test3 values(3,2,'01-JAN-2016','31-DEC-2016');
Insert into test3 values(4,2,'01-JAN-2016','31-DEC-2016');

Table test2 having all employee IDs and employee itself may be manager

select * from test2;
        ID
----------
         1  --Manager
         5  --Manager
         2  --1st level employee (Team Lead)
         3  --2nd level employee
         4  --2nd level employee

--In test3 table having employee and his curresponding manager linkage.But if the employee is high level manager
-- and if whe dont have any further manager then that data wont be available in this linkage table(assuming manager of himself).
--Employee 1 and 5 dont have any further manager so those employee ID wont be here.

select * from test3;        
 
        ID  PARENT_ID EFFECTIVE_FROM EFFECTIVE_TO
---------- ---------- -------------- ------------
         2          1 01-JAN-16      31-MAR-16   --Employee 2 working under manager 1 between 01-JAN-16 and 31-MAR-16
         2          5 01-APR-16      31-DEC-16   --Employee 2 then changed to manager 5 between01-APR-16 and 31-DEC-16
         3          2 01-JAN-16      31-DEC-16   --employee 3 working under employee 2 between 01-JAN-16 and 31-DEC-16
         4          2 01-JAN-16      31-DEC-16   --employee 4 also working under employee 2 between 01-JAN-16 and 31-DEC-16

Now my requirement is , if I pass specific employee ID and date to my query it should return the employee and who is his manager in that specific date in hierarchy. That hierarchy should be current,all upper level and one lower level.
For example,
If I pass employee 1 and date 1-jan-2016 then expected result is

ID parent_ID level
---   ----    ----
1     1       1     --Current level parent himself
2     1       2     --one level lower .Here No upper level

If I pass employee 2 and date 1-jan-2016 then expected result is

ID parent_ID level
1     1       1     --Upper level is 1
2     1       2     --Current level  2
3     2       3    -- Current level 3.Emploee 2 is manager of employee 3.
4     2       3     --Current level 3..Emploee 2 is also manager of employee 4.

If I pass employee 3 and date 1-jan-2016 then expected result is

ID parent_ID level
1     1       1     --First upper level 1
2     1       2     --Second upper level  2
3     2       3    -- Current level 3.Emploee 2 is manager of employee 3.FOr him no further lower level.

Sameway If I pass employee 1 and date 1-Apr-2016 then expected result is and so on.

ID parent_ID level
5     5       1     --Current level parent himself
2     1       2     --one level lower .Here No upper level.

I tried to join these two tables,but its not not working as one table dont have data for sometime.Please help me to write this query.

 with upper as (   
  select parent_id, A.id,   
    max(level) over() - level + 1 lvl   
  from test2   A, test3 B
  WHERE A.ID=B.ID
  AND :v_date between effective_From AND effective_to
  start with A.id = :n   
  connect by A.id = prior parent_id   
  order by 3   
)   
select * from upper   
union all   
select parent_id, A.id,   
  (select max(lvl) from upper) +1 lvl   
from test2   A, test3 B   
WHERE A.ID=B.ID
AND :v_date between effective_From AND effective_to
AND parent_id = :n;

Thanks in advance.

[Edit MC: add code tags]

[Updated on: Sat, 15 October 2016 01:12] by Moderator