Título: Suma Algebraica de 2 Select SQL Server 2012 Publicado por: ParKraS en 12 Septiembre 2017, 22:34 pm Buenos Tardes, tengo los siguientes Select en SQL Server 2012, y necesito hacer una suma algebraica de ambos Select del los campos cantidad y total, ya que el resultado de cada uno me da valores positivos y negativos.
SELECT T0.U_CTS_Vendedor, T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo],T5.SLPNAME, T1.[U_bgn_grupoarticulo], SUM(T1.[Quantity]) AS CANTIDAD, SUM(T1.[LineTotal]) AS TOTAL FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor WHERE ( T0.[DocDate] >= '[%1]' OR '[ %1]'=' ') and (T0.[DocDate] <= '[%2]' OR '[%2]'=' ') GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo] UNION ALL SELECT T0.U_CTS_Vendedor,T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo], T5.SLPNAME, T1.[U_bgn_grupoarticulo],SUM((T1.[Quantity]*(-1))) AS CANTIDAD, SUM((T1.[LineTotal]*(-1))) AS TOTAL FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor WHERE ( T0.[DocDate] >= '[%1]' OR '[ %1]'=' ') and (T0.[DocDate] <= '[%2]' OR '[ %2]'=' ') GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo] ORDER BY T5.[U_CTS_Tienda],T5.SLPNAME,T1.[U_bgn_grupoarticulo] El resultado de estos Select es el siguiente: T-010122 V20026844 T-01 5 PEDRO ROPA 3 30.000 T-010122 V20026844 T-01 5 PEDRO ROPA -1 -10.000 Necesito que se vea así: T-010122 V20026844 T-01 5 PEDRO ROPA 2 20.000 Agradecería mucho su ayuda ya que no tengo mucha experiencia con SQL. :D Intente hacer esto y no me funciono: SELECT * FROM ( SELECT T0.U_CTS_Vendedor, T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo],T5.SLPNAME, T1.[U_bgn_grupoarticulo], SUM(T1.[Quantity]) AS CANTIDAD, SUM(T1.[LineTotal]) AS TOTAL FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ') GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo] UNION ALL SELECT T0.U_CTS_Vendedor,T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo], T5.SLPNAME, T1.[U_bgn_grupoarticulo],SUM((T1.[Quantity]*(-1))) AS CANTIDAD, SUM((T1.[LineTotal]*(-1))) AS TOTAL FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ') GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo] ) Me da error de Sintaxis incorrecta cerca de ')' en la linea 12 que vendría siendo el ultimo paréntesis. Título: Re: Suma Algebraica de 2 Select SQL Server 2012 Publicado por: rubentexas en 13 Septiembre 2017, 03:30 am podrias crear una vista con los dos select unidos y finalmente agrupar el resultado para que de los 20.
SELECT * FROM ( SELECT T0.U_CTS_Vendedor, T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo],T5.SLPNAME, T1.[U_bgn_grupoarticulo], SUM(T1.[Quantity]) AS CANTIDAD, SUM(T1.[LineTotal]) AS TOTAL FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ') GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo] UNION ALL SELECT T0.U_CTS_Vendedor,T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo], T5.SLPNAME, T1.[U_bgn_grupoarticulo],SUM((T1.[Quantity]*(-1))) AS CANTIDAD, SUM((T1.[LineTotal]*(-1))) AS TOTAL FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ') GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo] ) AS EL_PARENTESIS te falta el alias de la tabla que estas simulando con los parentesis. Esto te dara de nuevo 2 lineas, PERO finalmente pones de nuevo el GROUP y sale OK. lo mas simple cuando hago esto es ver el select como sale y luego poner los parentesis, el alias y agrupar por los nombres de los campos que salen de ese select. GROUP BY U_CTS_Tienda,SLPNAME,U_CTS_Cedula,U_CTS_Vendedor,U_CTS_Cargo,U_bgn_grupoarticulo Recorda que el select afuera del parentesis ahora tendran el alias "EL_PARENTESIS" que le puse a proposito para mostrar el bug. Título: Re: Suma Algebraica de 2 Select SQL Server 2012 Publicado por: ParKraS en 13 Septiembre 2017, 04:23 am Según entiendo quedaría así:
SELECT * FROM ( SELECT T0.U_CTS_Vendedor, T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo],T5.SLPNAME, T1.[U_bgn_grupoarticulo], SUM(T1.[Quantity]) AS CANTIDAD, SUM(T1.[LineTotal]) AS TOTAL FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ') GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo] UNION ALL SELECT T0.U_CTS_Vendedor,T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo], T5.SLPNAME, T1.[U_bgn_grupoarticulo],SUM((T1.[Quantity]*(-1))) AS CANTIDAD, SUM((T1.[LineTotal]*(-1))) AS TOTAL FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ') GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo] ) AS SUMA GROUP BY SUMA.[U_CTS_Tienda], SUMA.SLPNAME,SUMA.[U_CTS_Cedula],SUMA.U_CTS_Vendedor,SUMA.[U_CTS_Cargo],SUMA.[U_bgn_grupoarticulo],SUMA.CANTIDAD,SUMA.TOTAL Hasta aquí todo bien pero en que punto realizo la suma de los campos CANTIDAD Y TOTAL, El Group by me agrupa pero no me hace la operación matemática, me sigue mostrando las dos lineas. Título: Re: Suma Algebraica de 2 Select SQL Server 2012 Publicado por: rubentexas en 13 Septiembre 2017, 04:58 am te falto el SUM
SELECT /* NO "select * "si usas asi no el group no funcionara como queres.*/ SUMA.[U_CTS_Tienda], SUMA.SLPNAME,SUMA.[U_CTS_Cedula],SUMA.U_CTS_Vendedor,SUMA.[U_CTS_Cargo],SUMA.[U_bgn_grupoarticulo],SUM(SUMA.CANTIDAD), SUM(SUMA.TOTAL) /*fijate que puse SUM(campo) esta es la funcion que realmente sumara.*/ FROM ( SELECT T0.U_CTS_Vendedor, T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo],T5.SLPNAME, T1.[U_bgn_grupoarticulo], SUM(T1.[Quantity]) AS CANTIDAD, SUM(T1.[LineTotal]) AS TOTAL FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ') GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo] UNION ALL SELECT T0.U_CTS_Vendedor,T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo], T5.SLPNAME, T1.[U_bgn_grupoarticulo],SUM((T1.[Quantity]*(-1))) AS CANTIDAD, SUM((T1.[LineTotal]*(-1))) AS TOTAL FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ') GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo] ) AS SUMA GROUP BY SUMA.[U_CTS_Tienda], SUMA.SLPNAME,SUMA.[U_CTS_Cedula],SUMA.U_CTS_Vendedor,SUMA.[U_CTS_Cargo],SUMA.[U_bgn_grupoarticulo] /* de aca sacas los campos que tienen funcion y pones la funcion en el select*/ Título: Re: Suma Algebraica de 2 Select SQL Server 2012 Publicado por: ParKraS en 13 Septiembre 2017, 14:15 pm Graciassssss! :D Ahora si me anda de 10. ;-)
|