I want to present bank transactions for passbook from given data?-CodePudding

TR_DATE	ACC_NAME	TYPE	AMOUNT
01-01-2017	AVNEESH	CR	60000
02-01-2017	AVNEESH	DB	8000
03-01-2017	AVNEESH	CR	8000
04-01-2017	AVNEESH	DB	5000
01-01-2017	NUPUR	CR	10000
02-01-2017	NUPUR	DB	8000
03-01-2017	NUPUR	CR	8000

And expected result for above data is

TR_DATE	ACC_NAME	TYPE	AMOUNT	BALANCE
01-01-2017	AVNEESH	CR	60000	60000
02-01-2017	AVNEESH	DB	8000	52000
03-01-2017	AVNEESH	CR	8000	60000
04-01-2017	AVNEESH	DB	5000	55000
01-01-2017	NUPUR	CR	10000	10000
02-01-2017	NUPUR	DB	8000	2000
03-01-2017	NUPUR	CR	8000	10000

CodePudding user response：

You can use the analytic version of the sum() function, with a case expression to turn debits into negative amounts, and a window clause to apply the sum to amounts up to the current row's date:

select tr_date, acc_name, type, amount,
  sum(case when type = 'DB' then -1 else 1 end * amount)
    over (partition by acc_name order by tr_date) as balance
from passbook
order by acc_name, tr_date

TR_DATE	ACC_NAME	TYPE	AMOUNT	BALANCE
2017-01-01	AVNEESH	CR	60000	60000
2017-01-02	AVNEESH	DB	8000	52000
2017-01-03	AVNEESH	CR	8000	60000
2017-01-04	AVNEESH	DB	5000	55000
2017-01-01	NUPUR	CR	10000	10000
2017-01-02	NUPUR	DB	8000	2000
2017-01-03	NUPUR	CR	8000	10000

fiddle