I need cumulative balance sub query or SQL query running total in the financial report query. I use table OJDT and JDT1 including document tables.
I use this query sample but not success:
SELECT counter, emp_short, trans_date, debit, credit,
(SELECT SUM(COALESCE(debit,0) - COALESCE(credit,0))
FROM trans02 tr2
WHERE tr2.counter <= tr1.counter and
tr2.emp_short = tr1.emp_short) AS 'Running balance'
FROM trans02 tr1
ORDER BY emp_short, counter
I replace trans02 with jdt1 and execute the query but the result is different with cumulative balance like in the G/L report cumulative balance field. Pls help.