Skip to Content
0
Former Member
Sep 22, 2014 at 07:51 PM

Rate of return query

20 Views

Hi Everyone,

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]

UNION

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'