I have the requirement of getting the parent,child relationship.For different period the relationship will change.So for passing input date on that day what is the hierarchy that I need to get.I need result as one lower level from input Id and all upper level.
My Table having below details,
create table test3 (id Number, parent_id Number,effective_From date,effective_to date);
Insert into test3 values(1,6,'01-JAN-2016','31-MAR-2016');
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');
Insert into test3 values(7,3,'01-JAN-2016','31-DEC-2016');
If I pass employee 2 and date 1-jan-2016 then expected result is
ID parent_ID level
6 6 2 --First Upper level
1 6 2 --Second Upper level
2 1 3 --Current level 2
3 2 4 -- First lower level.
4 2 4 -- First lower level.
With the below query I am getting the result up to this level.
with
lower_level1 as (
select parent_id, effective_From from test3
where parent_id = :id
and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
between effective_From and effective_to
),
lower_level as (
Select * From lower_level1
Union
select parent_id, effective_From from test3
where parent_id = :id
and effective_to =(Select max(effective_to)
from test3
where parent_id = :id
and effective_to < to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') )
and 0 = (select count(1) from lower_level1)
),
data as (
select distinct id, parent_id, level lvl,effective_From , effective_to
from test3
Where to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
between effective_From and effective_to
connect by prior parent_id = id
and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
between effective_From and effective_to
start with (parent_id, effective_From) in (select * from lower_level)
or (id, effective_From) in
(select id, effective_From from test3
where id = :id
and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
between effective_From and effective_to
and not exists (select null from lower_level))
),
lmax as (select max(lvl) lmax from data)
select id, parent_id, lmax-lvl+2 "LEVEL",effective_From , effective_to
from data,lmax
union all
select parent_id, parent_id, 1,effective_From , effective_to
from data
where lvl = (select lmax from lmax)
order by 3, 1
Now I need one more help similar to that requirement.I tried explain the scenario below.Could anyone please help me on this.
In the same way I need to get the hierarchy,but for given date and id if there is no parent or child then get the recent parent/Child relationship previous to given date(Not the feature date datas from given date).
create table test3 (id Number, parent_id Number,effective_From date,effective_to date);
Insert into test3 values(1,2,'01-Apr-2016','31-May-2016');
Insert into test3 values(3,1,'01-Jan-2016','31-Mar-2016');
Insert into test3 values(2,1,'01-Jan-2016','31-Mar-2016');
Insert into test3 values(2,3,'01-Apr-2016','31-May-2016');
Insert into test3 values(5,2,'01-Jun-2016','30-Dec-2016');
Insert into test3 values(6,5,'01-Aug-2016','30-Sep-2016');
Insert into test3 values(7,6,'01-oct-2016','30-nov-2016');
My ID 6 and input date 01-Dec-2016
I need output like
ID 1 -Parent ID 2 - Fourth upper Level
ID 3 -Parent ID 1 - Third upper Level
ID 2 -Parent ID 3 - Second upper Level.For ID 2 having two different parent_Id and Id 3 is recent Head ID for ID 1.
ID 5 -Parent ID 2 - First upper Level.This is active.
ID 6 -Parent ID 5 - Current Level.This is not active on dec but this is recent relation.
ID 7 -Parent ID 6 - First Lower level,This is not active on dec but this is recent relation.
Please reply me if need further input.
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0