WITH base_t AS ( SELECT '2020-05-01' dt, '01:00' tm, 1 v FROM dual UNION ALL SELECT '2020-05-02', '02:00', 2 FROM dual UNION ALL SELECT '2020-05-02', '02:10', 2 FROM dual -- 일자 중복 UNION ALL SELECT '2020-05-03', '03:00', 3 FROM dual UNION ALL SELECT '2020-05-04', '04:00', 4 FROM dual UNION ALL SELECT '2020-05-05', '05:00', 5 FROM dual UNION ALL SELECT '2020-05-06', '06:00', 6 FROM dual UNION ALL SELECT '2020-05-07', '07:00', 7 FROM dual UNION ALL SELECT '2020-05-08', '08:00', 8 FROM dual UNION ALL SELECT '2020-05-09', '09:00', 9 FROM dual UNION ALL SELECT '2020-05-09', '09:10', 9 FROM dual -- 일자 중복 UNION ALL SELECT '2020-05-09', '09:20', 9 FROM dual -- 일자 중복 UNION ALL SELECT '2020-05-10', '10:00', 10 FROM dual UNION ALL SELECT '2020-05-11', '11:00', 11 FROM dual UNION ALL SELECT '2020-05-12', '12:00', 12 FROM dual UNION ALL SELECT '2020-05-13', '13:00', 13 FROM dual UNION ALL SELECT '2020-05-14', '14:00', 14 FROM dual UNION ALL SELECT '2020-05-15', '15:00', 15 FROM dual UNION ALL SELECT '2020-05-16', '16:00', 16 FROM dual UNION ALL SELECT '2020-05-17', '17:00', 17 FROM dual UNION ALL SELECT '2020-05-18', '18:00', 18 FROM dual UNION ALL SELECT '2020-05-19', '19:00', 19 FROM dual UNION ALL SELECT '2020-05-20', '20:00', 20 FROM dual UNION ALL SELECT '2020-05-21', '21:00', 21 FROM dual ) , rat_t AS ( SELECT '2020-05-09' dt, 120 r FROM dual UNION ALL SELECT '2020-05-19', 200 FROM dual ) SELECT a.dt , a.tm , a.v , a.v * NVL(ROUND(EXP(SUM(LN(b.r / 100))), 2), 1) x FROM base_t a , rat_t b WHERE a.dt BETWEEN '2020-05-07' AND '2020-05-19' AND b.dt(+) > a.dt AND b.dt(+) <= '2020-05-19' GROUP BY a.dt, a.tm, a.v ORDER BY a.dt, a.tm ;