Hello,
I need a quick help to write an SQL (Oracle query):-
Here is the base table data:-
with t as (
select '2018-01' as period, 100 as expenses, 13 as charge_reversal, 5 as payout, 22 as payment_reversal from dual union all
select '2018-12', 115, 12, 13, 17 from dual union all
select '2018-02', 110, 15, 15, 6 from dual union all
select '2018-03', 70, 17, 7, 10 from dual union all
select '2019-02', 100, 17, 18, 27 from dual union all
select '2018-07', 130, 25, 20, 11 from dual union all
select '2018-11', 120, 16, 25, 30 from dual union all
select '2019-06', 150, 40, 25, 30 from dual
)
select * from t
order by period;
Output should be:-
| PERIOD | Beginning Balance | EXPENSES | CHARGE_REVERSAL | PAYOUT | PAYMENT_REVERSAL | Ending Balance |
|---|
| 2018-01 | 0 | 100 | 13 | 5 | 22 | 104 |
| 2018-02 | 104 | 110 | 15 | 15 | 6 | 190 |
| 2018-03 | 190 | 70 | 17 | 7 | 10 | 246 |
| 2018-07 | 246 | 130 | 25 | 20 | 11 | 342 |
| 2018-11 | 342 | 120 | 16 | 25 | 30 | 451 |
| 2018-12 | 451 | 115 | 12 | 13 | 17 | 558 |
| 2019-02 | 558 | 100 | 17 | 18 | 27 | 650 |
| 2019-06 | 650 | 150 | 40 | 25 | 30 | 765 |
Ending balance = Beginning_Balance + Expenses - charge_reversal - payout + payment_reversal
Beginning_Balance = Ending balance of previous period.
** For 1st record, Beginning_Balance will be zero.
I was able to perform self join as :-
where t1.ROW_NUMBER - 1 = t2.ROW_NUMBER(+)
however, I couldn't manage to write complete query to have beginning and end balance. I am using oracle 10g edition.
Any help will be much appreciated.
Regards,
Ritesh