WITH tbl_shift(shift_cd, day_type, start_time, sart_time_day_type, end_time, end_time_day_type) AS
(
          SELECT 'FRI1_S1', 'FRI', '0630', 'D', '1500', 'D'  FROM dual
UNION ALL SELECT 'FRI1_S2', 'FRI', '1500', 'D', '2300', 'D'  FROM dual
UNION ALL SELECT 'FRI1_S3', 'FRI', '2300', 'D', '0630', 'D1' FROM dual
UNION ALL SELECT 'NOR1_S1', 'NOR', '0630', 'D', '1430', 'D'  FROM dual
UNION ALL SELECT 'NOR1_S2', 'NOR', '1430', 'D', '2230', 'D'  FROM dual
UNION ALL SELECT 'NOR1_S3', 'NOR', '2230', 'D', '0630', 'D1' FROM dual
UNION ALL SELECT 'SAT6_S1', 'SAT', '0630', 'D', '1130', 'D'  FROM dual
UNION ALL SELECT 'SAT6_S2', 'SAT', '1130', 'D', '1630', 'D'  FROM dual
UNION ALL SELECT 'SAT6_S3', 'SAT', '1630', 'D', '2130', 'D'  FROM dual
)
, tbl_work_plan(plan_work_dt, item_cd, plant_qty, result_qty, comp_yn) AS
(
          SELECT '20240717', 'ITEM_A', 120, 0, 'N' FROM dual
UNION ALL SELECT '20240717', 'ITEM_B', 120, 0, 'N' FROM dual
UNION ALL SELECT '20240718', 'ITEM_A', 120, 0, 'N' FROM dual
UNION ALL SELECT '20240718', 'ITEM_B', 120, 0, 'N' FROM dual
UNION ALL SELECT '20240719', 'ITEM_A', 120, 0, 'N' FROM dual
UNION ALL SELECT '20240719', 'ITEM_C', 110, 0, 'N' FROM dual
)
, tbl_work_result(work_ymd, work_hms, item_cd, result_qty) AS
(
          SELECT '20240717', '063832', 'ITEM_A', 10 FROM dual
UNION ALL SELECT '20240717', '063925', 'ITEM_B', 12 FROM dual
UNION ALL SELECT '20240717', '064002', 'ITEM_A',  2 FROM dual
UNION ALL SELECT '20240717', '091002', 'ITEM_A', 48 FROM dual
UNION ALL SELECT '20240717', '093020', 'ITEM_A', 24 FROM dual
UNION ALL SELECT '20240717', '094002', 'ITEM_B', 24 FROM dual
UNION ALL SELECT '20240717', '134002', 'ITEM_A', 12 FROM dual
UNION ALL SELECT '20240717', '142000', 'ITEM_B', 12 FROM dual
UNION ALL SELECT '20240717', '230000', 'ITEM_A',  2 FROM dual
UNION ALL SELECT '20240717', '235021', 'ITEM_B', 20 FROM dual
UNION ALL SELECT '20240718', '002000', 'ITEM_A', 12 FROM dual
UNION ALL SELECT '20240718', '032300', 'ITEM_B', 20 FROM dual
UNION ALL SELECT '20240718', '042240', 'ITEM_B', 10 FROM dual
UNION ALL SELECT '20240718', '062258', 'ITEM_A', 10 FROM dual
UNION ALL SELECT '20240718', '063140', 'ITEM_B', 13 FROM dual
UNION ALL SELECT '20240718', '063640', 'ITEM_B', 12 FROM dual
UNION ALL SELECT '20240718', '064240', 'ITEM_A', 10 FROM dual
UNION ALL SELECT '20240718', '065240', 'ITEM_A', 10 FROM dual
UNION ALL SELECT '20240718', '063832', 'ITEM_A', 10 FROM dual
UNION ALL SELECT '20240718', '063925', 'ITEM_B', 12 FROM dual
UNION ALL SELECT '20240718', '064002', 'ITEM_A',  2 FROM dual
UNION ALL SELECT '20240718', '091002', 'ITEM_A', 48 FROM dual
UNION ALL SELECT '20240718', '093020', 'ITEM_A', 24 FROM dual
UNION ALL SELECT '20240718', '094002', 'ITEM_B', 24 FROM dual
UNION ALL SELECT '20240718', '134002', 'ITEM_A', 12 FROM dual
UNION ALL SELECT '20240718', '142000', 'ITEM_B', 12 FROM dual
UNION ALL SELECT '20240718', '230000', 'ITEM_A',  2 FROM dual
UNION ALL SELECT '20240718', '235021', 'ITEM_B', 20 FROM dual
UNION ALL SELECT '20240718', '002000', 'ITEM_A', 12 FROM dual
UNION ALL SELECT '20240718', '032300', 'ITEM_B', 20 FROM dual
UNION ALL SELECT '20240718', '042240', 'ITEM_B', 10 FROM dual
UNION ALL SELECT '20240718', '062258', 'ITEM_A', 10 FROM dual
UNION ALL SELECT '20240718', '063140', 'ITEM_A', 13 FROM dual
UNION ALL SELECT '20240718', '063640', 'ITEM_C', 12 FROM dual
UNION ALL SELECT '20240718', '064240', 'ITEM_C', 10 FROM dual
UNION ALL SELECT '20240718', '065240', 'ITEM_A', 10 FROM dual
)
SELECT a.plan_work_dt, a.item_cd, a.plant_qty
     , SUM(b.result_qty) result_qty
     , CASE WHEN SUM(b.result_qty) >= a.plant_qty THEN 'Y' ELSE 'N' END comp_yn
  FROM (SELECT a.plan_work_dt, a.item_cd, a.plant_qty
             , MIN(DECODE(NVL(c.sart_time_day_type, b.sart_time_day_type)
                   , 'D', a.dt, 'D1', a.dt1) || NVL(c.start_time, b.start_time) || '00') stm
             , MAX(DECODE(NVL(c.end_time_day_type , b.end_time_day_type)
                   , 'D', a.dt, 'D1', a.dt1) || NVL(c.end_time  , b.end_time  ) || '00') etm
          FROM (SELECT plan_work_dt, item_cd, plant_qty
                     , TO_CHAR(TO_DATE(plan_work_dt, 'yyyymmdd')  , 'yyyymmdd') dt
                     , TO_CHAR(TO_DATE(plan_work_dt, 'yyyymmdd')+1, 'yyyymmdd') dt1
                     , TO_CHAR(TO_DATE(plan_work_dt, 'yyyymmdd'), 'DY', 'nls_date_language=American') day_type
                  FROM tbl_work_plan a
                ) a
          LEFT OUTER JOIN tbl_shift b
            ON a.day_type = b.day_type
          LEFT OUTER JOIN tbl_shift c
            ON NVL2(b.day_type, '', 'NOR') = c.day_type
         GROUP BY a.plan_work_dt, a.item_cd, a.plant_qty
        ) a
  LEFT OUTER JOIN tbl_work_result b
    ON a.item_cd = b.item_cd
   AND b.work_ymd || b.work_hms >= a.stm
   AND b.work_ymd || b.work_hms <  a.etm
 GROUP BY a.plan_work_dt, a.item_cd, a.plant_qty
 ORDER BY 1, 2
;