AlHal2
asked on
Retrieve data for an employee, their immediate reports and hierarchy up to the CEO
Using this table as an example, I'd like to be able to enter an employee ID. The resulting SQL should give me the employee's direct reports. It should also give me the employee's chain of command up to the President. I'm using SQL 2012.
create table ##EMPLOYEES
(EmpID char(4) unique Not null,
Ename varchar(10),
Job varchar(9),
MGR char(4),
Hiredate date,
Salary decimal(7,2),
Comm decimal(7,2),
DeptNo char(2) not null,
Primary key(EmpID),
--Foreign key(DeptNo) REFERENCES DEPARTMENTS(DeptNo)
);
--The following is my INSERT script:
insert into ##EMPLOYEES values (7839,'King','President',null,'17-Nov-11',5000,null,10);
insert into ##EMPLOYEES values (7698,'Blake','Manager',7839,'01-May-11',2850,null,30);
insert into ##EMPLOYEES values (7782,'Clark','Manager',7839,'02-Jun-11',2450,null,10);
insert into ##EMPLOYEES values (7566,'Jones','Manager',7839,'02-Apr-11',2975,null,20);
insert into ##EMPLOYEES values (7654,'Martin','Salesman',7698,'28-Feb-12',1250,1400,30);
insert into ##EMPLOYEES values (7499,'Allen','Salesman',7698,'20-Feb-11',1600,300,30);
insert into ##EMPLOYEES values (7844,'Turner','Salesman',7698,'08-Sep-11',1500,0,30);
insert into ##EMPLOYEES values (7900,'James','Clerk',7698,'22-Feb-12',950,null,30);
insert into ##EMPLOYEES values (7521,'Ward','Salesman',7698,'22-Feb-12',1250,500,30);
insert into ##EMPLOYEES values (7902,'Ford','Analyst',7566,'03-Dec-11',3000,null,20);
insert into ##EMPLOYEES values (7369,'Smith','Clerk',7902,'17-Dec-10',800,null,20);
insert into ##EMPLOYEES values (7788,'Scott','Analyst',7566,'09-Dec-12',3000,null,20);
insert into ##EMPLOYEES values (7876,'Adams','Clerk',7788,'12-Jan-10',1100,null,20);
insert into ##EMPLOYEES values (7934,'Miller','Clerk',7782,'23-Jan-12',1300,null,10);
ASKER
In the above, King's ID is 7839. He is the president of the company and is the first row to be inserted.
The next 3 rows are managers. You'll see the 4th column for all these managers is 7839 ie they report to the president.
Does this clarify?
The next 3 rows are managers. You'll see the 4th column for all these managers is 7839 ie they report to the president.
Does this clarify?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks both.
we can't see a hierarchy from the data you have provided above.