Amigos buenos días alguien me puede colaborar con esta consulta.
la consulta esta bien pero me genera los datos positivos y negativos ya que los necesito para restar las notas crédito pero necesito agrupar para que no aparezcan los negativos pero que de igual manera los reste muchas gracias
no se si me hice entender
SELECT T2."ItemCode" AS "NOMBRE REF", T2."ItemName" AS "DESCRIPCION REF", sum(T1."Quantity") as "CANTIDAD DE EMPAQUE", T2."U_UNID_PRESENT" AS "UNIDAD PRESENTACION", SUM(T1."Quantity"*T2."U_UNID_PRESENT") AS "TOTAL UNIDADES", T3."ItmsGrpNam" as "GRUPO" FROM OINV T0 INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode" INNER JOIN OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod" WHERE T0."CANCELED" = 'N' AND T0."DocDate" >=[%0] and T0."DocDate" <=[%1] GROUP BY T2."ItemName", T2."ItemCode", T2."U_UNID_PRESENT",T3."ItmsGrpNam"
UNION
SELECT T2."ItemCode" AS "NOMBRE REF", T2."ItemName" AS "DESCRIPCION REF",sum(-T1."Quantity") as "CANTIDAD DE EMPAQUE", T2."U_UNID_PRESENT" AS "UNIDAD PRESENTACION", SUM(-T1."Quantity"*(-T2."U_UNID_PRESENT")) AS "TOTAL UNIDADES", T3."ItmsGrpNam" as "GRUPO" FROM ORIN T0 INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode" INNER JOIN OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod" WHERE T0."CANCELED" = 'N' AND T0."DocDate" >=[%0] and T0."DocDate" <=[%1] GROUP BY T2."ItemName", T2."ItemCode", T2."U_UNID_PRESENT",T3."ItmsGrpNam"