Sales order and Delivery difference

[Former Member]

I Am trying to write a query to produce totals of items ordered by customers compared to deliveries made. However there may be 3 deliveries made to one sales order and so my sales order total for an item is getting multiplied by 3 and showing a difference compared to the amount delivered where in fact the whole order has been delivered. How do I hold the sales order total per item once but then compare with the total delivery per item over multiple deliveries?

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T1.[CardName]'CustName',T1.DocNum 'SOOrder',T1.[DocStatus]'SOStatus',T0.[Project]'JobNo', T0.ItemCode,T0.Quantity 'SOQty',T2.Quantity 'Delivered QTY',T0.OpenQty 'BalQty', T3.DocNum 'Delivery#',T3.[DocDueDate]'DelDate'

From RDR1 T0 inner join ORDR T1 on T0.DocEntry=T1.DocEntry

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan

Part of your query is missing as the T2 and T3 Fields cannot be bound because there is no join for them. I am also looking for the value comparison. See below my query that is totalling the Sales Order totals multiple times when there is more that one delivery

SELECT T0.ItemCode, To.Dscription, Sum(T0.LineTotal AS 'Order Value', ISNULL(Sum(T1.LineTotal),0) AS 'Delivered Value', Sum(T0.LineTotal) - ISNULL(Sum(T1.LineTotal),0) AS 'Lost Sales'

FROM RDR1 T0 LEFT OUTER JOIN DLN1 ON T1 BaseType = 17 and T0.DocEntry = T1.BaseEntry AND T0.LineNum = T1.BaseLine

GROUP BY T0.ItemCode, T0.Dscription

ORDERBY T0.ItemCode

[kothandaraman_nagarajan]

Hi,

Try this query for cost difference

SELECT T0.ItemCode, T0.Dscription, Sum(T0.LineTotal) AS 'Order Value', ISNULL(Sum(T1.LineTotal),0) AS 'Delivered Value', Sum(T0.LineTotal) - ISNULL(Sum(T1.LineTotal),0) AS 'Lost Sales'

FROM RDR1 T0 LEFT OUTER JOIN DLN1 t1 ON  T0.DocEntry = T1.BaseEntry AND T0.LineNum = T1.BaseLine and T1. BaseType = 17

GROUP BY T0.ItemCode, T0.Dscription

ORDER BY T0.ItemCode

Replace my query with below query

SELECT T1.[CardName]'CustName',T1.DocNum 'SOOrder',T1.[DocStatus]'SOStatus',T0.[Project]'JobNo', T0.ItemCode,T0.Quantity 'SOQty',T2.Quantity 'Delivered QTY',T0.OpenQty 'BalQty', T3.DocNum 'Delivery#',T3.[DocDueDate]'DelDate'

From RDR1 T0 inner join ORDR T1 on T0.DocEntry=T1.DocEntry

   left join DLN1 T2 on T2.Basetype=17 and T2.BaseEntry=T0.DocEntry and T2.BaseLine=T0.LineNum

   left join ODLN T3 on T2.DocEntry=T3.DocEntry

Thanks & Regards,

Nagarajan