Recursive query with Rolling Data

Database
Enthusiast

Recursive query with Rolling Data

Hi All,

 

Along with the hierarchical data, I would like to get rolling total of the subordinates as given below, in case the managers have data, their salary can be added with the subordinates salaries, please help.

 

EmpIDEmp NameMgr IDLevelCalculationOutput
28000MarkNULL1Steve=6200 + Anna=600012200
28001Steve280002Lorena=5000 + Simon=12006200
28002Anna280002Peter=2500 + Paul=60008500
28008Paul28002360006000
28004Simon28001312001200
28007Peter28002325002500
28003Lorena280013Patrix=50005000
28005Patrix280034Zoya=50005000
28006Zoya28005550005000

 

Sample Data

 

CREATE MULTISET TABLE TMP_EMP_DATA
(
EMP_ID INTEGER,
MGR_ID INTEGER,
EMP_NAME VARCHAR(10),
SALARY decimal(38,2)
)
PRIMARY INDEX (EMP_ID,MGR_ID);

INSERT INTO TMP_EMP_DATA VALUES (28000,NULL,'Mark');
INSERT INTO TMP_EMP_DATA VALUES (28001,28000,'Steve');
INSERT INTO TMP_EMP_DATA VALUES (28002,28000,'Anna');
INSERT INTO TMP_EMP_DATA VALUES (28003,28001,'Lorena');
INSERT INTO TMP_EMP_DATA VALUES (28004,28001,'Simon',1200);
INSERT INTO TMP_EMP_DATA VALUES (28005,28003,'Patrix');
INSERT INTO TMP_EMP_DATA VALUES (28006,28005,'Zoya',5000);
INSERT INTO TMP_EMP_DATA VALUES (28007,28002,'Peter',2500);
INSERT INTO TMP_EMP_DATA VALUES (28008,28002,'Paul',6000);

 

Recursive Query

WITH RECURSIVE REC_EMP_CHILD
(
EMP_ID
, EMP_NAME
, MGR_ID
, lvl
)
AS
(
SELECT
A.EMP_ID
, A.EMP_NAME
, A.MGR_ID
, 1
FROM
TMP_EMP_DATA A
WHERE A.EMP_NAME = 'Mark'
UNION ALL
SELECT
A.EMP_ID
, A.EMP_NAME
, A.MGR_ID
, B.lvl + 1
FROM
TMP_EMP_DATA A
JOIN
REC_EMP_CHILD B
ON B.EMP_ID = A.MGR_ID
)
SELECT *
FROM REC_EMP_CHILD

4 REPLIES
Senior Apprentice

Re: Recursive query with Rolling Data

You need to traverse the hierarchy bottum-up, not top-down:

WITH RECURSIVE REC_EMP_CHILD
(
EMP_ID
, EMP_NAME
, MGR_ID
, lvl
, sal
)
AS
(
SELECT
  EMP_ID
, EMP_NAME
, MGR_ID
, 1
, Coalesce(SALARY, 0) AS sal
FROM
TMP_EMP_DATA A
WHERE NOT EXISTS 
(SELECT * FROM TMP_EMP_DATA AS B WHERE B.MGR_ID = A.EMP_ID)
--WHERE A.EMP_NAME = 'Mark'
UNION ALL
SELECT
  p.EMP_ID
, p.EMP_NAME
, p.MGR_ID
, c.lvl + 1
, c.SAL + Coalesce(p.SALARY,0)
FROM
TMP_EMP_DATA AS p
JOIN
REC_EMP_CHILD AS c
ON p.EMP_ID = c.MGR_ID
)
SELECT emp_id, emp_name, mgr_id, Sum(sal)
FROM REC_EMP_CHILD
GROUP BY 1,2,3

 

 

Enthusiast

Re: Recursive query with Rolling Data

Thank you so much 'dnoeth' !!!

 

Now, I would like the data sorted by their hierarchy, expecting it as given below.

 

Emp_IDEmp NameMgr ID
28000MarkNULL
28001Steve28000
28003Lorena28001
28005Patrix28003
28006Zoya28005
28004Simon28001
28002Anna28000
28007Peter28002
28008Paul28002

 

Please find the image below for additional reference, I'm fine if we need to write multiple queries for this, kindly help, thank you in advance.emp_hier.jpg

Enthusiast

Re: Recursive query with Rolling Data

Unfortunately, I'm not allowed to write Stored procedures or functions for this, this is restricted in our environment. Hence, I need to get this through SQL statements alone :(

Enthusiast

Re: Recursive query with Rolling Data

Forgot to mention, in the above output, I need along with Salary data as well.