SALES REPORT (Credit memos Subtracted)

[Former Member]

Hi all,

I would like to come up with a detailed Sales Reports that shows the Quantity and Value of items sold per day (AR Invoices) excluding the credit memos. I later want to use the same query in crystal reports. I came up with the below query; the select statements are executing individually but using 'UNION ALL' I am getting a 'Conversion' error.

SELECT T0.[CardCode]Code, T4.[SuppCatNum]cat, T4.[FrgnName]frn, T1.[Quantity]InvQ, T0.[DocTotal]InvT,0 MemoQ,0 MemoT FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry full outer JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode full outer JOIN OUSR T3 ON T0.UserSign = T3.USERID full outer JOIN OITM T4 ON T1.ItemCode = T4.ItemCode WHERE (T1.WhsCode='MLO01' or T1.WhsCode='ARG01') AND T0.DocDate=[%0]

union all

SELECT 0 Code, 0 cat, 0 frn, 0 InvQ,0 InvT, T1.[Quantity] MemoQ, T0.[DocTotal] MemoT FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry full outer JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode full outer JOIN OUSR T3 ON T0.UserSign = T3.USERID full outer JOIN OITM T4 ON T1.ItemCode = T4.ItemCode WHERE (T1.WhsCode='MLO01' or T1.WhsCode='ARG01') AND T0.DocDate=[%0]

kindly assist refine this query or come up with a better one altogether. Thank you.

[Former Member · Accepted solution]

Hi,

Try:

SELECT T0.CardCode Code, T4.SuppCatNum cat, T4.FrgnName frn, T1.Quantity InvQ, T1.LineTotal InvT,0 MemoQ,0 MemoT

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN OITM T4 ON T1.ItemCode = T4.ItemCode

WHERE (T1.WhsCode='MLO01' or T1.WhsCode='ARG01') AND T0.DocDate=[%0\]

union all

SELECT '', '', '', 0,0, T1.Quantity, T1.lineTotal

FROM ORIN T0

INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN OITM T4 ON T1.ItemCode = T4.ItemCode

WHERE (T1.WhsCode='MLO01' or T1.WhsCode='ARG01') AND T0.DocDate=[%0\]

Thanks,

Gordon