Ayuda en Agrupamiendo de Query

[Former Member]

Buen día consultores, tengo el siguiente Query, donde me están solicitando, un Reporte de Consumo de Materiales por Persona que Autoriza, por Mes y con Totales por Almacén

SELECT  PA.Name as 'Autoriza', T3.WhsName as 'Ud. de Negocio', T0.doctotal-T0.vatsum as Subtotal, T0.VatSum as 'IVA', T0.DocTotal as 'Total', (T0.doctotal-T0.vatsum)-T0.GrosProfit as Costo,((t0.doctotal-t0.vatsum)-((t0.doctotal-t0.vatsum)-T0.GrosProfit)) as 'Utilidad',  (100-((t0.doctotal-t0.vatsum)-T0.GrosProfit)/( t0.doctotal-t0.vatsum)*100) as 'Porcentaje'
 
FROM [dbo].[ODLN] T0  
INNER JOIN [dbo].[OCRD] T1 ON T0.CardCode = T1.CardCode 
INNER JOIN [dbo].[@AUTMATERIAL] PA ON T0.U_PerAutMat=PA.Code
INNER JOIN [dbo].[DLN1] EL ON EL.DocEntry =T0.DocEntry 
INNER JOIN [dbo].[OWHS] T3 ON EL.WhsCode = T3.WhsCode

WHERE T0.DocdATE BETWEEN [%0] AND [%1] AND EL.WhsCode =[%2] AND EL.TargetType  <> 16

GROUP BY PA.Name, T3.WhsName, T0.doctotal, T0.vatsum,T0.GrosProfit

ORDER BY PA.Name

p.d. Así es como me solicitan la información

Usuario       Unidad de Negocio	       Venta	       Costo	  Utilidad	%
Cañedo Quiroz Jesus	Cananea	       49,781 	     39,376 	   10,404 	20.9%
Corella Figueroa Adrian	Cananea	    1,774,026 	   1,394,384 	   379,642 	21.4%
					
Totales		                    1,823,807       1,433,761       390,046     21.4%

Alguno me puede ayudar a que la información la pueda mostrar tal como me la están pidiendo en un query.

Gracias!

Venus Jiménez

[former_member188440 · Accepted solution]

Mete un union al final, donde traigas solo los calculos, solo recuerda que debes traer el mismo tipo y numero de datos que en el primer select, solo necesitas unos SUM, el where se queda igual en ambas secciones del codigo

ejemplo

select campo1, campo2, campo3 where
union

select '','',sum(campo3)where

order by .....

Las comillas son solo para que sigas uniendo el mismo tipo de dato y el mismo numero de columnas

[Former Member · Accepted solution]

Gracias Mauricio... Según te entendí agregué esto al query:

SELECT  PA.Name as 'Autoriza', T3.WhsName as 'Ud. de Negocio', T0.DocNum as 'Doc SAP', T0.doctotal-T0.vatsum as Subtotal, T0.VatSum as 'IVA', T0.DocTotal as 'Total', (T0.doctotal-T0.vatsum)-T0.GrosProfit as Costo,((t0.doctotal-t0.vatsum)-((t0.doctotal-t0.vatsum)-T0.GrosProfit)) as 'Utilidad',  (100-((t0.doctotal-t0.vatsum)-T0.GrosProfit)/( t0.doctotal-t0.vatsum)*100) as 'Porcentaje' 
FROM [dbo].[ODLN] T0  
INNER JOIN [dbo].[OCRD] T1 ON T0.CardCode = T1.CardCode 
INNER JOIN [dbo].[@AUTMATERIAL] PA ON T0.U_PerAutMat=PA.Code
INNER JOIN [dbo].[DLN1] EL ON EL.DocEntry =T0.DocEntry 
INNER JOIN [dbo].[OWHS] T3 ON EL.WhsCode = T3.WhsCode

WHERE T0.DocdATE BETWEEN [%0] AND [%1] AND EL.WhsCode =[%2] AND EL.TargetType  <> 16

UNION

SELECT  PA.Name as 'Autoriza', T3.WhsName as 'Ud. de Negocio', T0.DocNum as 'Doc SAP',
SUM(T0.doctotal)-SUM(T0.vatsum) as Subtotal, 
SUM(T0.VatSum) as 'IVA', 
SUM(T0.DocTotal) as 'Total', 
(SUM(T0.doctotal)-SUM(T0.vatsum))-SUM(T0.GrosProfit) as Costo,
((SUM(T0.doctotal)-SUM(T0.vatsum))-((SUM(T0.doctotal)-SUM(T0.vatsum))-SUM(T0.GrosProfit))) as 'Utilidad',
(100-((SUM(T0.doctotal)-SUM(T0.vatsum))-SUM(T0.GrosProfit))/(SUM(T0.doctotal)-SUM(T0.vatsum)) *100) as 'Porcentaje' 
FROM [dbo].[ODLN] T0  
INNER JOIN [dbo].[OCRD] T1 ON T0.CardCode = T1.CardCode 
INNER JOIN [dbo].[@AUTMATERIAL] PA ON T0.U_PerAutMat=PA.Code
INNER JOIN [dbo].[DLN1] EL ON EL.DocEntry =T0.DocEntry 
INNER JOIN [dbo].[OWHS] T3 ON EL.WhsCode = T3.WhsCode

WHERE T0.DocdATE BETWEEN [%0] AND [%1] AND EL.WhsCode =[%2] AND EL.TargetType  <> 16

GROUP BY PA.Name, T3.WhsName, T0.Docnum, T0.doctotal, T0.vatsum,T0.GrosProfit

ORDER BY PA.Name

Sin el Union me despliega lo siguiente:

#	Autoriza	Subtotal	IVA	Total	Costo	Utilidad	Porcentaje
1	ABEL CORDOVA	18,918.68	3,026.99	21,945.67	16,823.77	2,094.91	11.07
2	ADRIAN RODRIGUEZ	13,560.05	2,169.61	15,729.66	15,182.93	-1,622.88	-11.97
3	BRIJIDO PERAL	4,916.02	786.56	5,702.58	2,700.86	2,215.16	45.06
4	BRIJIDO PERAL	5,127.62	820.42	5,948.04	2,852.16	2,275.46	44.38
5	BRIJIDO PERAL	18,155.77	2,904.92	21,060.69	13,628.97	4,526.80	24.93
6	BRIJIDO PERAL	64,420.79	10,307.33	74,728.12	40,938.63	23,482.16	36.45

Con el Union me despliega así:

#	Autoriza	Subtotal	IVA	Total	Costo	Utilidad	Porcentaje
1	ABEL CORDOVA	18,918.68	3,026.99	21,945.67	16,823.77	2,094.91	11.07
2	ABEL CORDOVA	283,780.20	45,404.85	329,185.05	252,356.55	31,423.65	11.07
3	ADRIAN RODRIGUEZ	13,560.05	2,169.61	15,729.66	15,182.93	-1,622.88	-11.97
4	ADRIAN RODRIGUEZ	230,520.85	36,883.37	267,404.22	258,109.81	-27,588.96	-11.97
5	BRIJIDO PERAL	4,916.02	786.56	5,702.58	2,700.86	2,215.16	45.06
6	BRIJIDO PERAL	5,127.62	820.42	5,948.04	2,852.16	2,275.46	44.38
7	BRIJIDO PERAL	18,155.77	2,904.92	21,060.69	13,628.97	4,526.80	24.93
8	BRIJIDO PERAL	34,412.14	5,505.92	39,918.06	18,906.02	15,506.12	45.06
9	BRIJIDO PERAL	64,420.79	10,307.33	74,728.12	40,938.63	23,482.16	36.45
10	BRIJIDO PERAL	71,786.68	11,485.88	83,272.56	39,930.24	31,856.44	44.38
11	BRIJIDO PERAL	381,271.17	61,003.32	442,274.49	286,208.37	95,062.80	24.93
12	BRIJIDO PERAL	966,311.85	154,609.95	1,120,921.80	 614,079.45	352,232.40	36.45

Creo que aquí, el orden de los factores, sí altera el producto.

[former_member188440 · Accepted solution]

Mas o menos lo que te sugeria era asi

select docnum, cardcode, doctotal from OINV where CreateDate >='20101201' and CardCode ='C2F001'
union
select '','totales',SUM(doctotal) from OINV where  CreateDate >='20101201' and cardcode='C2F001'

Intentalo cambiando el cardcode por algun cliente tuyo y revisa el resultado, es mas o menos la logica sugerida

[Former Member · Accepted solution]

Gracias Mauricio, ya quedó !