on 01-27-2011 5:03 PM
Estimados Todos, como puedo hacer un comparativo en un rango de fechas de los productos que se han ingresado como una Orden de Ventas y cuantos de estos se han facturado, se tendria que ver el producto, cantidad pedido, valor pedido, cantidad facturado, valor facturado.
Gracias por su ayuda.
Saludos.
Nelson Guerrero
Hola Nelson
Prueba esta query
SELECT DISTINCT T0.DocNum 'Orden de Vta', T1.ItemCode, T1.Dscription, T1.Quantity 'Solicitado', (T1.Quantity * T1.Price) 'Valor Solic', T2.DocNum 'Entrega', T3.ItemCode, T3.Quantity, (T3.Quantity * T3.Price) 'Valor Entreg', T4.DocNum 'Factura', T5.ItemCode, T5.Quantity, (T5.Quantity * T5.Price) 'Valor Fact'
FROM ORDR T0
INNER JOIN RDR1 T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN ODLN T2 ON T2.DocNum = T1.TrgetEntry
LEFT JOIN DLN1 T3 ON T3.DocEntry = T2.DocEntry AND T3.ItemCode = T1.ItemCode
LEFT JOIN OINV T4 ON T4.DocNum = T3.TrgetEntry
LEFT JOIN INV1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode = T3.ItemCode
WHERE T0.DocDate BETWEEN '[%0]' AND '[%1]'
ORDER BY T0.DocNum, T1.ItemCode
Saludos
Felipe Loyola
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Entonces Prueba este query
SELECT DISTINCT T1.ItemCode, T1.Dscription, SUM(T1.Quantity) 'Solicitado', SUM(T1.Quantity * T1.Price) 'Valor Solic', SUM(T3.Quantity) 'Entregado', SUM(T3.Quantity * T3.Price) 'Valor Entreg', SUM(T5.Quantity) 'Facturado', SUM(T5.Quantity * T5.Price) 'Valor Fact'
FROM ORDR T0
LEFT JOIN RDR1 T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN DLN1 T3 ON T3.ItemCode = T1.ItemCode
LEFT JOIN ODLN T2 ON T2.DocEntry = T3.DocEntry
LEFT JOIN INV1 T5 ON T5.ItemCode = T3.ItemCode
LEFT JOIN OINV T4 ON T4.DocEntry = T5.DocEntry
WHERE T0.DocDate BETWEEN '[%0]' AND '[%1]' AND T2.DocDate BETWEEN '[%0]' AND '[%1]' AND T4.DocDate BETWEEN '[%0]' AND '[%1]'
GROUP BY T1.ItemCode, T1.Dscription
ORDER BY T1.ItemCode
Atte.
FLR
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.