-
Hi
I am using
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
Letz say i have a table with the following structure..
--------------------------------------------------
EMPNO ReportingTo(manager) Eff.Date
--------------------------------------------------
1000 1001 1-Aug-01
1001 1000 1-Aug-01
1002 1001 1-Aug-01
1003 1002 1-Aug-01
1004 1000 1-Aug-01
1005 1003 1-Aug-01
1000 1003 1-Sep-01
1001 1004 1-Sep-01
1005 1001 20-aug-01
1003 9999 1-Sep-01
1004 1000 1-Sep-01
1005 1002 1-Sep-01
1000 1004 8-Sep-01
1005 1001 9-Sep-01
9999 1-jan-01
------------------------------------------
Now i wanted to find the tree structure for an given employee on a given date.
i.e. list of employees reporting to the given employee on a GIVEN DATE..
If i give
emp = 1005 and
date <=01-sep-2001
then i should get the tree (level) of the given employee (1005) and the max record (based on the date condition 01-sep-2001 ) of the employee reporting to the given employee !!!
i.e.
1005 1002
1002 1001
1001 1004
1004 1000
1000 1003
1003 9999
Rgds
Vijay.s
-
I'm not sure if this query is what you want:
select empno, reporting
from tablename
where effdate >= '01-SEP-01' AND empno <> 9999;
-
-
Try this:
select empno, reporting_to
from (select *
from your_table
where (empno,eff_date) in (select empno, max(eff_date)
from your_table
where eff_date <= to_date('01.09.2001')
group by empno))
connect by empno = prior reporting_to
start with empno = 1005
/
roger
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|