如何计算贷方和借方的余额?

Question

如何根据每个客户的贷方CRD和借方DEB获得余额金额cust来自下表txn_tbl

SQL> SELECT * FROM txn_tbl;
CUS        AMT TXN_CDE
--- ---------- ----------
A          500 CRD
B          400 CRD
A          350 CRD
C          235 DEB
C          800 CRD
B          132 DEB
D          673 CRD

这是我尝试过的查询

SELECT cust, LAG(amt,1,0) OVER (PARTITION BY cust ORDER BY cust) "Bal"
FROM 
(SELECT cust, SUM(amt)amt
FROM txn_tbl
GROUP BY cust, txn_cde
ORDER BY 2);

Answer 1

如果你想要运行余额，你可以用条件逻辑做一个窗口总和：

select 
t.*,
sum(case when txn_cde = 'CRD' then amt else -amt end) 
over(partition by cus order by id) running_balance
from mytable

为此，您需要一个可用于对记录进行排序的列;我假设id.

另一方面，如果你想要每个客户的总体余额(所以结果集中每个客户只有一条记录(，你可以对聚合使用相同的逻辑(并且你不需要排序列(：

select cus, sum(case when txn_cde = 'CRD' then amt else -amt end) balance
from mytable
group by cus

Answer 2

你可以把你的金额加起来。诀窍是使您的信用(或借方，不清楚(为负数：

SELECT cust, Sum(CASE WHEN TXN_CODE = 'DEB' THEN -1 ELSE 1 END * amt) as
FROM txn_tbl 
GROUP BY cust