Skip to Content
0
Former Member
Feb 02, 2016 at 09:36 PM

Profit and Loss Statement Query with Credit Memo

517 Views

The Query for Profit and Loss Statemen works but I'm missing Credit Memo

SELECT T0.[FormatCode], T0.[AcctName], SUM(T1.[Credit]-T1.[Debit]) Balance

FROM OACT T0

INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account

INNER JOIN OJDT T2 ON T1.TransId = T2.TransId

WHERE T0.[ActType] in ('I','E') and DateDiff(YY,T2.[RefDate],GetDate()) = 0 AND T1.[Debit] != T1.[Credit]

GROUP BY T0.[FormatCode], T0.[AcctName]

Having SUM(T1.[Debit]-T1.[Credit]) != 0

ORDER BY T0.[FormatCode]