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?