Hierarchical query with Summary Data

Database
Enthusiast

Hierarchical query with Summary Data

 

Earlier, I got a solution from here 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 with Summary Data

Try Below Code,

 

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,
P.SALARY AS SALARY
FROM EMP_HIERARCHY_DATA P
INNER JOIN
REC_EMP_CHILD C
ON P.EMP_ID=C.MGR_ID
)
SELECT SQ.EMP_ID
, SQ.MGR_ID 
,CASE 	WHEN SQ.LVL=MIN(SQ.LVL) OVER (ORDER BY SQ.LVL ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) THEN SQ.SALARY 
		ELSE SUM(SQ.SALARY) OVER (ORDER BY SQ.LVL ROWS UNBOUNDED PRECEDING) 
END AS SALARY_SUM 
FROM
(SELECT DISTINCT A.* FROM REC_EMP_CHILD A)SQ 
ORDER BY SQ.MGR_ID;

Wiki