Database
Highlighted
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.

 EmpID Emp Name Mgr ID Level Calculation Output 28000 Mark NULL 1 Steve=6200 + Anna=6000 12200 28001 Steve 28000 2 Lorena=5000 + Simon=1200 6200 28002 Anna 28000 2 Peter=2500 + Paul=6000 8500 28008 Paul 28002 3 6000 6000 28004 Simon 28001 3 1200 1200 28007 Peter 28002 3 2500 2500 28003 Lorena 28001 3 Patrix=5000 5000 28005 Patrix 28003 4 Zoya=5000 5000 28006 Zoya 28005 5 5000 5000

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 4

## 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_ID Emp Name Mgr ID 28000 Mark NULL 28001 Steve 28000 28003 Lorena 28001 28005 Patrix 28003 28006 Zoya 28005 28004 Simon 28001 28002 Anna 28000 28007 Peter 28002 28008 Paul 28002

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.

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.