WITH JO_MAIN AS
(
SELECT 'A' AS KEY_VALUE, 1000 AS QTY FROM DUAL
UNION SELECT 'B' AS KEY_VALUE, 1500 AS QTY FROM DUAL
UNION SELECT 'C' AS KEY_VALUE, 2000 AS QTY FROM DUAL
)
, JO_DETAIL AS
(
SELECT '20100510' AS DAY_DATE, 500 AS DAY_QTY FROM DUAL UNION
SELECT '20100511' AS DAY_DATE, 600 AS DAY_QTY FROM DUAL UNION
SELECT '20100512' AS DAY_DATE, 700 AS DAY_QTY FROM DUAL UNION
SELECT '20100513' AS DAY_DATE, 300 AS DAY_QTY FROM DUAL UNION
SELECT '20100514' AS DAY_DATE, 500 AS DAY_QTY FROM DUAL
)
SELECT key_value ,
day_date ,
day_qty
FROM
(SELECT b.key_value ,
a.day_date ,
a.day_qty ,
CASE WHEN Sum(a.day_qty) over (PARTITION BY b.key_value ORDER BY a.day_date) - a.day_qty BETWEEN b.s_qty - b.qty AND s_qty
THEN s_qty END gb,
b.s_qty
FROM JO_DETAIL a, (SELECT key_value ,
qty ,
Sum(qty) over (ORDER BY key_value) s_qty
FROM JO_MAIN) b
)
WHERE gb = s_qty

WITH jo_main AS
(
SELECT 'A' key_value, 1000 qty FROM dual
UNION ALL SELECT 'B', 1000 FROM dual
UNION ALL SELECT 'C', 1000 FROM dual
)
, jo_detail AS
(
SELECT '20100510' day_date, 500 day_qty FROM dual
UNION ALL SELECT '20100511', 600 FROM dual
UNION ALL SELECT '20100512', 700 FROM dual
UNION ALL SELECT '20100513', 300 FROM dual
UNION ALL SELECT '20100514', 500 FROM dual
)
SELECT key_value, qty
, day_date, day_qty
FROM (SELECT key_value, qty, SUM(qty) OVER(ORDER BY key_value) s_qty FROM jo_main) a,
(SELECT day_date, day_qty, SUM(day_qty) OVER(ORDER BY day_date) s_day_qty FROM jo_detail) b
WHERE s_day_qty - day_qty >= s_qty - qty
AND s_day_qty - day_qty < s_qty
ORDER BY key_value, day_date
;