Monthly sales and monthly incoming payments according to customers

[former_member573912]

Hi,

Can One one help in this query:-

I required customer wise sales of particular month and customer wise incoming payment for particular month.

For example in november month what is the total gross sales amount as well as total incoming payment received from that customer.

Month of November

customer_name customer sales grossamount customer incoming payment

a 200000 500000

b 50000 10000

c 100000 10000

My Query:-

select cardname,sum(DocTotal)AS SALEStotal from OINV

where Canceled='n' and DocDate>='20180601' and DocDate <='20180630'

group by cardname

UNION ALL

select cardname,sum(DocTotal) AS PAYMENTRECVED from ORCT

where Canceled='n' and DocDate>='20180601' and DocDate <='20180630'

group by cardname

[former_member573912]

Hi ,Sorry to inform you, the output is coming wrong.

I required the total sales of november month and total payment received in november month, if the invoice is prepared in september and october, if we receive the payment in this month it should be appear.

For example, if the total sales of November is 5000 and if i recieved the payment in november is 20,000 , that should appear.

Even if there is no sales for customer in november , but he had given payment in november it should display in result.

[former_member573912]

Hi, Thanks for the query. In the same query i want to add closing sales of last maonth as well as closing incoming payment of last month

PFAbb.jpg

[former_member419030]

Dear qwer G,

I think, you have to join the invoice and payment together, so that it can appear in one line for each BP..

UNION make the result to appear two lines.

Below is my suggestion.

select T0.cardname,sum(T0.DocTotal)AS 'SALEStotal',sum(T2.DOCTOTAL) as 'payment received'

from OINV T0

INNER JOIN RCT2 T1 ON T0.BASEABS=T1.DOCENTRY

INNER JOIN ORCT T2 ON T1.DOCNUM=T2.DOCENTRY

where T0.Canceled='n' and T0.DocDate>=[%1] and T0.DocDate <=[%2]

group by cardname

[former_member573912]

Thanks for your Answer, error is coming

Invalid column name 'baseabs'.

[Abdul]

Hi the correct correct join between RCT2 and OINV is INNER JOIN RCT2 T1 ON T0.[DocEntry]=T1.DOCENTRY and T1.[InvType] = T0.Objtype

[former_member419030]

Dear qwer G,

Sorry my mistake,

its supposed : INNER JOIN RCT2 T1 ON T0.Docentry=t1.BASEABS

or you may try with Abdul Mannan suggestion.