计算每一笔交易开放BAL后余额
问题描述:
我有两个SQL数据库中的表即计算每一笔交易开放BAL后余额
Cust_Table(CustID, CustName, custAddrs, CustMob, CustOpBal)
而且
Trans_Table(TransId, CustID, TransAmt, TransType(bool dr/cr), Msg, TansDate)
现在我需要的SQL查询的越来越声明等(银行存折)特别是 客户ID?
Date Message Dt_Amount Cr_Amount Balance
荫使用下面的查询
SELECT t1.Trans_Date, t1.Trans_Msg,
(CASE WHEN t1.Trans_Type=1 THEN 'Cr' ELSE 'Dr' END) as Trans_Type,
t1.Trans_Amount,
SUM(t2.Trans_Amount*case when t2.trans_type = '1' then 1 else -1 end) as Balance
FROM [LNLCredit].[dbo].[Trans_Table] t1
INNER JOIN [LNLCredit].[dbo].[Trans_Table] t2
ON t1.cust_id = t2.cust_id AND t1.trans_id >= t2.Trans_ID
WHERE t1.Cust_ID=2
GROUP BY t1.cust_id,t1.trans_id,t1.trans_type,t1.Trans_Amount,t1.Trans_Date,t1.Trans_Msg;
其工作的罚款。
但我也想添加Opening Balance(从Cust_Table)到上面的解决方案。
请帮忙??????
答
试试这个..
SELECT t1.Trans_Date, t1.Trans_Msg,
(CASE WHEN t1.Trans_Type=1 THEN 'Cr' ELSE 'Dr' END) as Trans_Type,
t1.Trans_Amount,
SUM(t2.Trans_Amount*case when t2.trans_type = '1' then 1 else -1 end) as Balance,
c.CustOpBal
FROM [LNLCredit].[dbo].[Trans_Table] t1
INNER JOIN [LNLCredit].[dbo].[Trans_Table] t2
ON t1.cust_id = t2.cust_id AND t1.trans_id >= t2.Trans_ID
INNER JOIN Cust_Table C on C.cust_id = t1.cust_id
WHERE t1.Cust_ID=2
GROUP BY t1.cust_id,t1.trans_id,t1.trans_type,t1.Trans_Amount,
t1.Trans_Date,t1.Trans_Msg,C.CustOpBal;
你需要列中添加额外的INNER JOIN
并添加openingBalance到GROUP BY
和SELECT
+0
它确定,但我需要更新交易余额中的期初余额。 – kamal
刚刚加入到'Cust_Table',从那里添加任何领域,不要忘记将其包含在“Group By”子句中。 – Arvo