SQL - How to calculate opening and closing balance-CodePudding

I'm trying to figure out a way to calculate two new columns for the following model:

Link:

Can anyone share any examples of how I could achieve this?

CodePudding user response：

You can use SUM() window function and a CASE expression to check for the 1st transaction:

SELECT *,
       CASE 
         WHEN ROW_NUMBER() OVER (ORDER BY TRANSACTION_DATE, TRANSACTION_ID) = 1 THEN AMOUNT
         ELSE SUM(AMOUNT) OVER (ORDER BY TRANSACTION_DATE, TRANSACTION_ID) - AMOUNT 
       END OPENING_BALANCE,
       SUM(AMOUNT) OVER (ORDER BY TRANSACTION_DATE, TRANSACTION_ID) CLOSING_BALANCE
FROM TRANSATION_TABLE;

See the demo.

CodePudding user response：

select f.TRANSACTION_ID, f.TRANSACTION_DATE, f.AMOUNT,CASE WHEN sum(s.AMOUNT)-f.AMOUNT >0
   THEN sum(s.AMOUNT)-f.AMOUNT ELSE s.AMOUNT END AS OPENING_BALANCE,sum(s.AMOUNT) as CLOSING_BALANCE
from TRANSATION_TABLE f inner join TRANSATION_TABLE s on f.TRANSACTION_ID >= s.TRANSACTION_ID
   group by f.amount order by f.TRANSACTION_ID asc