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

  • Pregunta

  • 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

    viernes, 27 de enero de 2017 13:03

Respuestas

  • 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







    • Propuesto como respuesta Joyce_ACModerator viernes, 27 de enero de 2017 15:44
    • Editado HunchbackMVP viernes, 27 de enero de 2017 17:13
    • Marcado como respuesta Angeleci viernes, 27 de enero de 2017 17:55
    viernes, 27 de enero de 2017 14:14
  • 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



    • Editado HunchbackMVP martes, 31 de enero de 2017 17:00
    • Marcado como respuesta Angeleci sábado, 4 de febrero de 2017 8:23
    martes, 31 de enero de 2017 16:58
  • 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



    • Editado HunchbackMVP jueves, 2 de febrero de 2017 13:48
    • Marcado como respuesta Angeleci sábado, 4 de febrero de 2017 8:23
    jueves, 2 de febrero de 2017 13:46

Todas las respuestas

  • 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







    • Propuesto como respuesta Joyce_ACModerator viernes, 27 de enero de 2017 15:44
    • Editado HunchbackMVP viernes, 27 de enero de 2017 17:13
    • Marcado como respuesta Angeleci viernes, 27 de enero de 2017 17:55
    viernes, 27 de enero de 2017 14:14
  • 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.
    viernes, 27 de enero de 2017 14:36
  • Gracias Huncback.

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

    Angel

    viernes, 27 de enero de 2017 19:08
  • 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

    lunes, 30 de enero de 2017 20:35
  • 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

    lunes, 30 de enero de 2017 20:45
  • 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

    martes, 31 de enero de 2017 12:08
  • 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



    • Editado HunchbackMVP martes, 31 de enero de 2017 17:00
    • Marcado como respuesta Angeleci sábado, 4 de febrero de 2017 8:23
    martes, 31 de enero de 2017 16:58
  • 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

    miércoles, 1 de febrero de 2017 20:42
  • 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



    • Editado HunchbackMVP jueves, 2 de febrero de 2017 13:48
    • Marcado como respuesta Angeleci sábado, 4 de febrero de 2017 8:23
    jueves, 2 de febrero de 2017 13:46
  • 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

    sábado, 4 de febrero de 2017 8:25
  • 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

    lunes, 6 de febrero de 2017 15:29