WITH
    T AS
        (SELECT '20170101' YMD, 'A' DPT, 1 CNT FROM DUAL
         UNION ALL
         SELECT '20170102' YMD, 'B' DPT, 2 CNT FROM DUAL
         UNION ALL
         SELECT '20170103' YMD, 'C' DPT, 3 CNT FROM DUAL
         UNION ALL
         SELECT '20170104' YMD, 'A' DPT, 1 CNT FROM DUAL
         UNION ALL
         SELECT '20180102' YMD, 'B' DPT, 2 CNT FROM DUAL
         UNION ALL
         SELECT '20180103' YMD, 'C' DPT, 3 CNT FROM DUAL
         UNION ALL
         SELECT '20180108' YMD, 'A' DPT, 5 CNT FROM DUAL
         UNION ALL
         SELECT '20180109' YMD, 'B' DPT, 6 CNT FROM DUAL
         UNION ALL
         SELECT '20180916' YMD, 'D' DPT, 77 CNT FROM DUAL)
  SELECT Y.THIS_YESTERDAY + 1 AS YMD
       , T.DPT
       , NVL(SUM(CASE WHEN TO_DATE(YMD, 'YYYYMMDD') BETWEEN Y.LAST_YEAR AND Y.LAST_YESTERDAY THEN CNT END), 0) LAST_YEAR_CNT
       , NVL(SUM(CASE WHEN TO_DATE(YMD, 'YYYYMMDD') BETWEEN Y.THIS_YEAR AND Y.THIS_YESTERDAY THEN CNT END), 0) THIS_YEAR_CNT
    FROM T
       , (    SELECT TRUNC(SYSDATE, 'YEAR')            THIS_YEAR
                   , TRUNC(SYSDATE) - LEVEL            THIS_YESTERDAY
                   , ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) LAST_YEAR
                   , ADD_MONTHS(TRUNC(SYSDATE), -12) - LEVEL LAST_YESTERDAY
                FROM DUAL
          CONNECT BY LEVEL <= 10) Y
GROUP BY Y.THIS_YESTERDAY, T.DPT
ORDER BY Y.THIS_YESTERDAY ASC, T.DPT ASC;

WITH t AS 
(
SELECT '20170101' ymd, 'A' dpt, 1 cnt FROM dual   
UNION ALL SELECT '20170102', 'B',  2 FROM dual 
UNION ALL SELECT '20170103', 'C',  3 FROM dual 
UNION ALL SELECT '20170104', 'A',  1 FROM dual 
UNION ALL SELECT '20180102', 'B',  2 FROM dual 
UNION ALL SELECT '20180103', 'C',  3 FROM dual 
UNION ALL SELECT '20180108', 'A',  5 FROM dual 
UNION ALL SELECT '20180109', 'B',  6 FROM dual
UNION ALL SELECT '20180916', 'D', 77 FROM dual
)
, t_ymd AS
(
SELECT TO_CHAR(                 dt                , 'yyyymmdd') ymd
     , TO_CHAR(TRUNC(ADD_MONTHS(dt    , -12), 'y'), 'yyyymmdd') symd_1
     , TO_CHAR(      ADD_MONTHS(dt - 1, -12)      , 'yyyymmdd') eymd_1
     , TO_CHAR(TRUNC(           dt          , 'y'), 'yyyymmdd') symd_2
     , TO_CHAR(                 dt - 1            , 'yyyymmdd') eymd_2
  FROM (SELECT TRUNC(sysdate) - LEVEL + 1 dt
          FROM dual
         CONNECT BY LEVEL <= 10
        )
)
, t_dpt AS
(
SELECT 'A' dpt FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
SELECT a.ymd
     , b.dpt
     , NVL(SUM(CASE WHEN c.ymd <= a.eymd_1 THEN cnt END), 0) cnt_1
     , NVL(SUM(CASE WHEN c.ymd >= a.symd_2 THEN cnt END), 0) cnt_2
  FROM t_ymd a
 CROSS JOIN t_dpt b
  LEFT OUTER JOIN t c
    ON b.dpt = c.dpt
   AND c.ymd BETWEEN a.symd_1 AND a.eymd_2
 GROUP BY a.ymd, b.dpt
 ORDER BY a.ymd, b.dpt
;