Skip to Content
0
Former Member
Nov 28, 2011 at 01:51 PM

Query to get G/L accounts even with the Zero transactions

67 Views

Dear experts,

i have tried a query as below

SELECT T0.[AcctCode], T0.[AcctName], T0.[CurrTotal] AS 'Bank Balance', sum(T1.[Debit]) as 'Payment', sum(T1.[Credit]) as 'Collection' FROM OACT T0 LEFT OUTER

JOIN JDT1 T1 ON T0.AcctCode = T1.Account WHERE T0.[AcctCode] in ('162016','162010','162011','162002','162003','161010') AND T1.RefDate BETWEEN '[%0]' AND '[%1]'

GROUP BY T0.[AcctCode], T0.[AcctName], T0.[CurrTotal]

My requirement is

I have given 5 G/L accounts in where condition. I am getting the report only the account have debit or credit transaction.

whereas i want account in report even if it has "0" as transaction within that period.

Thanks in advance,

dwarak

Edited by: Dwarakanath.P on Nov 28, 2011 2:51 PM