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;
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 |