 Autor
|
Tema: Suma Algebraica de 2 Select SQL Server 2012 (Leído 3,614 veces)
|
ParKraS
 Desconectado
Mensajes: 3
|
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.  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.
|
|
|
|
« Última modificación: 12 Septiembre 2017, 22:36 pm por ParKraS »
|
En línea
|
|
|
|
rubentexas
Desconectado
Mensajes: 9
|
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.
|
|
|
|
|
En línea
|
|
|
|
ParKraS
Desconectado
Mensajes: 3
|
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.
|
|
|
|
|
En línea
|
|
|
|
rubentexas
Desconectado
Mensajes: 9
|
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*/
|
|
|
|
|
En línea
|
|
|
|
ParKraS
Desconectado
Mensajes: 3
|
Graciassssss! Ahora si me anda de 10.  |
|
|
|
|
En línea
|
|
|
|
|
| Mensajes similares |
|
Asunto |
Iniciado por |
Respuestas |
Vistas |
Último mensaje |
|
|
|
¿Windows Server 2008 o 2012?
Windows
|
motocros_elche
|
3
|
6,516
|
14 Enero 2014, 23:28 pm
por motocros_elche
|
|
|
|
Windows Server 2012 y Ad
Windows
|
Criss009
|
0
|
1,637
|
28 Abril 2014, 19:22 pm
por Criss009
|
|
|
|
[?] Como hago la evaluacion de una funcion algebraica
Programación Visual Basic
|
.:: KsV ::.
|
3
|
1,482
|
19 Enero 2016, 23:17 pm
por fary
|
|
|
|
Problemas SQL Server 2012
.NET (C#, VB.NET, ASP)
|
estebanrios
|
0
|
3,578
|
20 Marzo 2016, 05:25 am
por estebanrios
|
|
|
|
[ayuda]si la suma de fila es igual a la suma de columna imprima es simetrica
Programación C/C++
|
l.calderon609
|
2
|
2,747
|
14 Noviembre 2016, 20:47 pm
por l.calderon609
|
 |
