Hierarchical Query - getting wrong Summary

Database
Enthusiast

Hierarchical Query - getting wrong Summary

Hi Dnoeth,

 

Earlier, you gave me a solution for retrieving Hierarchical output with the Summary data, I'm facing an issue in this now, please find the details below and kindly help me to overcome this.


create table emp_hierarchy_data
(emp_id varchar(10),
mgr_id varchar(10),
salary decimal(10,2))
primary index (emp_id);

 

insert into emp_hierarchy_data
values(10, NULL, 20000);

insert into emp_hierarchy_data
values(20, 10, 15000);

insert into emp_hierarchy_data
values(30, 20, 10000);

insert into emp_hierarchy_data
values(41, 30, 4000);

insert into emp_hierarchy_data
values(42, 30, 3000);

 

WITH RECURSIVE REC_Emp_Child
(emp_id, mgr_id, lvl, salary)
AS
(SELECT
emp_id, mgr_id, 1 as lvl,
Coalesce(salary, 0) as salary
FROM emp_hierarchy_data a
WHERE NOT EXISTS
(select * from emp_hierarchy_data b where b.mgr_id=a.emp_id)
UNION ALL
SELECT
p.emp_id , p.mgr_id, c.lvl + 1,
c.salary + Coalesce(p.salary, 0) as salary
FROM emp_hierarchy_data p
INNER JOIN
REC_Emp_Child c
ON p.emp_id=c.mgr_id)
SELECT *
FROM REC_Emp_Child
ORDER BY emp_id, mgr_id;

 

The above query, adds Employee Salary with Manager salary multiple times, depending upon the number of employees, he has got, this is incorrect.

 

emp_id mgr_id lvl salary
1 10 ? 4 49,000.00
2 10 ? 4 48,000.00
3 20 10 3 28,000.00
4 20 10 3 29,000.00
5 30 20 2 14,000.00
6 30 20 2 13,000.00
7 41 30 1 4,000.00
8 42 30 1 3,000.00

 

Eventually, the below query too returns the wrong output.

 

WITH RECURSIVE REC_Emp_Child
(emp_id, mgr_id, lvl, salary)
AS
(SELECT
emp_id, mgr_id, 1 as lvl,
Coalesce(salary, 0) as salary
FROM emp_hierarchy_data a
WHERE NOT EXISTS
(select * from emp_hierarchy_data b where b.mgr_id=a.emp_id)
UNION ALL
SELECT
p.emp_id , p.mgr_id, c.lvl + 1,
c.salary + Coalesce(p.salary, 0) as salary
FROM emp_hierarchy_data p
INNER JOIN
REC_Emp_Child c
ON p.emp_id=c.mgr_id)
SELECT emp_id, mgr_id,
SUM(salary) as salary
FROM REC_Emp_Child
GROUP BY emp_id, mgr_id
ORDER BY emp_id, mgr_id;

 

emp_id mgr_id salary
1 10 ? 97,000.00
2 20 10 57,000.00
3 30 20 27,000.00
4 41 30 4,000.00
5 42 30 3,000.00

 

The expected output is as shown below.

 

emp_id mgr_id salary
10 ? 52,000.00
20 10 32,000.00
30 20 17,000.00
41 30 4,000.00
42 30 3,000.00

1 REPLY
Enthusiast

Re: Hierarchical Query - getting wrong Summary

Anyone else has any thoughts on this, kindly share the same, thank you.