在SAP B1 HANA数据库中如何基于Debit和Credit字段计算余额

你的原查询语句如下：

SELECT T0."TransId",
T0."Account",
T0."TransType",
SUM(COALESCE(CASE WHEN T0."TransType" = '15' THEN T0."Credit" END,T0."Credit")) As "Credit",
SUM(COALESCE(CASE WHEN T0."TransType" = '13' THEN T0."Debit" END,T0."Debit")) As "Debit",
SUM(COALESCE(CASE WHEN T0."TransType" = '13' THEN T0."Debit" END,T0."Debit")) - SUM(COALESCE(CASE WHEN T0."TransType" = '15' THEN T0."Credit" END,T0."Credit")) "Balance"
FROM JDT1 T0
WHERE T0."RefDate" BETWEEN '2022-09-01' AND '2022-09-30'
AND T0."Account" IN ('4101101','5101101')
GROUP BY T0."TransId", T0."Account",T0."TransType"

问题分析 #

你当前的借贷发生额计算存在冗余：COALESCE(CASE WHEN T0."TransType" = '15' THEN T0."Credit" END, T0."Credit")等价于直接取T0."Credit"，因为当交易类型不是15时，CASE返回NULL，COALESCE会直接取原Credit值，导致CASE判断完全没起到过滤作用。

修正后的余额计算方案 #

假设你的业务规则是交易类型13仅统计借方、15仅统计贷方，可以调整CASE语句，精准过滤对应交易类型的发生额后再计算余额：

SELECT 
    T0."TransId",
    T0."Account",
    T0."TransType",
    -- 仅交易类型15统计贷方
    SUM(CASE WHEN T0."TransType" = '15' THEN T0."Credit" ELSE 0 END) AS "Credit",
    -- 仅交易类型13统计借方
    SUM(CASE WHEN T0."TransType" = '13' THEN T0."Debit" ELSE 0 END) AS "Debit",
    -- 余额 = 借方发生额 - 贷方发生额
    SUM(CASE WHEN T0."TransType" = '13' THEN T0."Debit" ELSE 0 END) - SUM(CASE WHEN T0."TransType" = '15' THEN T0."Credit" ELSE 0 END) AS "Balance"
FROM JDT1 T0
WHERE 
    T0."RefDate" BETWEEN '2022-09-01' AND '2022-09-30'
    AND T0."Account" IN ('4101101','5101101')
GROUP BY T0."TransId", T0."Account", T0."TransType"

通用账户累计余额计算 #

如果需要计算每个账户的整体余额（而非单交易ID的余额），只需去掉TransId和TransType的分组，按账户汇总：

SELECT 
    T0."Account",
    SUM(T0."Debit") AS "TotalDebit",
    SUM(T0."Credit") AS "TotalCredit",
    -- 资产类科目用借方减贷方，负债/权益类换为贷方减借方，可根据实际科目类型调整
    SUM(T0."Debit") - SUM(T0."Credit") AS "AccountBalance"
FROM JDT1 T0
WHERE 
    T0."RefDate" BETWEEN '2022-09-01' AND '2022-09-30'
    AND T0."Account" IN ('4101101','5101101')
GROUP BY T0."Account"

内容的提问来源于stack exchange，提问作者Benvc