Profit and Loss Statement Query

[CRVMANISH]

Hello Experts,

I want to make customize profit and Loss Report using User Defined form with Grid view or Crystal Report. I need SQL Query , what tables will give P & L Statement Report(yearly/ monthy / quarterly).

Already available Query will be very helpful

Please Suggest

Quick Reply will be very helful

[Former Member · Accepted solution]

Hi,

Try this to start:

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]

If you are using natural account, replace FormatCode with AcctCode.

Thanks,

Gordon

[Former Member · Accepted solution]

The query works perfectly great but i'm missing credit memos.