sqloracle-databasewindow-functionsoracle21c

How to subtract the value from previous value


I have a table like this:

DT PRIN INT
01-01-2023 100000 1100
01-02-2023 100000 1200
01-03-2023 100000 1500
01-04-2023 100000 2300

I need to subtract INT column from PRIN and get the subtracted amount in first row.
On second and following rows I need to also subtract INT values from all preceding rows.

The result should look like this:

DT PRIN INT BAL
01-01-2023 100000 1100 98900
01-02-2023 100000 1200 97700
01-03-2023 100000 1500 96200
01-04-2023 100000 2300 93900

I have tried using LEAD and LAG functionality but cannot get subtraction correctly and this should be done in SQL.

Here is the script:

create table tbl9 ( dt date, prin varchar2(20), int varchar2(20));
insert into tbl9 values(to_date('01-01-2023','dd-mm-yyyy'),100000, 1100);
insert into tbl9 values(to_date('01-02-2023','dd-mm-yyyy'),100000, 1200);
insert into tbl9 values(to_date('01-03-2023','dd-mm-yyyy'),100000, 1500);
insert into tbl9 values(to_date('01-04-2023','dd-mm-yyyy'),100000, 2300);
commit;

Solution

  • You can use a Window Function SUM() for that

    SELECT dt,prin, int, prin - SUM(int) OVER(PARTITION BY prin ORDER BY dt) bal
    FROM tbl9
    
    DT PRIN INT BAL
    01-JAN-23 100000 1100 98900
    01-FEB-23 100000 1200 97700
    01-MAR-23 100000 1500 96200
    01-APR-23 100000 2300 93900

    fiddle