I have another question that I need help with. I am generating a query that will compare how many items have been delivered and how many have been returned in a given time period, then display the % returned from that time period.
The problem I am facing is that our returns and credit memo process do not require that a return document be linked directly to a delivery document. So I believe I need a UNION statement. I am encountering some errors and would greatly appreciate any help to get this working. I'm still learning SQL so please let me know what I am doing wrong:
SELECT T0.[ITEMCODE], NULL AS 'Delivered', sum(T0.[Quantity]) AS 'Returned'
FROM RDN1 T0
INNER JOIN ORDN T1 on T0.DocEntry = T1.DocEntry
WHERE T1.[DocDate] >=[%0] and T1.[DocDate] <=[%1]
SELECT T2.[ITEMCODE], sum(T2.[Quantity]) AS 'Delivered', NULL AS 'Returned'
FROM ODLN T3
INNER JOIN DLN1 T2 on T2.DocEntry = T3.DocEntry
WHERE T3.[DocDate] >=[%0] and T3.[DocDate] <=[%1]
GROUP BY T2.[ITEMCODE], 'Delivered', 'Returned'