Db2 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH source (d_date, emp_id, dept_id, course_id, f_priority, s_priority, deleted, d_timestamp) AS
( VALUES ('20190419', 'E1', 'D1', 'C1', 'Low',      2, 0, '2019-04-19 12:10:18'),
         ('20190521', 'E1', 'D1', 'C2', 'Medium',   2, 0, '2019-05-21 12:10:18'),
         ('20190521', 'E1', 'D1', 'C3', 'High',     4, 0, '2019-05-21 14:10:18'),
         ('20190521', 'E1', 'D1', 'C4', 'High',     4, 0, '2019-05-21 14:11:18'),
         ('20190621', 'E1', 'D1', 'C3', 'High',     4, 1, '2019-06-21 15:10:18'),
         ('20190621', 'E1', 'D1', 'C5', 'High',     4, 0, '2019-06-21 16:10:18'),
         ('20191021', 'E1', 'D1', 'C6', 'High',     8, 0, '2019-06-21 16:10:18'),
         ('20200121', 'E1', 'D1', 'C7', 'Critical', 4, 0, '2019-05-21 14:10:18'),
         ('20200121', 'E1', 'D1', 'C8', 'Critical', 4, 0, '2019-05-21 14:11:18'),
         ('20190419', 'E2', NULL, 'C1', 'Low',      2, 0, '2019-04-19 12:10:18'),
         ('20190521', 'E2', NULL, 'C2', 'Medium',   2, 0, '2019-05-21 12:10:18'),
         ('20190521', 'E2', NULL, 'C3', 'High',     4, 0, '2019-05-21 14:10:18'),
         ('20190521', 'E2', NULL, 'C4', 'High',     4, 0, '2019-05-21 14:11:18'),
         ('20190531', 'E2', NULL, 'C2', 'Medium',   2, 1, '2019-05-31 15:01:18'),
         ('20190621', 'E2', 'D2', 'C3', 'High',     4, 0, '2019-06-21 15:10:18'),
         ('20190621', 'E2', 'D2', 'C5', 'High',     6, 0, '2019-06-21 16:10:18'),
         ('20190721', 'E2', 'D2', 'C5', 'High',     6, 1, '2019-06-21 15:10:18'),
         ('20191021', 'E2', 'D2', 'C7', 'Low',      8, 0, '2019-06-21 16:10:18')
), max_priorities (emp_id, dept_id, d_date, course_id, f_priority, s_priority, deleted, fs_ord_priority, max_fs_ord_priority) AS
( SELECT emp_id, dept_id, d_date, course_id, s.f_priority, s_priority, deleted, def.f_ord_priority*10+s_priority,
         MAX(def.f_ord_priority*10+s_priority) OVER (PARTITION BY emp_id, dept_id, d_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    FROM source s
      INNER JOIN (VALUES ('Low', 1), ('Medium', 2), ('High', 3), ('Critical', 4)) AS def(f_priority, f_ord_priority) ON s.f_priority = def.f_priority
), filtered_priorities (emp_id, dept_id, d_date, course_id, deleted, fs_ord_priority, entry_ord, entry_count, date_ord) AS
( SELECT emp_id, dept_id, d_date, course_id, deleted, fs_ord_priority,
         ROWNUMBER() OVER (PARTITION BY emp_id, dept_id ORDER BY d_date, course_id),
         COUNT(*) OVER (PARTITION BY emp_id, dept_id),
         COUNT(*) OVER (PARTITION BY emp_id, dept_id ORDER BY d_date)
    FROM max_priorities
    WHERE max_fs_ord_priority = fs_ord_priority
)
, recursive_courses (emp_id, dept_id, d_date, course_dtl, current_fs_ord_priority, entry_ord, entry_count, date_ord) AS
( SELECT emp_id, dept_id, d_date, CAST(',' || course_id AS VARCHAR(100)), fs_ord_priority, entry_ord, entry_count, date_ord
    FROM filtered_priorities
    WHERE entry_ord = 1
  UNION ALL
  SELECT n.emp_id, n.dept_id, n.d_date,
         CASE WHEN o.current_fs_ord_priority < n.fs_ord_priority THEN ',' || n.course_id
              WHEN o.current_fs_ord_priority > n.fs_ord_priority THEN o.course_dtl
              WHEN o.current_fs_ord_priority = n.fs_ord_priority AND n.deleted = 0 THEN o.course_dtl || ',' || n.course_id
              ELSE INSERT(o.course_dtl, LOCATE(','||n.course_id||',', o.course_dtl||','), LENGTH(','||n.course_id), '')
         END,
         CASE WHEN o.current_fs_ord_priority < n.fs_ord_priority THEN n.fs_ord_priority
              WHEN o.current_fs_ord_priority = n.fs_ord_priority AND n.deleted = 1 AND LOCATE(',', o.course_dtl, 2) = 0 THEN -1
              ELSE o.current_fs_ord_priority
         END,
         CASE WHEN o.current_fs_ord_priority > n.fs_ord_priority THEN -1 ELSE n.entry_ord END,
         n.entry_count, n.date_ord
    FROM recursive_courses o, filtered_priorities n
    WHERE (o.emp_id, COALESCE(o.dept_id, 'N/A'), o.entry_ord + 1) = (n.emp_id, COALESCE(n.dept_id, 'N/A'), n.entry_ord)
      AND n.entry_ord <= o.entry_count
)
SELECT emp_id, dept_id, CASE WHEN course_dtl != '' THEN SUBSTR(course_dtl, 2) END AS course_dtl,
       d_date AS s_date,
       COALESCE( INSERT(INSERT(CHAR(
         DATE(INSERT(INSERT(LEAD(d_date) OVER (PARTITION BY emp_id, dept_id ORDER BY d_date), 5, 0, '-'), 8, 0, '-')) - 1 DAY
                 ), 5, 1, ''), 7, 1, '')
                , '99991231') AS e_date
  FROM recursive_courses
  WHERE entry_ord = date_ord
  ORDER BY emp_id, COALESCE(dept_id, ''), d_date
;

EMP_ID	DEPT_ID	COURSE_DTL	S_DATE	E_DATE
E1	D1	C1	20190419	20190520
E1	D1	C3,C4	20190521	20190620
E1	D1	C4,C5	20190621	20191020
E1	D1	C6	20191021	20200120
E1	D1	C7,C8	20200121	99991231
E2	null	C1	20190419	20190520
E2	null	C3,C4	20190521	99991231
E2	D2	C5	20190621	20190720
E2	D2	null	20190721	20191020
E2	D2	C7	20191021	99991231