Buenas tardes.
Me pueden ayudar con este Query, actualmente me esta funcionando bien, Codigo, Nombre, Total en Stock, Comprometido, Solicitado y Disponible por mes, Ventas, Piezas, Costo. ahora quiero agregar las devoluciones, Anexo el Query
SELECT DISTINCT T2."ItemCode" AS "CODIGO", T4."ItemName" AS "NOMBRE PRODUCTO",
T4."OnHand" AS "TOTAL", T4."IsCommited" AS "COMPROMETIDO",
T4."OnOrder" AS "SOLICITADO", T4."OnHand" + T4."OnOrder" - T2."IsCommited" AS "DISPONIBLE",
(SELECT DISTINCT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 1 AND T5."ItemCode" = T4."ItemCode")AS "CANTIDAD ENERO",
(SELECT DISTINCT SUM(T5."LineTotal")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 1 AND T5."ItemCode" = T4."ItemCode")AS "IMPORTE ENERO",
(SELECT DISTINCT SUM(T5."StockPrice"*T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 1 AND T5."ItemCode" = T4."ItemCode")AS "COSTO ENERO",
(SELECT DISTINCT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 2 AND T5."ItemCode" = T4."ItemCode")AS "CANTIDAD FEBRERO",
(SELECT DISTINCT SUM(T5."LineTotal")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 2 AND T5."ItemCode" = T4."ItemCode")AS "IMPORTE FEBRERO",
(SELECT DISTINCT SUM(T5."StockPrice"*T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 2 AND T5."ItemCode" = T4."ItemCode")AS "COSTO FEBRERO",
(SELECT DISTINCT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 3 AND T5."ItemCode" = T4."ItemCode")AS "CANTIDAD MARZO",
(SELECT DISTINCT SUM(T5."LineTotal")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 3 AND T5."ItemCode" = T4."ItemCode")AS "IMPORTE MARZO",
(SELECT DISTINCT SUM(T5."StockPrice"*T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 3 AND T5."ItemCode" = T4."ItemCode")AS "COSTO MARZO",
(SELECT DISTINCT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 4 AND T5."ItemCode" = T4."ItemCode")AS "CANTIDAD ABRIL",
(SELECT DISTINCT SUM(T5."LineTotal")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 4 AND T5."ItemCode" = T4."ItemCode")AS "IMPORTE ABRIL",
(SELECT DISTINCT SUM(T5."StockPrice"*T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 4 AND T5."ItemCode" = T4."ItemCode")AS "COSTO ABRIL",
(SELECT DISTINCT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 5 AND T5."ItemCode" = T4."ItemCode")AS "CANTIDAD MAYO",
(SELECT DISTINCT SUM(T5."LineTotal")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 5 AND T5."ItemCode" = T4."ItemCode")AS "IMPORTE MAYO",
(SELECT DISTINCT SUM(T5."StockPrice"*T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 5 AND T5."ItemCode" = T4."ItemCode")AS "COSTO MAYO",
(SELECT DISTINCT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 6 AND T5."ItemCode" = T4."ItemCode")AS "CANTIDAD JUNIO",
(SELECT DISTINCT SUM(T5."LineTotal")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 6 AND T5."ItemCode" = T4."ItemCode")AS "IMPORTE JUNIO",
(SELECT DISTINCT SUM(T5."StockPrice"*T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 6 AND T5."ItemCode" = T4."ItemCode")AS "COSTO JUNIO",
(SELECT DISTINCT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 7 AND T5."ItemCode" = T4."ItemCode")AS "CANTIDAD JULIO",
(SELECT DISTINCT SUM(T5."LineTotal")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 7 AND T5."ItemCode" = T4."ItemCode")AS "IMPORTE JULIO",
(SELECT DISTINCT SUM(T5."StockPrice"*T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 7 AND T5."ItemCode" = T4."ItemCode")AS "COSTO JULIO",
(SELECT DISTINCT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 8 AND T5."ItemCode" = T4."ItemCode")AS "CANTIDAD AGOSTO",
(SELECT DISTINCT SUM(T5."LineTotal")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 8 AND T5."ItemCode" = T4."ItemCode")AS "IMPORTE AGOSTO",
(SELECT DISTINCT SUM(T5."StockPrice"*T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 8 AND T5."ItemCode" = T4."ItemCode")AS "COSTO AGOSTO",
(SELECT DISTINCT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 9 AND T5."ItemCode" = T4."ItemCode")AS "VENTA SEPTIEMBRE",
(SELECT DISTINCT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 10 AND T5."ItemCode" = T4."ItemCode")AS "VENTA OCTUBRE",
(SELECT DISTINCT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 11 AND T5."ItemCode" = T4."ItemCode")AS "VENTA NOVIEMBRE",
(SELECT SUM(T5."Quantity")
FROM INV1 T5 WHERE MONTH(T5."DocDate") = 12 AND T5."ItemCode" = T4."ItemCode")AS "VENTA DICIEMBRE"
FROM OWHS T0 INNER JOIN OWOR T1 ON T0."WhsCode" = T1."Warehouse"
INNER JOIN OITW T2 ON T0."WhsCode" = T2."WhsCode"
INNER JOIN FCT1 T3 ON T0."WhsCode" = T3."WhsCode"
INNER JOIN OITM T4 ON T2."ItemCode" = T4."ItemCode"
LEFT JOIN INV1 T5 ON T5."ItemCode" = T4."ItemCode"
INNER JOIN OINV T6 ON T5."DocEntry" = T6."DocEntry"
LEFT JOIN RIN1 T7 ON T7."ItemCode" = T4."ItemCode"
INNER JOIN ORIN T8 ON T7."DocEntry" = T8."DocEntry"
WHERE T6."CANCELED" = 'N' AND T8."CANCELED" = 'N' AND T2."WhsCode" = 'LQ02' OR T2."WhsCode" = 'PT01' OR T2."WhsCode" = 'PT02'
OR T2."WhsCode" = 'PT03' OR T2."WhsCode" = 'PT04' AND T3."Date" BETWEEN [%0] AND [%1];
