Daily Sales Report base Total And Doctotal

[former_member573912]

I required daily sales Base amount and Gross Total , i Created the below query but doctotal is coming Wrong .Please check the below query as whether the query included AR debit Memo amount or not.

Please help me in correcting the query of daily sales base amount and Sales Gross Total

select a.docdate,sum(b.linetotal) as baseamount ,sum(a.doctotal) from oinv a inner join inv1 b on a.docentry=b.DocEntry where a.CANCELED not in ('y','c') and a.docdate>='2018-04-01' group by a.DocDate order by a.DocDate

[kothandaraman_nagarajan]

Hi,

Try this,

select a.Docnum,a.docdate,sum(b.linetotal) as baseamount ,a.doctotal from oinv a inner join inv1 b on a.docentry=b.DocEntry where a.CANCELED not in ('y','c') and a.docdate>='2018-04-01' group by a.DocDate,a.doctotal,a.docnum UNION ALL select a.docnum,a.docdate,-1* sum(b.linetotal) as baseamount ,-1 * a.doctotal from orin a inner join rin1 b on a.docentry=b.DocEntry where a.CANCELED not in ('y','c') and a.docdate>='2018-04-01' group by a.DocDate,a.doctotal,a.docnum

Regards,

Nagarajan

[former_member573912]

Please check the attachmentquery-result.jpg

The doc total is coming wrong. Doc total amount is double the base amount..

I required the 2 queries :-

a) Base amount and doctotal

b) baseamount with freight charges not including freight taxes..

Please help.

[kothandaraman_nagarajan]

Share your query result and highlight the wrong amount.

[former_member573912]

Can you please explain what is -1 in ,a.docdate,-1* sum(b.linetotal)

[former_member573912]

Doc Total is Coming Wrong

[kothandaraman_nagarajan]

Hi,

Try this query,

select a.docdate,sum(b.linetotal) as baseamount ,sum(a.doctotal) from oinv a inner join inv1 b on a.docentry=b.DocEntry where a.CANCELED not in ('y','c') and a.docdate>='2018-04-01' group by a.DocDate

UNION ALL

select a.docdate,-1* sum(b.linetotal) as baseamount ,-1 * sum(a.doctotal) from orin a inner join rin1 b on a.docentry=b.DocEntry where a.CANCELED not in ('y','c') and a.docdate>='2018-04-01' group by a.DocDate

order by a.DocDate

Regards,

Nagarajan