Help on Cumulative Balance SAP

[geraldhans]

Hi All

I want to seek help regarding how to retrieve Cumulative Balance on SAP B1. I understand that this values are not stored inside OJDT or JDT tables. All you have to do is to SUM Debit and Credit on JDT1 table.

Right now I'm writing queries so I can export the results directly without opening SAP Application. Below are the Query I wrote

SELECT T1.DueDate, T1.Account, SUM(T1.Debit - T1.Credit) - (
    SELECT SUM(T2.Debit - T2.Credit)
    FROM JDT1 T2
    WHERE T2.DueDate BETWEEN '20230501' AND '20230531' AND T2.Account = '11020101'
) AS Result
FROM JDT1 T1
WHERE T1.Account = '11020101'
GROUP BY T1.DueDate, T1.Account;<br>

Basically what I want retrieve the Bank Balance based on Date Selection input by user, but I need to calculate the Balance prior to the user input. For example User want to retrieve May Bank Balance, before showing the bank balance I need to calculate the Bank Balance as per April 2023 then I add / minus the ongoing transasction based on the user input

Appreciate your help

Thank You

Regards
Gerald

[SonTran]

Hi,

Try this

SELECT T1.Account, 
	(
	SELECT SUM(isnull(S0.Debit,0) - isnull(S0.Credit,0)) FROM JDT1 S0 
	WHERE S0.Account=[%0] and S0.RefDate<[%1]
	) as OpeningBalance
	, SUM(isnull(T1.Debit,0)) as Debit
	, SUM(isnull(T1.Credit,0)) as Credit
	, 
	(
	SELECT SUM(isnull(S0.Debit,0) - isnull(S0.Credit,0)) FROM JDT1 S0 
	WHERE S0.Account=[%0] and S0.RefDate<=[%2]
	) as ClosingBalance
FROM JDT1 T1
WHERE T1.Account=[%0] and T1.RefDate>=[%1] and T1.RefDate<=[%2]
GROUP BY T1.Account

Hope this helps,

Son Tran