You don't need to consider the hierarchy in the data (assuming that there are no overlaps); you just need to aggregate:
SELECT emp_id,
SUM(to_date - from_date + 1) AS absence_days,
MIN(from_date) AS from_date,
MAX(to_date) AS to_date
FROM EMPLOYEE_ABSENCE_TAB
GROUP BY emp_id
Which, for the sample data:
CREATE TABLE EMPLOYEE_ABSENCE_TAB
(emp_id, absence_id, from_date, to_date, absence_continuation) AS
SELECT 100, 1, DATE '2022-01-01', DATE '2022-01-03', NULL FROM DUAL UNION ALL
SELECT 100, 2, DATE '2022-02-01', DATE '2022-02-05', 1 FROM DUAL UNION ALL
SELECT 200, 3, DATE '2022-01-01', DATE '2022-01-07', NULL FROM DUAL UNION ALL
SELECT 200, 4, DATE '2022-01-10', DATE '2022-01-14', 3 FROM DUAL UNION ALL
SELECT 200, 5, DATE '2022-01-16', DATE '2022-01-20', 4 FROM DUAL UNION ALL
SELECT 300, 6, DATE '2022-01-01', DATE '2022-01-14', NULL FROM DUAL;
Outputs:
| EMP_ID |
ABSENCE_DAYS |
FROM_DATE |
TO_DATE |
| 100 |
8 |
2022-01-01 00:00:00 |
2022-02-05 00:00:00 |
| 200 |
17 |
2022-01-01 00:00:00 |
2022-01-20 00:00:00 |
| 300 |
14 |
2022-01-01 00:00:00 |
2022-01-14 00:00:00 |
If you did want to consider the hierarchy and get the duration of each absence (and not the total absences per employee) then you can use:
SELECT emp_id,
absence_id,
SUM(to_date - from_date + 1) AS absence_days,
MIN(from_date) AS from_date,
MAX(to_date) AS to_date
FROM (
SELECT emp_id,
CONNECT_BY_ROOT absence_id AS absence_id,
to_date - from_date + 1 AS absence_days,
from_date,
to_date
FROM EMPLOYEE_ABSENCE_TAB
START WITH absence_continuation IS NULL
CONNECT BY PRIOR absence_id = absence_continuation
)
GROUP BY
emp_id,
absence_id
Which outputs the same (with an additional absence_id column for the id of the first part of the absence) for your sample data as you only have a single absence for each employee; if you had more absences then it would aggregate each absence separately.
db<>fiddle here
