ayuda con query y totales
Publicado por latrew (3 intervenciones) el 03/08/2017 17:09:30
buen dia,
Tengo un query que me trae facturas y notas de credito....hice un union para que me mostrar agrupado por familia de articulo, el detalle es que me trae el total de facturas y el total de notas de credito (en negativo) .....lo meti esto dentro de un subquery para hacerle un sum y que me trajera solo un resultado, sin embargo sigue mostrando dos cantidades.
el query es este:
Select Name, sum(isnull(Total,0)) as Total, sum(isnull(GBruta,0)) as GBruta, ProdA FROM (
SELECT T3.[ItmsGrpNam] as Name, sum(IsNULL(T1.[TotalSumSy],0)) as Total, T3.U_gpo1 as ProdA,sum(IsNULL(T1.GrssProfSC,0)) as 'GBruta'
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]
INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]
WHERE T0.[DocDate] >='20170101' AND T0.[DocDate] <='20170731'
GROUP BY T3.[ItmsGrpNam], T3.U_gpo1
UNION ALL
SELECT T3.[ItmsGrpNam] as Name, sum(IsNULL(T1.[TotalSumSy],0))*-1 as Total, T3.U_gpo1 as ProdA,sum(IsNULL(T1.GrssProfSC,0))*-1 as 'GBruta'
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]
INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]
WHERE T0.[DocDate] >='20170101' AND T0.[DocDate] <='20170731' AND T1.BaseType<>'203'
GROUP BY T3.[ItmsGrpNam], T3.U_gpo1)xx1
GROUP BY Name, ProdA, GBruta
El resultado de este query es:
AC ADHESIVOS -905.46 -280.06 B
AC ADHESIVOS 24475.14 7233.94 B
AC CINTAS -449.19 -100.53 B
AC CINTAS 20275.3 6017.07 B
Busco que ya me de solo una cantidad, el subquery no lo hace....no se por que.
Gracias
Tengo un query que me trae facturas y notas de credito....hice un union para que me mostrar agrupado por familia de articulo, el detalle es que me trae el total de facturas y el total de notas de credito (en negativo) .....lo meti esto dentro de un subquery para hacerle un sum y que me trajera solo un resultado, sin embargo sigue mostrando dos cantidades.
el query es este:
Select Name, sum(isnull(Total,0)) as Total, sum(isnull(GBruta,0)) as GBruta, ProdA FROM (
SELECT T3.[ItmsGrpNam] as Name, sum(IsNULL(T1.[TotalSumSy],0)) as Total, T3.U_gpo1 as ProdA,sum(IsNULL(T1.GrssProfSC,0)) as 'GBruta'
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]
INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]
WHERE T0.[DocDate] >='20170101' AND T0.[DocDate] <='20170731'
GROUP BY T3.[ItmsGrpNam], T3.U_gpo1
UNION ALL
SELECT T3.[ItmsGrpNam] as Name, sum(IsNULL(T1.[TotalSumSy],0))*-1 as Total, T3.U_gpo1 as ProdA,sum(IsNULL(T1.GrssProfSC,0))*-1 as 'GBruta'
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]
INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]
WHERE T0.[DocDate] >='20170101' AND T0.[DocDate] <='20170731' AND T1.BaseType<>'203'
GROUP BY T3.[ItmsGrpNam], T3.U_gpo1)xx1
GROUP BY Name, ProdA, GBruta
El resultado de este query es:
AC ADHESIVOS -905.46 -280.06 B
AC ADHESIVOS 24475.14 7233.94 B
AC CINTAS -449.19 -100.53 B
AC CINTAS 20275.3 6017.07 B
Busco que ya me de solo una cantidad, el subquery no lo hace....no se por que.
Gracias
Valora esta pregunta
0