Query for Comparative Sales Statement

[Former Member]

Dear Experts,

Please tell me a query to get result Sales Invoice results as follows or sales recorded through Journal Vouchers sometimes. when an invoice is not created directly journal entry is posted in JDT1.

BP.NAME     Jan   Feb   Mar  April May June

Total at the bottom is also needed.

Regards,

Junaid

[Former Member · Accepted solution]

Hi Junaid,

Try:

SELECT P.CardName, [1] Jan, [2] Feb, [3] Mar, [4] Apr, [5] May, [6] Jun, [7] Jul, [8] Aug, [9] Sep, [10] Oct, [11] Nov, [12] Dec

FROM (SELECT T0.ShortName,T1.Cardname,

(T0.Debit-T0.Credit) AS AMOUNT, MONTH(T2.RefDate) Month FROM JDT1 T0  INNER JOIN OCRD T1 ON T1.CardCode = T0.Shortname

INNER JOIN OJDT T2 ON T2.TransID = T0.TransID AND T2.TransType in ('13','14','30')

WHERE T2.RefDate >='[%0]' AND T2.RefDate <='[%1]') S

PIVOT (SUM(AMOUNT) FOR month IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] ) ) P

Thanks,

Gordon

[Former Member · Accepted solution]

Chah Gaya Mera Bhai

TOOOOOOOOO GOOOOOOOOOOD!!!!!!!!!!!!!!

[Former Member · Accepted solution]

Dear Gordon,

I am very thankful for you help full answer Please add following things in the query.

BP Code field before the name field and selection for bp  group at the time of execution.

Those BPs which do not have posting or zero balance should be reflect in the output.

Best Regards,

Junaid

[Former Member · Accepted solution]

Please post a new thread for added request.

[Former Member]

Could you please share me your Email id so that i can send the query to you.

Regards

Abuthahir

[Former Member]

junaidahmad@hotmail.com