Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE INPUT_TABLE (ITEM,LOC,NEEDDATE,QTY) AS
SELECT 'ITEM100087','LOC000987',DATE '2024-04-01',1.38428 FROM DUAL UNION ALL
SELECT 'ITEM100087','LOC000987',DATE '2024-04-02',1.38428 FROM DUAL UNION ALL
SELECT 'ITEM100087','LOC000987',DATE '2024-04-03',1.38428 FROM DUAL UNION ALL
SELECT 'ITEM100087','LOC000987',DATE '2024-04-04',1.38428 FROM DUAL UNION ALL
SELECT 'ITEM100087','LOC000987',DATE '2024-04-05',1.38428 FROM DUAL UNION ALL
SELECT 'ITEM100087','LOC000987',DATE '2024-04-06',1.38428 FROM DUAL;
6 rows affected

SELECT t.*,
       TRUNC(
         SUM(qty) OVER (
           PARTITION BY item, loc
           ORDER BY needdate, ROWNUM
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         )
       )
       -
       COALESCE(
         TRUNC(
           SUM(qty) OVER (
             PARTITION BY item, loc
             ORDER BY needdate, ROWNUM
             ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
           )
         ),
         0
       ) AS newqty,
       MOD(
         SUM(qty) OVER (
           PARTITION BY item, loc
           ORDER BY needdate, ROWNUM
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ),
         1
       ) AS decimalqty
FROM   input_table t


ITEM
LOC
NEEDDATE
QTY
NEWQTY
DECIMALQTY


ITEM100087
LOC000987
2024-04-01 00:00:00
1.38428
1
.38428

ITEM100087
LOC000987
2024-04-02 00:00:00
1.38428
1
.76856

ITEM100087
LOC000987
2024-04-03 00:00:00
1.38428
2
.15284

ITEM100087
LOC000987
2024-04-04 00:00:00
1.38428
1
.53712

ITEM100087
LOC000987
2024-04-05 00:00:00
1.38428
1
.9214

ITEM100087
LOC000987
2024-04-06 00:00:00
1.38428
2
.30568


SELECT *
FROM   input_table
MODEL
  PARTITION BY (item, loc)
  DIMENSION BY (needdate)
  MEASURES (
    qty,
    0 AS newqty,
    0 AS decimalqty
  )  
  RULES AUTOMATIC ORDER (
    newqty[needdate]     = TRUNC(
                             qty[cv(needdate)]
                             + COALESCE(decimalqty[cv(needdate) - 1], 0)
                           ),
    decimalqty[needdate] = MOD(
                             qty[cv(needdate)]
                             + COALESCE(decimalqty[cv(needdate) - 1], 0),
                             1
                           )
  )


ITEM
LOC
NEEDDATE
QTY
NEWQTY
DECIMALQTY


ITEM100087
LOC000987
2024-04-01 00:00:00
1.38428
1
.38428

ITEM100087
LOC000987
2024-04-02 00:00:00
1.38428
1
.76856

ITEM100087
LOC000987
2024-04-03 00:00:00
1.38428
2
.15284

ITEM100087
LOC000987
2024-04-04 00:00:00
1.38428
1
.53712

ITEM100087
LOC000987
2024-04-05 00:00:00
1.38428
1
.9214

ITEM100087
LOC000987
2024-04-06 00:00:00
1.38428
2
.30568

ITEM	LOC	NEEDDATE	QTY	NEWQTY	DECIMALQTY
ITEM100087	LOC000987	2024-04-01 00:00:00	1.38428	1	.38428
ITEM100087	LOC000987	2024-04-02 00:00:00	1.38428	1	.76856
ITEM100087	LOC000987	2024-04-03 00:00:00	1.38428	2	.15284
ITEM100087	LOC000987	2024-04-04 00:00:00	1.38428	1	.53712
ITEM100087	LOC000987	2024-04-05 00:00:00	1.38428	1	.9214
ITEM100087	LOC000987	2024-04-06 00:00:00	1.38428	2	.30568