WITH emp(idx, dept_name, dept_seq, pos_name, pos_seq, user_name) AS
(
SELECT 1234, '인사팀', '001', '인사팀장', 1, '강호동' FROM dual UNION ALL
SELECT 1234, '홍보팀', '002', '사원'    , 2, '강호동' FROM dual UNION ALL
SELECT 1234, '기획팀', '003', '부팀장'  , 3, '강호동' FROM dual UNION ALL
SELECT 5678, '인사팀', '001', '사원'    , 1, '유재석' FROM dual UNION ALL
SELECT 7890, '기획팀', '003', '사원'    , 1, '이수근' FROM dual
)
, abs(idx, row_seq, pos_seq, regency, absence_sdate, absence_edate) AS
(
SELECT 1234, 1, 1, 5678, '2019-05-08 08:00', '2019-05-08 19:00' FROM dual UNION ALL
SELECT 1234, 2, 3, 7890, '2019-05-08 08:00', '2019-05-08 19:00' FROM dual
)
SELECT *
  FROM (SELECT a.idx
             , a.user_name
             , a.dept_name
             , a.pos_seq
             , a.pos_name
             , c.user_name regency
             , MIN(b.absence_sdate ||'~'|| b.absence_edate) OVER(PARTITION BY a.idx) absence_date
          FROM emp a
          LEFT OUTER JOIN abs b
            ON a.idx = b.idx
           AND a.pos_seq = b.pos_seq
           AND b.absence_sdate < TO_CHAR(sysdate + 1, 'yyyy-mm-dd')
           AND b.absence_edate > TO_CHAR(sysdate    , 'yyyy-mm-dd')
          LEFT OUTER JOIN emp c
            ON b.regency = c.idx
        )
 WHERE absence_date != '~'
 ORDER BY idx, pos_seq
;