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

[Former Member]

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

[Former Member]

Hi Dwarak,

Try:

SELECT T0.AcctCode, T0.AcctName, T0.CurrTotal AS 'Bank Balance', sum(IsNull(T1.Debit,0)) as 'Payment', sum(IsNull(T1.Credit,0)) as 'Collection'

FROM OACT T0

LEFT JOIN JDT1 T1 ON T0.AcctCode = T1.Account AND T1.RefDate BETWEEN '[%0\]' AND '[%1\]'

WHERE T0.AcctCode in ('162016','162010','162011','162002','162003','161010')

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

Thanks,

Gordon

[Former Member]

Hi GordonDu,

Thanks for your query. It workiing , But it has one drawback.

When am giving posting date(Refdate) of 01.12.2011 . It showing me the today(06.12.2011) Current total.

I want only the closing balance(Current total) of 01.12.2011. how can i get it?

Regards,

Dwarak.

[jitin_chawla]

Hi,

The current total is picked as the balance appearing in the G\L Account as on the system date and not the refdate selected.

You need to query it further as a sub query to get that value by doing a

select sum(debit)-sum(credit) from JDT1 where shortname ='xx' and refdate >= [%1] and refdate <= [%2]

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

The only way to achieve your goal world be subtracting all transactions between the date you selected and the current date or rebuilt all history since day one. That may not be realistic if you want all ranges of dates such previous years.

[Former Member]

Dear Gordon,

Your query is correct expect current balance because I want the header like as follow with below conditions.

Account code, Account name, Balance, Sum (Debit) and Sum (Credit)

Conditions

Balance - up till particular date . Example: 01.12.2011

Sum(Debit) and Sum(Credit) - only as on 01.12.2011

So, the posting date will be

Balance is (T1.RefDate <= [%0])

Sum(Debit) and Sum(Credit) (T1.RefDate = [%0])

Whereas,

your query gives solution for Sum(Debit) and Sum(Credit).

Jitin query gives solution for Balance.(as below)

select sum(debit)-sum(credit) from JDT1 where shortname ='xx' and refdate >= [%1] and refdate <= [%2]

Now I want to merge both of this to give my requirement ?

Regards,

Dwarak

[Former Member]

Try:

SELECT T0.AcctCode, T0.AcctName, sum(IsNull(T1.Debit,0))-sum(IsNull(T1.Credit,0)) AS 'Bank Balance', sum(IsNull(T1.Debit,0)) as 'Payment', sum(IsNull(T1.Credit,0)) as 'Collection'

FROM OACT T0

LEFT JOIN JDT1 T1 ON T0.AcctCode = T1.Account AND T1.RefDate BETWEEN '[%0\]' AND '[%1\]'

WHERE T0.AcctCode in ('162016','162010','162011','162002','162003','161010')

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

[jitin_chawla]

Hi,

Check this if it works for you :

SELECT T0.AcctCode, T0.AcctName, CASE WHEN T0.CurrTotal != 0 THEN t0.Currtotal ELSE 0 END AS 'Bank Balance', 
isnull(sum(T1.Debit),0) as 'Payment', isnull(sum(T1.Credit),0) 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 '2009.01.01' AND '2011.12.31'
GROUP BY T0.AcctCode, T0.AcctName, T0.CurrTotal

Kind Regards,

Jitin

SAP Business One Forum Team

[vasileiosfasolis]

hi Dwarakanath

try this

SELECT isnull(T0.AcctCode,0), T0.AcctName, isnull(T0.CurrTotal,0) AS 'Bank Balance',isnull(sum(T1.Debit),0) as 'Payment', isnull(sum(T1.Credit),0) 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

Edited by: Fasolis Vasilios on Nov 28, 2011 3:01 PM

[Former Member]

Dear Fasolis Vasilios,

Still am getting only the account having debit or credit sum.

like this

# Account Code Account Name Bank Balance Payment Collection

1 161010 Cash A/C - 2,376.00 0 3,363.00

2 162002 BNP Paribas - USD A/c 11,336,040.71 2,281,479.20 0

3 162010 BNP Paribas - INR A/c 14,314,924.41 1,845,229.00 1,721,899.00

4 162016 ING Vysya-CC A/c 98,653,514.21 908,598.70 565,601.00

Other 2 account is missing since it has no debit and credit.

Regards,

Dwarak

[vasileiosfasolis]

Hi

try this

SELECT isnull(T0.AcctCode,0), T0.AcctName, isnull(T0.CurrTotal,0) AS 'Bank Balance',isnull(sum(T1.Debit),0) as 'Payment', isnull(sum(T1.Credit),0) as 'Collection' FROM OACT T0 inner
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

if it does not work then an if statement should be embeded in your select statement

[Former Member]

HI

still didnt got it.

I got the reason.

Current balance of account doesnt appears. if it is does not have transaction for the particular date.

so, please code such a way that i should able to get the account in report even if it doesnt have transaction for the particular period.

Regards,

Dwarak

Edited by: Dwarakanath.P on Nov 29, 2011 9:11 AM

[vasileiosfasolis]

try this please

SELECT 
isnull(T0.AcctCode,0), T0.AcctName, (case when T0.CurrTotal!= ' ' then t0.currtotal else 0 end) 
 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 ='2011-11-05'
GROUP BY T0.AcctCode, T0.AcctName, T0.CurrTotal