Skip to Content
0
Former Member
Feb 05, 2015 at 10:40 AM

Query for Account balance as of day by cost centre

293 Views

Hi all,

I have the following query which essentially gives me the balance of account by a certain day but I want to refine it further:

SELECT T1.FormatCode as "Account Code",T1.AcctName,SUM(T0.Debit-T0.Credit) 'Balance as of the day', T0.ProfitCode as "Department", T0.OcrCode2 as "Customer"

FROM JDT1 T0

JOIN OACT T1 ON T1.AcctCode=T0.Account

JOIN OJDT T2 ON T2.TransID=T0.TransID

WHERE T2.RefDate <=[%0\] and T1.AcctName Not Like '%%xx%%' and T1.FormatCode >= '403101' and T1.FormatCode <> '999999'

Group By T1.FormatCode,T1.AcctName, T0.ProfitCode, T0.OcrCode2

HAVING SUM (T0.Debit) - SUM (T0.Credit) <> '0'

What I would want is to have the balance if T0.ProfitCode <>'' or T0.OcrCode2 <> '' so essentially exclude them if both are blank but this does not seem to work and repeats some of the lines (even if the same account and cost centers) and now excludes my first two conditions of T1.AcctName Not Like '%%xx%%' and T1.FormatCode >= '403101'.


Can someone please advise?


Many Thanks,