By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name ( "Date", credit, debit ) AS
SELECT Date '2021-01-01', 1000, 0 FROM DUAL UNION ALL
SELECT Date '2021-01-02', 0, 500 FROM DUAL UNION ALL
SELECT Date '2021-01-03', 0, 700 FROM DUAL;
3 rows affected
SELECT "Date",
credit,
debit,
SUM(credit-debit) OVER (ORDER BY "Date") AS balance
FROM table_name
| Date | CREDIT | DEBIT | BALANCE |
|---|---|---|---|
| 01-JAN-21 | 1000 | 0 | 1000 |
| 02-JAN-21 | 0 | 500 | 500 |
| 03-JAN-21 | 0 | 700 | -200 |
DROP TABLE table_name;
CREATE TABLE table_name ( "Date", account_balance ) AS
SELECT Date '2021-01-01', +1000 FROM DUAL UNION ALL
SELECT Date '2021-01-02', + 500 FROM DUAL UNION ALL
SELECT Date '2021-01-03', - 200 FROM DUAL;
3 rows affected
SELECT "Date",
GREATEST(
account_balance - LAG(account_balance, 1, 0) OVER (ORDER BY "Date"),
0
) AS credit,
GREATEST(
LAG(account_balance, 1, 0) OVER (ORDER BY "Date") - account_balance,
0
) AS debit,
account_balance
FROM table_name
| Date | CREDIT | DEBIT | ACCOUNT_BALANCE |
|---|---|---|---|
| 01-JAN-21 | 1000 | 0 | 1000 |
| 02-JAN-21 | 0 | 500 | 500 |
| 03-JAN-21 | 0 | 700 | -200 |