COMO AÑADIR UN CAMPO SELECT DISTINTC A UNA TABLA CON VALORES ACUMULADOS

Question

Hola,

Trabajo con SQL Server y tengo el siguiente problema:

Con la ayuda de este foro di forma hace unos meses a un script el cual me calculaba las ventas acumuladas tanto absolutas como porcentuales partiendo de una tabla donde se desglosaban las productos vendidos contenidos en cada albaran o documento de entrega. Este script es así:

DECLARE @Ventas AS TABLE
(
   SerieNumero nvarchar(12),
   FechaAlbaran  Date,
   IdCliente Integer,
   NombreCliente Nvarchar(100),
   IdArticulo integer,
   ImporteVenta decimal(18,5)

   
);

INSERT INTO @Ventas
VALUES('A1','01/01/2016',1,'pepe',8,24.5),
 ('A1','01/01/2016',1,'pepe',7,44.5),
 ('A2','02/01/2016',1,'pepe',4,14),
 ('A3','03/01/2016',1,'pepe',3,1.5),
 ('A3','03/01/2016',1,'pepe',1,32.5),
 ('A4','01/01/2016',5,'antonio',12,11.5),
 ('A24','01/01/2016',3,'lucas',11,46),
 ('A5','02/01/2016',5,'antonio',14,9.24),
 ('A6','04/01/2016',1,'pepe',1,32.5),
 ('A6','04/01/2016',1,'pepe',12,11.5),
 ('A7','04/01/2016',5,'antonio',14,9.24)


;with CTE1 AS ( SELECT  CASE WHEN (GROUPING(NombreCliente) = 1) THEN (COUNT(*) OVER())  ELSE ROW_NUMBER() OVER(ORDER BY SUM(ImporteVenta) DESC )  -1 END AS Orden,  
  CASE   
  WHEN (GROUPING(NombreCliente) = 1) THEN NULL   ELSE MAX(IdCliente)  END AS ID, 
   CASE   WHEN (GROUPING(NombreCliente) = 1) THEN 'TOTAL'
    ELSE NombreCliente  
	END AS NombreCliente, 
    CONVERT(Decimal(9,2),CAST(SUM(ImporteVenta) AS money)) AS Ventas,
	  SUM(ImporteVenta) AS VentaSinSimbolo, 
	 CONVERT(decimal(9,2), CONVERT(decimal(9,2), (SUM(ImporteVenta)  * 100) / (SELECT SUM(ImporteVenta)  FROM @Ventas)))  AS PorcentajeVentas  
	
	 FROM @Ventas 
	 GROUP BY  ROLLUP(NombreCliente)), 

	  CTE2 AS ( SELECT  CTE1.Orden, CTE1.NombreCliente,CTE1.Ventas, CTE1.PorcentajeVentas,
	  CASE CTE1.NombreCliente
	    WHEN 'TOTAL' 	  THEN CONVERT(DECIMAL(9,2),CAST(VentaSinSimbolo AS money ))
	   ELSE CONVERT(DECIMAL(9,2),CAST(SUM(VentaSinSimbolo) OVER (ORDER BY CTE1.Orden ASC ROWS UNBOUNDED PRECEDING ) AS money ) ) END
	    AS VentasAcumuladas, 

		 CASE CTE1.NombreCliente  WHEN 'TOTAL' THEN CONVERT(Decimal(9,2),CAST(PorcentajeVentas AS money ))  
		 Else CONVERT(Decimal(9,2), CAST(SUM(PorcentajeVentas) OVER (ORDER BY CTE1.Orden ASC ROWS UNBOUNDED PRECEDING ) AS money ))   END 
		 AS PorcentajeVentasAcumulado,   CONVERT(decimal(9,2), Orden/(MAX(Orden) OVER() * 1.0) * 100) AS PorcentajeClientes  FROM  CTE1 )
		  
select Orden ,NombreCliente as [Nombre Cliente],Ventas,CONVERT(DECIMAL(9,2),CAST(PorcentajeVentas AS money )) as [% Ventas],VentasAcumuladas as [Ventas Acumuladas],
 PorcentajeVentasAcumulado as [% Ventas Acumuladas], PorcentajeClientes as [% Clientes]
from Cte2 order by orden Asc

El resultado de esto es:

Como veis agrupa las ventas por NombreCliente y añade una columnas donde se ven las ventas acumuladas.

Pues bien, necesito insertar dos nuevas columnas:

1- Albaranes: Es el numero de SerieNumero distintos que hay en la tabla para ese IdCliente.

2- Venta Media Albarán: La división entre el campo ventas y el nuevo campo "albaranes".

Básicamente mi problema me viene en el campo albaranes que no sé bien como desarrollar. El otro campo es una simple división.

He probado con 

 SUM(a.Importe) OVER(PARTITION BY a.IdCliente) / (SELECT COUNT(DISTINCT NumeroAlbaran) FROM @Ventas v WHERE (v.IdCliente = a.IdCliente) )

DECLARE @Ventas AS TABLE
(
   SerieNumero nvarchar(12),
   FechaAlbaran  Date,
   IdCliente Integer,
   NombreCliente Nvarchar(100),
   IdArticulo integer,
   ImporteVenta decimal(18,5)

   
);

INSERT INTO @Ventas
VALUES('A1','01/01/2016',1,'pepe',8,24.5),
 ('A1','01/01/2016',1,'pepe',7,44.5),
 ('A2','02/01/2016',1,'pepe',4,14),
 ('A3','03/01/2016',1,'pepe',3,1.5),
 ('A3','03/01/2016',1,'pepe',1,32.5),
 ('A4','01/01/2016',5,'antonio',12,11.5),
 ('A24','01/01/2016',3,'lucas',11,46),
 ('A5','02/01/2016',5,'antonio',14,9.24),
 ('A6','04/01/2016',1,'pepe',1,32.5),
 ('A6','04/01/2016',1,'pepe',12,11.5),
 ('A7','04/01/2016',5,'antonio',14,9.24)


;with CTE1 AS ( SELECT  CASE WHEN (GROUPING(NombreCliente) = 1) THEN (COUNT(*) OVER())  ELSE ROW_NUMBER() OVER(ORDER BY SUM(ImporteVenta) DESC )  -1 END AS Orden,  
  CASE   
  WHEN (GROUPING(NombreCliente) = 1) THEN NULL   ELSE MAX(IdCliente)  END AS ID, 
   CASE   WHEN (GROUPING(NombreCliente) = 1) THEN 'TOTAL'
    ELSE NombreCliente  
	END AS NombreCliente, 
    CONVERT(Decimal(9,2),CAST(SUM(ImporteVenta) AS money)) AS Ventas,

	 SUM(v.ImporteVenta) OVER(PARTITION BY a.IdCliente) / (SELECT COUNT(DISTINCT SerieNumero) FROM @Ventas v WHERE (v.IdCliente = IdCliente) ) as albaranes,

	  SUM(ImporteVenta) AS VentaSinSimbolo, 
	 CONVERT(decimal(9,2), CONVERT(decimal(9,2), (SUM(ImporteVenta)  * 100) / (SELECT SUM(ImporteVenta)  FROM @Ventas)))  AS PorcentajeVentas  
	
	 FROM @Ventas v
	 GROUP BY  ROLLUP(NombreCliente)), 

	  CTE2 AS ( SELECT  CTE1.Orden, CTE1.NombreCliente,CTE1.Ventas, CTE1.PorcentajeVentas,
	  CASE CTE1.NombreCliente
	    WHEN 'TOTAL' 	  THEN CONVERT(DECIMAL(9,2),CAST(VentaSinSimbolo AS money ))
	   ELSE CONVERT(DECIMAL(9,2),CAST(SUM(VentaSinSimbolo) OVER (ORDER BY CTE1.Orden ASC ROWS UNBOUNDED PRECEDING ) AS money ) ) END
	    AS VentasAcumuladas, 

		 CASE CTE1.NombreCliente  WHEN 'TOTAL' THEN CONVERT(Decimal(9,2),CAST(PorcentajeVentas AS money ))  
		 Else CONVERT(Decimal(9,2), CAST(SUM(PorcentajeVentas) OVER (ORDER BY CTE1.Orden ASC ROWS UNBOUNDED PRECEDING ) AS money ))   END 
		 AS PorcentajeVentasAcumulado,   CONVERT(decimal(9,2), Orden/(MAX(Orden) OVER() * 1.0) * 100) AS PorcentajeClientes  FROM  CTE1 )
		  
select Orden ,NombreCliente as [Nombre Cliente],Ventas,CONVERT(DECIMAL(9,2),CAST(PorcentajeVentas AS money )) as [% Ventas],VentasAcumuladas as [Ventas Acumuladas],
 PorcentajeVentasAcumulado as [% Ventas Acumuladas], PorcentajeClientes as [% Clientes]
from Cte2 order by orden Asc

Pero recibo el error: 

Mens. 4104, Nivel 16, Estado 1, Línea 35
El identificador formado por varias partes "a.IdCliente" no se pudo enlazar.

¿ qué puede estar equivocado? ¿alguna idea?

Gracias

Angel

Answer 1

Angel,

Por lo pronto voy a senialar por que el error y alguno que otro concepto.

En la CTE1 la columna [v.IdCliente] no forma parte de la lista de columnas en la clausula SELECT y por lo tanto no tiene sentido que la uses en la clausula OVER. Recuerda la clausula OVER trabaja sobre el resultado del query, que en tu caso no incluye esa columna.

Una forma de solucionarlo seria incluir esta columna en la lista de columnas de la clasula SELECT pero entonces tendras que agregarla a la agrupacion y redefinir tus agrupaciones dado que ROLLUP incluira un nivel no reconocido en tus expresiones CASE que referencian la funcion GROUPING.

SELECT v.IdCliente,..., SUM(SUM(v.ImporteVenta)) OVER(PARTITION BY v.IdCliente) ...
GROUP BY ROLLUP(v.IdCliente)

Fijate que uso SUM(SUM(...)) pues la funcion interna es para la agrupacion y la externa para la particion del resultado (clasula OVER). Ahora, si agrupas por v.IdCliente entonces el resultado solo tendra una fila por cada IdCliente y por lo tanto la particion mediante la clausula OVER contendra solo una fila. Puedes deshacerte de su uso en este caso.

SELECT v.IdCliente,..., SUM(v.ImporteVenta) / ... 
GROUP BY ROLLUP(v.IdCliente)

Ahora deberas ajustar todas las referencias a la funcion GROUPING puesto que esta nueva columna marca el tope del rodaje.

El mismo concepto de agrupacion (group by) y agregado con ventana puede ser aplicado a la linea:

CONVERT(decimal(9,2), SUM(v.ImporteVenta) * 100.00 / (SELECT SUM(v2.ImporteVenta) FROM @Ventas AS v2)) AS PorcentajeVentas  

pues el gran total lo puedes sacar sin tener que usar el subquery.

CONVERT(decimal(9,2), SUM(v.ImporteVenta) * 100.00 / SUM(SUM(v.ImporteVenta)) OVER()) AS PorcentajeVentas  

Quedando el codigo asi:

;WITH CTE1 AS ( 
SELECT
	CASE   
	WHEN GROUPING(v.IdCliente) = 1 THEN 'TOTAL'
    ELSE CAST(v.IdCliente AS varchar(15))  
	END AS IdCliente,
	 
	CASE 
	WHEN GROUPING(v.IdCliente) = 1 THEN COUNT(*) OVER()
	ELSE ROW_NUMBER() OVER(ORDER BY SUM(v.ImporteVenta) DESC ) - 1 
	END AS Orden,

	CASE   
	WHEN (GROUPING(v.IdCliente) = 1) THEN ''
	ELSE MAX(v.NombreCliente)  
	END AS NombreCliente,

    CONVERT(decimal(9,2),CAST(SUM(ImporteVenta) AS money)) AS Ventas,

	SUM(v.ImporteVenta) / NULLIF(COUNT(DISTINCT v.SerieNumero), 0) AS albaranes,

	SUM(ImporteVenta) AS VentaSinSimbolo,

	CONVERT(decimal(9,2), SUM(v.ImporteVenta) * 100.00 / SUM(SUM(v.ImporteVenta)) OVER()) AS PorcentajeVentas  
FROM 
	@Ventas v
GROUP BY
	ROLLUP(v.IdCliente)
), 
CTE2 AS (
SELECT
	CTE1.Orden, 
	CTE1.IdCliente,
	CTE1.NombreCliente,
	CTE1.Ventas, 
	CTE1.PorcentajeVentas,

	CASE CTE1.IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2),CAST(SUM(VentaSinSimbolo) OVER (ORDER BY CTE1.Orden ASC ROWS UNBOUNDED PRECEDING ) AS money ) )
	ELSE CONVERT(decimal(9,2),CAST(VentaSinSimbolo AS money )) 
	END AS VentasAcumuladas,

	CASE CTE1.IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2), CAST(SUM(PorcentajeVentas) OVER (ORDER BY CTE1.Orden ASC ROWS UNBOUNDED PRECEDING ) AS  money ))   
	ELSE CONVERT(decimal(9,2),CAST(PorcentajeVentas AS money ))
	END AS PorcentajeVentasAcumulado,   

	CONVERT(decimal(9,2), Orden/(MAX(Orden) OVER() * 1.0) * 100) AS PorcentajeClientes,

	CASE CTE1.IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2), SUM(CTE1.albaranes) OVER (ORDER BY CTE1.Orden ASC ROWS UNBOUNDED PRECEDING ))
	ELSE CONVERT(decimal(9,2), CTE1.albaranes)  
	END AS albaranes
FROM  
	CTE1 
)
SELECT
    Orden,
	CTE2.IdCliente,
    NombreCliente AS [Nombre Cliente],
    Ventas,
    CONVERT(decimal(9, 2), CAST(PorcentajeVentas AS money)) AS [% Ventas],
    VentasAcumuladas AS [Ventas Acumuladas],
    PorcentajeVentasAcumulado AS [% Ventas Acumuladas],
    PorcentajeClientes AS [% Clientes],
	CTE2.albaranes
FROM
    CTE2
ORDER BY
    Orden ASC;
GO

Te re-estructure las expresiones CASE en la segunda CTE ya que tenias invertido el concepto.

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Answer 2

Angeleci,

No he revisado todo el código que adjuntas pero el cálculo de la columna [VentaMediaAlbaran] según el contexto que muestras debería ser:

SUM(v.ImporteVenta)/COUNT(DISTINCT v.SerieNumero) AS [VentaMediaAlbaran]

Fíjate que agrupas por el cliente, pues basta con sumar las ventas del grupo y dividir entre los distintos números de serie -también del grupo-, según el ejemplo deberías obtener:

---

Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.

Answer 3

Gracias Huncback.

Funciona correctamente. Voy a revisar a fondo lo que me comentas para aprender a dominar bien estas claúsulas.

Angel

Answer 4

Hola Huncback;

Llevo un rato intentando obtener lo siguiente a partir del script que me has pasado:

Estoy intentado ordenar los registros de forma que el total quede abajo del todo. He intentado cambiar algunas partes del script pero no consigo dar con el sitio donde puedo cambiar esto. 

También que estén ordenados de mas a menos Venta Media Por Albarán en vez por el campo Ventas como estaba anteriormente.

He probado:

 DECLARE @Ventas AS TABLE
(
   SerieNumero nvarchar(12),
   FechaAlbaran  Date,
   IdCliente Integer,
   NombreCliente Nvarchar(100),
   IdArticulo integer,
   ImporteVenta decimal(18,5)

   
);

INSERT INTO @Ventas
VALUES('A1','01/01/2016',1,'pepe',8,24.5),
 ('A1','01/01/2016',1,'pepe',7,44.5),
 ('A2','02/01/2016',1,'pepe',4,14),
 ('A3','03/01/2016',1,'pepe',3,1.5),
 ('A3','03/01/2016',1,'pepe',1,32.5),
 ('A4','01/01/2016',5,'antonio',12,11.5),
 ('A24','01/01/2016',3,'lucas',11,46),
 ('A5','02/01/2016',5,'antonio',14,9.24),
 ('A6','04/01/2016',1,'pepe',1,32.5),
 ('A6','04/01/2016',1,'pepe',12,11.5),
 ('A7','04/01/2016',5,'antonio',14,9.24)



;WITH CTE0 AS ( 
SELECT
	CASE   
	WHEN GROUPING(v.IdCliente) = 1 THEN 'TOTAL'
    ELSE CAST(v.IdCliente AS varchar(15))  
	END AS IdCliente,
	 
	--CASE 
	--WHEN GROUPING(v.IdCliente) = 1 THEN COUNT(*) OVER()
	--ELSE ROW_NUMBER() OVER(ORDER BY SUM(v.ImporteVenta) / NULLIF(COUNT(DISTINCT v.SerieNumero), 0) DESC ) - 1 
	--END AS Orden,

	CASE   
	WHEN (GROUPING(v.IdCliente) = 1) THEN ''
	ELSE MAX(v.NombreCliente)  
	END AS NombreCliente,

    CONVERT(decimal(9,2),CAST(SUM(ImporteVenta) AS money)) AS Ventas,

	COUNT(DISTINCT v.SerieNumero) as [Número de Albaranes]

	,SUM(v.ImporteVenta) / NULLIF(COUNT(DISTINCT v.SerieNumero), 0) AS [Venta Media Por Albaran]

	,SUM(ImporteVenta) AS VentaSinSimbolo

	,CONVERT(decimal(9,2), SUM(v.ImporteVenta) * 100.00 / SUM(SUM(v.ImporteVenta)) OVER()) AS PorcentajeVentas  
	,ROW_NUMBER() OVER(ORDER BY SUM(ImporteVenta) / NULLIF(COUNT(DISTINCT SerieNumero), 0) DESC) AS ORDEN
FROM 
	@Ventas v
GROUP BY
	ROLLUP(v.IdCliente)
)



,CTE1 AS (
SELECT
	Orden
	,IdCliente
	,NombreCliente
	,Ventas
	,[Número de Albaranes]
	,[Venta Media Por Albaran]
	,PorcentajeVentas,
    CASE IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2),CAST(SUM(VentaSinSimbolo) OVER (ORDER BY Orden ASC ROWS UNBOUNDED PRECEDING ) AS money ) )
	ELSE CONVERT(decimal(9,2),CAST(VentaSinSimbolo AS money )) 
	END AS VentasAcumuladas,

	CASE IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2), CAST(SUM(PorcentajeVentas) OVER (ORDER BY Orden ASC ROWS UNBOUNDED PRECEDING ) AS  money ))   
	ELSE CONVERT(decimal(9,2),CAST(PorcentajeVentas AS money ))
	END AS PorcentajeVentasAcumulado   

	,CONVERT(decimal(9,2), Orden/(MAX(Orden) OVER() * 1.0) * 100) AS PorcentajeClientes,

	CASE IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2), SUM([Venta Media Por Albaran]) OVER (ORDER BY Orden ASC ROWS UNBOUNDED PRECEDING ))
	ELSE CONVERT(decimal(9,2), [Venta Media Por Albaran])  
	END AS VentaMediaPorAlbaran
FROM  
	CTE0
)

SELECT
    Orden,
	CTE1.IdCliente
    ,NombreCliente AS [Nombre Cliente]
    ,Ventas
	,[Número de Albaranes]
	,[Venta Media Por Albaran]
    ,CONVERT(decimal(9, 2), CAST(PorcentajeVentas AS money)) AS [% Ventas]
    ,VentasAcumuladas AS [Ventas Acumuladas]
    ,PorcentajeVentasAcumulado AS [% Ventas Acumuladas]
    ,PorcentajeClientes AS [% Clientes]

FROM
    CTE1
ORDER BY
    ORDEN;
GO

Lo que obtengo ahora mismo es:

Y necesito obtener:

Mi pregunta es dónde puede radicar mi error. ¿qué no estoy teniendo en cuenta? Por favor, no deseo me den el script reparado sino que me indiquen qué parte del script que estoy probando deberia recomponer. Tengo que intentar hacerlo por mi mismo.

Gracias

Answer 5

Trata:

...
ORDER BY
	CASE WHEN [IdCliente] = 'TOTAL' THEN 1 ELSE 0 END,
	[Venta Media Por Albaran] DESC,
	[IdCliente];
GO

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Answer 6

He probado con:

 

 DECLARE @Ventas AS TABLE
(
   SerieNumero nvarchar(12),
   FechaAlbaran  Date,
   IdCliente Integer,
   NombreCliente Nvarchar(100),
   IdArticulo integer,
   ImporteVenta decimal(18,5)

   
);

INSERT INTO @Ventas
VALUES('A1','01/01/2016',1,'pepe',8,24.5),
 ('A1','01/01/2016',1,'pepe',7,44.5),
 ('A2','02/01/2016',1,'pepe',4,14),
 ('A3','03/01/2016',1,'pepe',3,1.5),
 ('A3','03/01/2016',1,'pepe',1,32.5),
 ('A4','01/01/2016',5,'antonio',12,11.5),
 ('A24','01/01/2016',3,'lucas',11,46),
 ('A5','02/01/2016',5,'antonio',14,9.24),
 ('A6','04/01/2016',1,'pepe',1,32.5),
 ('A6','04/01/2016',1,'pepe',12,11.5),
 ('A7','04/01/2016',5,'antonio',14,9.24)



;WITH CTE0 AS ( 
SELECT
	CASE   
	WHEN GROUPING(v.IdCliente) = 1 THEN 'TOTAL'
    ELSE CAST(v.IdCliente AS varchar(15))  
	END AS IdCliente,
	 
	CASE 
	WHEN GROUPING(v.IdCliente) = 1 THEN COUNT(*) OVER()
	ELSE ROW_NUMBER() OVER(ORDER BY SUM(v.ImporteVenta) / NULLIF(COUNT(DISTINCT v.SerieNumero), 0) DESC )  
	END AS Orden,

	CASE   
	WHEN (GROUPING(v.IdCliente) = 1) THEN ''
	ELSE MAX(v.NombreCliente)  
	END AS NombreCliente,

    CONVERT(decimal(9,2),CAST(SUM(ImporteVenta) AS money)) AS Ventas,

	COUNT(DISTINCT v.SerieNumero) as [Número de Albaranes]

	,SUM(v.ImporteVenta) / NULLIF(COUNT(DISTINCT v.SerieNumero), 0) AS [Venta Media Por Albaran]

	,SUM(ImporteVenta) AS VentaSinSimbolo

	,CONVERT(decimal(9,2), SUM(v.ImporteVenta) * 100.00 / SUM(SUM(v.ImporteVenta)) OVER()) AS PorcentajeVentas  
	--,ROW_NUMBER() OVER(ORDER BY SUM(ImporteVenta) / NULLIF(COUNT(DISTINCT SerieNumero), 0) DESC) AS ORDEN
FROM 
	@Ventas v
GROUP BY
	ROLLUP(v.IdCliente)
)



,CTE1 AS (
SELECT
	Orden
	,IdCliente
	,NombreCliente
	,Ventas
	,[Número de Albaranes]
	,[Venta Media Por Albaran]
	,PorcentajeVentas,
    CASE IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2),CAST(SUM(VentaSinSimbolo) OVER (ORDER BY Orden ASC ROWS UNBOUNDED PRECEDING ) AS money ) )
	ELSE CONVERT(decimal(9,2),CAST(VentaSinSimbolo AS money )) 
	END AS VentasAcumuladas,

	CASE IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2), CAST(SUM(PorcentajeVentas) OVER (ORDER BY Orden ASC ROWS UNBOUNDED PRECEDING ) AS  money ))   
	ELSE CONVERT(decimal(9,2),CAST(PorcentajeVentas AS money ))
	END AS PorcentajeVentasAcumulado   

	,CONVERT(decimal(9,2), Orden/(MAX(Orden) OVER() * 1.0) * 100) AS PorcentajeClientes,

	CASE IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2), SUM([Venta Media Por Albaran]) OVER (ORDER BY Orden ASC ROWS UNBOUNDED PRECEDING ))
	ELSE CONVERT(decimal(9,2), [Venta Media Por Albaran])  
	END AS VentaMediaPorAlbaran
FROM  
	CTE0
)

SELECT
    Orden,
	CTE1.IdCliente
    ,NombreCliente AS [Nombre Cliente]
    ,Ventas
	,[Número de Albaranes]
	,[Venta Media Por Albaran]
    ,CONVERT(decimal(9, 2), CAST(PorcentajeVentas AS money)) AS [% Ventas]
    ,VentasAcumuladas AS [Ventas Acumuladas]
    ,PorcentajeVentasAcumulado AS [% Ventas Acumuladas]
    ,PorcentajeClientes AS [% Clientes]

FROM
    CTE1
ORDER BY
	CASE WHEN [IdCliente] = 'TOTAL' THEN 1 ELSE 0 END,
	[Venta Media Por Albaran] DESC,
	[IdCliente];
GO

Y lo que obtengo es:

Para solventar el problema de las columnas con venta acumuladas creo que debo hacer que el campo orden refleje valores crecientes consecutivos.

Por eso he probado a cambiar 

CASE 
WHEN GROUPING(v.IdCliente) = 1 THEN COUNT(*) OVER()
ELSE ROW_NUMBER() OVER(ORDER BY SUM(v.ImporteVenta) / NULLIF(COUNT(DISTINCT v.SerieNumero), 0) DESC )  
END AS Orden,

por

CASE WHEN (GROUPING(NombreCliente) = 1) THEN (COUNT(*) OVER()) + 1 ELSE ROW_NUMBER() OVER(ORDER BY SUM(ImporteVenta) DESC)-1 END AS Orden,   

es decir

 DECLARE @Ventas AS TABLE
(
   SerieNumero nvarchar(12),
   FechaAlbaran  Date,
   IdCliente Integer,
   NombreCliente Nvarchar(100),
   IdArticulo integer,
   ImporteVenta decimal(18,5)

   
);

INSERT INTO @Ventas
VALUES('A1','01/01/2016',1,'pepe',8,24.5),
 ('A1','01/01/2016',1,'pepe',7,44.5),
 ('A2','02/01/2016',1,'pepe',4,14),
 ('A3','03/01/2016',1,'pepe',3,1.5),
 ('A3','03/01/2016',1,'pepe',1,32.5),
 ('A4','01/01/2016',5,'antonio',12,11.5),
 ('A24','01/01/2016',3,'lucas',11,46),
 ('A5','02/01/2016',5,'antonio',14,9.24),
 ('A6','04/01/2016',1,'pepe',1,32.5),
 ('A6','04/01/2016',1,'pepe',12,11.5),
 ('A7','04/01/2016',5,'antonio',14,9.24)



;WITH CTE0 AS ( 
SELECT
	CASE   
	WHEN GROUPING(v.IdCliente) = 1 THEN 'TOTAL'
    ELSE CAST(v.IdCliente AS varchar(15))  
	END AS IdCliente,
	 
	CASE WHEN (GROUPING(NombreCliente) = 1) THEN (COUNT(*) OVER()) + 1 ELSE ROW_NUMBER() OVER(ORDER BY SUM(ImporteVenta) DESC)-1 END AS Orden,   

	CASE   
	WHEN (GROUPING(v.IdCliente) = 1) THEN ''
	ELSE MAX(v.NombreCliente)  
	END AS NombreCliente,

    CONVERT(decimal(9,2),CAST(SUM(ImporteVenta) AS money)) AS Ventas,

	COUNT(DISTINCT v.SerieNumero) as [Número de Albaranes]

	,SUM(v.ImporteVenta) / NULLIF(COUNT(DISTINCT v.SerieNumero), 0) AS [Venta Media Por Albaran]

	,SUM(ImporteVenta) AS VentaSinSimbolo

	,CONVERT(decimal(9,2), SUM(v.ImporteVenta) * 100.00 / SUM(SUM(v.ImporteVenta)) OVER()) AS PorcentajeVentas  
	--,ROW_NUMBER() OVER(ORDER BY SUM(ImporteVenta) / NULLIF(COUNT(DISTINCT SerieNumero), 0) DESC) AS ORDEN
FROM 
	@Ventas v
GROUP BY
	ROLLUP(v.IdCliente)
)



,CTE1 AS (
SELECT
	Orden
	,IdCliente
	,NombreCliente
	,Ventas
	,[Número de Albaranes]
	,[Venta Media Por Albaran]
	,PorcentajeVentas,
    CASE IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2),CAST(SUM(VentaSinSimbolo) OVER (ORDER BY Orden ASC ROWS UNBOUNDED PRECEDING ) AS money ) )
	ELSE CONVERT(decimal(9,2),CAST(VentaSinSimbolo AS money )) 
	END AS VentasAcumuladas,

	CASE IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2), CAST(SUM(PorcentajeVentas) OVER (ORDER BY Orden ASC ROWS UNBOUNDED PRECEDING ) AS  money ))   
	ELSE CONVERT(decimal(9,2),CAST(PorcentajeVentas AS money ))
	END AS PorcentajeVentasAcumulado   

	,CONVERT(decimal(9,2), Orden/(MAX(Orden) OVER() * 1.0) * 100) AS PorcentajeClientes,

	CASE IdCliente
	WHEN 'TOTAL' THEN CONVERT(decimal(9,2), SUM([Venta Media Por Albaran]) OVER (ORDER BY Orden ASC ROWS UNBOUNDED PRECEDING ))
	ELSE CONVERT(decimal(9,2), [Venta Media Por Albaran])  
	END AS VentaMediaPorAlbaran
FROM  
	CTE0
)

SELECT
    Orden,
	CTE1.IdCliente
    ,NombreCliente AS [Nombre Cliente]
    ,Ventas
	,[Número de Albaranes]
	,[Venta Media Por Albaran]
    ,CONVERT(decimal(9, 2), CAST(PorcentajeVentas AS money)) AS [% Ventas]
    ,VentasAcumuladas AS [Ventas Acumuladas]
    ,PorcentajeVentasAcumulado AS [% Ventas Acumuladas]
    ,PorcentajeClientes AS [% Clientes]

FROM
    CTE1
ORDER BY
	CASE WHEN [IdCliente] = 'TOTAL' THEN 1 ELSE 0 END,
	[Venta Media Por Albaran] DESC,
	[IdCliente];
GO

Pero obtengo el error:

Mens. 8161, Nivel 16, Estado 1, Línea 35
El argumento 1 de la función GROUPING no coincide con ninguna de las expresiones de la cláusula GROUP BY.

¿dónde puede estar el error de lo que hago?

Gracias

Angel

Answer 7

En una de las lineas usas la columna NombreCliente en la funcion GROUPING. Eso no puede ser ya que no agrupas por esa columna.

Mi consejo es que dejes el formateo para el final y solo calcules lo necesario. Es mas si puedes dejar el ROLLUP y Acumulados para la herramienta de reportes seria mejor.

DECLARE @Ventas AS table (
SerieNumero nvarchar(12),
FechaAlbaran  Date,
IdCliente Integer,
NombreCliente Nvarchar(100),
IdArticulo integer,
ImporteVenta decimal(18,5)
);

INSERT INTO @Ventas
VALUES('A1','01/01/2016',1,'pepe',8,24.5),
 ('A1','01/01/2016',1,'pepe',7,44.5),
 ('A2','02/01/2016',1,'pepe',4,14),
 ('A3','03/01/2016',1,'pepe',3,1.5),
 ('A3','03/01/2016',1,'pepe',1,32.5),
 ('A4','01/01/2016',5,'antonio',12,11.5),
 ('A24','01/01/2016',3,'lucas',11,46),
 ('A5','02/01/2016',5,'antonio',14,9.24),
 ('A6','04/01/2016',1,'pepe',1,32.5),
 ('A6','04/01/2016',1,'pepe',12,11.5),
 ('A7','04/01/2016',5,'antonio',14,9.24);

WITH CTE0 AS ( 
SELECT
	IdCliente,
	MAX(NombreCliente) AS NombreCliente,
    SUM(ImporteVenta) AS Ventas,
	COUNT(DISTINCT SerieNumero) as [Número de Albaranes],
	SUM(SUM(ImporteVenta)) OVER() AS TotalVentas
FROM 
	@Ventas
GROUP BY
	IdCliente
)
SELECT
	CASE WHEN GROUPING(IdCliente) = 1 THEN 'Total' ELSE CAST(IdCliente AS varchar(15)) END AS IdCliente,
    CASE WHEN GROUPING(IdCliente) = 1 THEN '' ELSE MAX(NombreCliente) END AS [Nombre Cliente],
    CAST(SUM(Ventas) AS decimal(9, 2)) AS Ventas,
	SUM([Número de Albaranes]) AS [Número de Albaranes],
	CAST(SUM(Ventas) / SUM([Número de Albaranes]) AS decimal(9, 2))AS [Venta Media Por Albaran],
    CAST((SUM(Ventas) * 100.00)/ MIN(TotalVentas) AS decimal(5, 2)) AS [% Ventas],

	CAST(
	CASE WHEN GROUPING(IdCliente) = 1 THEN SUM(Ventas)
	ELSE
		SUM(SUM(Ventas)) OVER(
		ORDER BY GROUPING(IdCliente), SUM(Ventas) / SUM([Número de Albaranes]) DESC, [IdCliente]
		ROWS UNBOUNDED PRECEDING
		) 
	END AS decimal(9, 2)) AS [Ventas Acumuladas],

	CAST(
	(
	CASE WHEN GROUPING(IdCliente) = 1 THEN MIN(TotalVentas)
	ELSE
		SUM(SUM(Ventas)) OVER(
		ORDER BY GROUPING(IdCliente), SUM(Ventas) / SUM([Número de Albaranes]) DESC, [IdCliente]
		ROWS UNBOUNDED PRECEDING
		) 
	END * 100.00
	) / MIN(TotalVentas) AS decimal(5, 2)) AS [% Ventas Acumuladas]
FROM
    CTE0
GROUP BY
	ROLLUP(IdCliente)
ORDER BY
	GROUPING(IdCliente),
	[Venta Media Por Albaran] DESC,
	[IdCliente];
GO

La CTE se usa para calcular las ventas, numero de albaranes y total ventas. Con esto ya puedes calcular los otros valores como venta media por albaran y porciento de ventas.

Los acumulados son mas engorrosos porque deben seguir el mismo orden que usas para el resultado (grouping(IdCliente, [Venta MEdia por Albaran] DESC, IdCliente)), pero como la venta media por albaran es una formula entonces debes usar la formula en la clausula OVER ya que el alias de columna solo es visible en el ORDER BY.

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Answer 8

Hola Hunchback

Gracias por tu aclaración. Tirando de ella he intentado dar forma a un nuevo campo que me marque el orden de cada fila dentro de la tabla. Para ello he probado con

 row_number() over (partition by Idcliente order by (GROUPING(IdCliente),
[Venta Media Por Albaran] DESC),

Exactamente;

DECLARE @Ventas AS table (
SerieNumero nvarchar(12),
FechaAlbaran  Date,
IdCliente Integer,
NombreCliente Nvarchar(100),
IdArticulo integer,
ImporteVenta decimal(18,5)
);

INSERT INTO @Ventas
VALUES('A1','01/01/2016',1,'pepe',8,24.5),
 ('A1','01/01/2016',1,'pepe',7,44.5),
 ('A2','02/01/2016',1,'pepe',4,14),
 ('A3','03/01/2016',1,'pepe',3,1.5),
 ('A3','03/01/2016',1,'pepe',1,32.5),
 ('A4','01/01/2016',5,'antonio',12,11.5),
 ('A24','01/01/2016',3,'lucas',11,46),
 ('A5','02/01/2016',5,'antonio',14,9.24),
 ('A6','04/01/2016',1,'pepe',1,32.5),
 ('A6','04/01/2016',1,'pepe',12,11.5),
 ('A7','04/01/2016',5,'antonio',14,9.24);

WITH CTE0 AS ( 
SELECT
	IdCliente,
	MAX(NombreCliente) AS NombreCliente,
    SUM(ImporteVenta) AS Ventas,
	COUNT(DISTINCT SerieNumero) as [Número de Albaranes],
	SUM(SUM(ImporteVenta)) OVER() AS TotalVentas
FROM 
	@Ventas
GROUP BY
	IdCliente
)
SELECT
 row_number() over (partition by Idcliente order by (GROUPING(IdCliente),
	[Venta Media Por Albaran] DESC),
	[IdCliente]) as orden,
	CASE WHEN GROUPING(IdCliente) = 1 THEN 'Total' ELSE CAST(IdCliente AS varchar(15)) END AS IdCliente,
    CASE WHEN GROUPING(IdCliente) = 1 THEN '' ELSE MAX(NombreCliente) END AS [Nombre Cliente],
    CAST(SUM(Ventas) AS decimal(9, 2)) AS Ventas,
	SUM([Número de Albaranes]) AS [Número de Albaranes],
	CAST(SUM(Ventas) / SUM([Número de Albaranes]) AS decimal(9, 2))AS [Venta Media Por Albaran],
    CAST((SUM(Ventas) * 100.00)/ MIN(TotalVentas) AS decimal(5, 2)) AS [% Ventas],

	CAST(
	CASE WHEN GROUPING(IdCliente) = 1 THEN SUM(Ventas)
	ELSE
		SUM(SUM(Ventas)) OVER(
		ORDER BY GROUPING(IdCliente), SUM(Ventas) / SUM([Número de Albaranes]) DESC, [IdCliente]
		ROWS UNBOUNDED PRECEDING
		) 
	END AS decimal(9, 2)) AS [Ventas Acumuladas],

	CAST(
	(
	CASE WHEN GROUPING(IdCliente) = 1 THEN MIN(TotalVentas)
	ELSE
		SUM(SUM(Ventas)) OVER(
		ORDER BY GROUPING(IdCliente), SUM(Ventas) / SUM([Número de Albaranes]) DESC, [IdCliente]
		ROWS UNBOUNDED PRECEDING
		) 
	END * 100.00
	) / MIN(TotalVentas) AS decimal(5, 2)) AS [% Ventas Acumuladas]
FROM
    CTE0
GROUP BY
	ROLLUP(IdCliente)
ORDER BY
	GROUPING(IdCliente),
	[Venta Media Por Albaran] DESC,
	[IdCliente];
GO

Pero recibo el error:

Sintaxis incorrecta cerca de ')'.

¿qué estoy haciendo mal? ¿No se puede usar 

ORDER BY
GROUPING(IdCliente),
[Venta Media Por Albaran] DESC,
[IdCliente] 

en una clausula over?

Gracias

Angel

Answer 9

Sin intencion de ofender pero al parecer usted no esta leyendo y tratando de entender lo que he explicado anteriormente.

Si desea adicionar una columna para enumerar las filas retornadas en el orden deseado mediante el uso de la funcion ROW_NUMBER() OVER(...) pues no habra diferencia con el orden usado en las columnas donde se calcula el valor acumulado.

Ya le comente que debido a que el alias de columnas, en este caso [Venta Media Por Albaran], solo es visible en la clausula ORDER BY producto del proceso logico de un query por parte del motor, entonces debera usar la formula en vez del alias tal y como hice para las columnas de acumulados.

DECLARE @Ventas AS table (
SerieNumero nvarchar(12),
FechaAlbaran  date,
IdCliente integer,
NombreCliente nvarchar(100),
IdArticulo integer,
ImporteVenta decimal(18,5)
);

INSERT INTO @Ventas
VALUES('A1','01/01/2016',1,'pepe',8,24.5),
 ('A1','01/01/2016',1,'pepe',7,44.5),
 ('A2','02/01/2016',1,'pepe',4,14),
 ('A3','03/01/2016',1,'pepe',3,1.5),
 ('A3','03/01/2016',1,'pepe',1,32.5),
 ('A4','01/01/2016',5,'antonio',12,11.5),
 ('A24','01/01/2016',3,'lucas',11,46),
 ('A5','02/01/2016',5,'antonio',14,9.24),
 ('A6','04/01/2016',1,'pepe',1,32.5),
 ('A6','04/01/2016',1,'pepe',12,11.5),
 ('A7','04/01/2016',5,'antonio',14,9.24);

WITH CTE0 AS ( 
SELECT
	IdCliente,
	MAX(NombreCliente) AS NombreCliente,
    SUM(ImporteVenta) AS Ventas,
	COUNT(DISTINCT SerieNumero) AS [Número de Albaranes],
	SUM(SUM(ImporteVenta)) OVER() AS TotalVentas
FROM 
	@Ventas
GROUP BY
	IdCliente
)
SELECT
	ROW_NUMBER()  OVER (ORDER BY
	 GROUPING(IdCliente), SUM(Ventas) / SUM([Número de Albaranes]) DESC, [IdCliente]
	 ) AS SortOrder,
	CASE WHEN GROUPING(IdCliente) = 1 THEN 'Total' ELSE CAST(IdCliente AS varchar(15)) END AS IdCliente,
    CASE WHEN GROUPING(IdCliente) = 1 THEN '' ELSE MAX(NombreCliente) END AS [Nombre Cliente],
    CAST(SUM(Ventas) AS decimal(9, 2)) AS Ventas,
	SUM([Número de Albaranes]) AS [Número de Albaranes],
	CAST(SUM(Ventas) / SUM([Número de Albaranes]) AS decimal(9, 2))AS [Venta Media Por Albaran],
    CAST((SUM(Ventas) * 100.00)/ MIN(TotalVentas) AS decimal(5, 2)) AS [% Ventas],

	CAST(
	CASE WHEN GROUPING(IdCliente) = 1 THEN SUM(Ventas)
	ELSE
		SUM(SUM(Ventas)) OVER(
		ORDER BY GROUPING(IdCliente), SUM(Ventas) / SUM([Número de Albaranes]) DESC, [IdCliente]
		ROWS UNBOUNDED PRECEDING
		) 
	END AS decimal(9, 2)) AS [Ventas Acumuladas],

	CAST(
	(
	CASE WHEN GROUPING(IdCliente) = 1 THEN MIN(TotalVentas)
	ELSE
		SUM(SUM(Ventas)) OVER(
		ORDER BY GROUPING(IdCliente), SUM(Ventas) / SUM([Número de Albaranes]) DESC, [IdCliente]
		ROWS UNBOUNDED PRECEDING
		) 
	END * 100.00
	) / MIN(TotalVentas) AS decimal(5, 2)) AS [% Ventas Acumuladas]
FROM
    CTE0
GROUP BY
	ROLLUP(IdCliente)
ORDER BY
	GROUPING(IdCliente),
	[Venta Media Por Albaran] DESC,
	[IdCliente];
GO

Le aconsejo este libro que ensenia los fundamentos del lenguje T-SQL y que le ayudara mucho a familiarizarse con las funciones de ventana.

https://www.amazon.com/T-SQL-Fundamentals-3rd-Itzik-Ben-Gan/dp/150930200X

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Answer 10

Pido disculpas por mi lentitud al entender lo que querías transmitirme en tu explicación. Con este último script creo que me ha quedado más claro.

Como bien supones no tengo un gran nivel en T-SQL y existen conceptos y cláusulas que aún me cuestan un poco entender y asimilar.

Muchas gracias por tu respuesta y por el libro que me aconsejas.

Un saludo

Angel

Answer 11

Angel,

No hay de que disculparse.

El consejo que te he dado es para que sobre todo entiendas lo que se postea. La idea no es que se copie ciegamente lo que se postea y usarlo sin entenderlo, puesto que los problemas saldran cuando sea necesario cambiar/adicionar algo.

T-SQL es un lenguaje declarativo y nos cuesta trabajo al principio adaptarnos a pensar diferente (en conjuntos) cuando se viene de programar en otros lenguajes donde casi siempre se procesa un elemento a la vez.

El libro que te recomende es formidable y esta dedicado principalmente a que se entiendan los fundamentos del lenguaje antes de que profundizes en su uso.

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas