none
SELECT CON SUMA E IMPORTE MEDIO DE REGISTROS AGRUPADOS RRS feed

  • Pregunta

  • Hola,

    Trabajo con SQL Server 2014 y me encuentro con el siguiente problema partiendo de la siguiente tabla:

    DECLARE @Ventas AS TABLE
    (
       IdCliente Int,
       Fecha  Date,
       NumeroAlbaran Int,
       Importe decimal(15,5)
       
    )
    
    ;
    
    INSERT INTO @Ventas
    VALUES(1,'01/01/2016',1,24.5),
     (1,'02/01/2016',1,44.5),
     (1,'02/01/2016',2,14),
     (2,'03/01/2016',3,1.5),
     (1,'03/02/2016',4,32.5),
     (16,'08/05/2016',5,11.5),
     (16,'08/05/2016',5,9.24),
     (9,'11/06/2016',6,8.74),
     (9,'11/06/2016',6,22.5),
     (9,'11/06/2016',6,11.4),
     (9,'15/06/2016',7,6.5),
     (9,'15/06/2016',7,9.8),
     (9,'17/06/2016',8,44),
     (9,'17/06/2016',8,24.15),
     (4,'19/07/2016',9,27.8),
     (4,'19/07/2016',9,14.6),
     (14,'20/07/2016',10,27.8),
     (19,'20/07/2016',10,9.5),
     (7,'21/07/2016',11,4.5),
     (11,'22/07/2016',12,37.2),
     (4,'22/07/2016',12,19.2);
    

    Necesito dar forma a un select que contenga lo siguiente:

    1- Todas las columnas de esta tabla.

    2- Una columna llamada TotalImporteAlbaran cuyo valor sea la suma de los "importes" de los registros con el mismo NumeroAlbaran.

    3- Una columna llamada ImporteAlbaranMedioMensual cuyo valor sea igual a: Suma de los importes de los registros para ese mes y para ese IdCliente dividido entre el numero de valores distintos de NumeroAlbaran para ese mes y para ese IdCliente.

    Os pongo el resultado que obtendriamos a raiz de la tabla Ventas.

    No sé si es posible emplazar este tipo de cálculo en un mismo select pues requiere de agrupaciones de registros anteriores y posteriores.

    ¿se os ocurre alguna forma o idea sobre la que investigar?

    Muchas gracias

    Angel

    viernes, 29 de julio de 2016 10:59

Respuestas

  • Angeleci,

    Ejecuta la siguiente consulta (basada en los datos que adjuntas):

    SELECT 
    	v.IdCliente,
    	v.Fecha,
    	v.NumeroAlbaran,
    	v.Importe,
    	SUM(v.Importe) OVER(PARTITION BY v.NumeroAlbaran) [TotalImporteAlbaran],
    	SUM(v.Importe) OVER(PARTITION BY v.IdCliente, MONTH(v.Fecha)) / (SELECT COUNT(DISTINCT NumeroAlbaran) FROM @Ventas WHERE (v.IdCliente = IdCliente) AND (MONTH(v.Fecha) = MONTH(Fecha))) [ImporteAlbaranMedioMensual]	
    FROM 
    	@Ventas v;

    Resultado:



    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    • Marcado como respuesta Angeleci lunes, 1 de agosto de 2016 14:40
    viernes, 29 de julio de 2016 18:43
  • Proba con esto

    DECLARE @Ventas AS TABLE
    (
       IdCliente Int,
       Fecha  Date,
       NumeroAlbaran Int,
       Importe decimal(15,1)
       
    );
    
    INSERT INTO @Ventas
    VALUES(1,'01/01/2016',1,24.5),
     (1,'02/01/2016',1,44.5),
     (1,'02/01/2016',2,14),
     (2,'03/01/2016',3,1.5),
     (1,'03/02/2016',4,32.5),
     (16,'08/05/2016',5,11.5),
     (16,'08/05/2016',5,9.24),
     (9,'11/06/2016',6,8.74),
     (9,'11/06/2016',6,22.5),
     (9,'11/06/2016',6,11.4),
     (9,'15/06/2016',7,6.5),
     (9,'15/06/2016',7,9.8),
     (9,'17/06/2016',8,44),
     (9,'17/06/2016',8,24.15),
     (4,'19/07/2016',9,27.8),
     (4,'19/07/2016',9,14.6),
     (14,'20/07/2016',10,27.8),
     (19,'20/07/2016',10,9.5),
     (7,'21/07/2016',11,4.5),
     (11,'22/07/2016',12,37.2),
     (4,'22/07/2016',12,19.2);
    	
    	
     select a.IdCliente ,
            a.Fecha ,
            a.NumeroAlbaran ,
            a.Importe ,
            ve.total_importe ,
            SUM(a.Importe) OVER(PARTITION BY a.IdCliente, MONTH(a.Fecha)) / (SELECT COUNT(DISTINCT NumeroAlbaran) FROM @Ventas v WHERE (v.IdCliente = a.IdCliente) AND (MONTH(v.Fecha) = MONTH(a.Fecha))) [ImporteAlbaranMedioMensual]        
     from  @Ventas as a
                        join ( select   sum(v2.Importe) total_importe ,
                                        v2.numeroAlbaran
                               from     @Ventas as v2
                               group by v2.numeroAlbaran
                             ) ve on ve.numeroAlbaran = a.numeroAlbaran
            
            
          

    OBS: Favor vota si te es útil la información.
    Saludos
    Bader Molinas - Paraguay
    https://deveintel.wordpress.com/



    • Editado Bader Molinas viernes, 29 de julio de 2016 19:55
    • Marcado como respuesta Angeleci lunes, 1 de agosto de 2016 7:59
    viernes, 29 de julio de 2016 19:35

Todas las respuestas

  • Hola amigo, me podrias colocar en numero como allaste el 41.50  ?? Hasta el momento tengo esto

    DECLARE @Ventas AS TABLE
    (
       IdCliente Int,
       Fecha  Date,
       NumeroAlbaran Int,
       Importe decimal(15,1)
       
    )
    
    ;
    
    INSERT INTO @Ventas
    VALUES(1,'01/01/2016',1,24.5),
     (1,'02/01/2016',1,44.5),
     (1,'02/01/2016',2,14),
     (2,'03/01/2016',3,1.5),
     (1,'03/02/2016',4,32.5),
     (16,'08/05/2016',5,11.5),
     (16,'08/05/2016',5,9.24),
     (9,'11/06/2016',6,8.74),
     (9,'11/06/2016',6,22.5),
     (9,'11/06/2016',6,11.4),
     (9,'15/06/2016',7,6.5),
     (9,'15/06/2016',7,9.8),
     (9,'17/06/2016',8,44),
     (9,'17/06/2016',8,24.15),
     (4,'19/07/2016',9,27.8),
     (4,'19/07/2016',9,14.6),
     (14,'20/07/2016',10,27.8),
     (19,'20/07/2016',10,9.5),
     (7,'21/07/2016',11,4.5),
     (11,'22/07/2016',12,37.2),
     (4,'22/07/2016',12,19.2);
     
     select v.IdCliente ,
       v.Fecha  ,
       v.NumeroAlbaran ,
       v.Importe,ve.total_importe
     from   @Ventas as v
            join ( select   sum(v2.Importe) total_importe ,
                            v2.numeroAlbaran
                   from     @Ventas as v2
                   group by v2.numeroAlbaran
                 ) ve on ve.numeroAlbaran = v.numeroAlbaran
       


    OBS: Favor vota si te es útil la información.
    Saludos
    Bader Molinas - Paraguay
    https://deveintel.wordpress.com/


    viernes, 29 de julio de 2016 13:00
  • Hola Bader.

    El 41.50 es la media del total importe de los distintos albaranes que tenemos en ese mes para ese Idcliente.

    Por ejemplo, para el mes de Enero de 2016 para el IdCliente 1 tenemos dos NumeroAlbaran distintos, el 1 el 2.

    El total Importe del Albaran 1 es 69 y el total Importe del Albaran 2 es 14. 41.50 es la media de ambos valores, 69 y 14.

    Existe otro Numero de Albaran para el IdCliente 1 pero corresponde a otro mes (su fecha es 03/02/2016), Febrero de 2016, por lo que no entra a formar parte de esta media la cual se calcula a nivel de meses.

    Muchas gracias

    Angel

    viernes, 29 de julio de 2016 14:54
  • Que tal Angel, te paso lo solicitado..Cualquier cosa si me comentas

    DECLARE @Ventas AS TABLE
    (
       IdCliente Int,
       Fecha  Date,
       NumeroAlbaran Int,
       Importe decimal(15,1)
       
    );
    
    INSERT INTO @Ventas
    VALUES(1,'01/01/2016',1,24.5),
     (1,'02/01/2016',1,44.5),
     (1,'02/01/2016',2,14),
     (2,'03/01/2016',3,1.5),
     (1,'03/02/2016',4,32.5),
     (16,'08/05/2016',5,11.5),
     (16,'08/05/2016',5,9.24),
     (9,'11/06/2016',6,8.74),
     (9,'11/06/2016',6,22.5),
     (9,'11/06/2016',6,11.4),
     (9,'15/06/2016',7,6.5),
     (9,'15/06/2016',7,9.8),
     (9,'17/06/2016',8,44),
     (9,'17/06/2016',8,24.15),
     (4,'19/07/2016',9,27.8),
     (4,'19/07/2016',9,14.6),
     (14,'20/07/2016',10,27.8),
     (19,'20/07/2016',10,9.5),
     (7,'21/07/2016',11,4.5),
     (11,'22/07/2016',12,37.2),
     (4,'22/07/2016',12,19.2);
    
     
     select a.IdCliente ,
            a.Fecha ,
            a.NumeroAlbaran ,
            a.Importe ,
            a.total_importe ,
            ( select    ( sum(v2.Importe) / 2 ) total_importe
              from      @Ventas as v2
              where     v2.IdCliente = a.IdCliente
                        and year(v2.Fecha) = year(a.Fecha)
                        and month(v2.Fecha) = month(a.Fecha)
              group by  year(v2.Fecha) ,
                        month(v2.Fecha) ,
                        v2.IdCliente
            ) medio_mes_cliente
     from   ( select    v.IdCliente ,
                        v.Fecha ,
                        v.NumeroAlbaran ,
                        v.Importe ,
                        ve.total_importe
              from      @Ventas as v
                        join ( select   sum(v2.Importe) total_importe ,
                                        v2.numeroAlbaran
                               from     @Ventas as v2
                               group by v2.numeroAlbaran
                             ) ve on ve.numeroAlbaran = v.numeroAlbaran
            ) a
            
          

    OBS: Favor vota si te es útil la información.
    Saludos
    Bader Molinas - Paraguay
    https://deveintel.wordpress.com/

    viernes, 29 de julio de 2016 17:54
  • Hola Bader.

    He estado comprobando los resultados y hay algunos registros que no se calculan correctamente.

    Veo que el resultado que esperamos es dividido entre 2. Te pongo una imagen donde te lo comento.

    Cr

    Creo que el error se encuentra en que partes de que siempre habrá dos numero de albaranes distintos por mes. Por eso cuando solo hay uno sale dividido por dos. Realmente puede haber un numero indefinido de albaranes distintos por mes y cliente por lo deberia realizar una promedio entre los importes de dichos albaranes.

    Muchas gracias

    Angel

    • Marcado como respuesta Angeleci lunes, 1 de agosto de 2016 15:02
    • Desmarcado como respuesta Angeleci lunes, 1 de agosto de 2016 15:02
    viernes, 29 de julio de 2016 18:39
  • Angeleci,

    Ejecuta la siguiente consulta (basada en los datos que adjuntas):

    SELECT 
    	v.IdCliente,
    	v.Fecha,
    	v.NumeroAlbaran,
    	v.Importe,
    	SUM(v.Importe) OVER(PARTITION BY v.NumeroAlbaran) [TotalImporteAlbaran],
    	SUM(v.Importe) OVER(PARTITION BY v.IdCliente, MONTH(v.Fecha)) / (SELECT COUNT(DISTINCT NumeroAlbaran) FROM @Ventas WHERE (v.IdCliente = IdCliente) AND (MONTH(v.Fecha) = MONTH(Fecha))) [ImporteAlbaranMedioMensual]	
    FROM 
    	@Ventas v;

    Resultado:



    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    • Marcado como respuesta Angeleci lunes, 1 de agosto de 2016 14:40
    viernes, 29 de julio de 2016 18:43
  • Proba con esto

    DECLARE @Ventas AS TABLE
    (
       IdCliente Int,
       Fecha  Date,
       NumeroAlbaran Int,
       Importe decimal(15,1)
       
    );
    
    INSERT INTO @Ventas
    VALUES(1,'01/01/2016',1,24.5),
     (1,'02/01/2016',1,44.5),
     (1,'02/01/2016',2,14),
     (2,'03/01/2016',3,1.5),
     (1,'03/02/2016',4,32.5),
     (16,'08/05/2016',5,11.5),
     (16,'08/05/2016',5,9.24),
     (9,'11/06/2016',6,8.74),
     (9,'11/06/2016',6,22.5),
     (9,'11/06/2016',6,11.4),
     (9,'15/06/2016',7,6.5),
     (9,'15/06/2016',7,9.8),
     (9,'17/06/2016',8,44),
     (9,'17/06/2016',8,24.15),
     (4,'19/07/2016',9,27.8),
     (4,'19/07/2016',9,14.6),
     (14,'20/07/2016',10,27.8),
     (19,'20/07/2016',10,9.5),
     (7,'21/07/2016',11,4.5),
     (11,'22/07/2016',12,37.2),
     (4,'22/07/2016',12,19.2);
    	
    	
     select a.IdCliente ,
            a.Fecha ,
            a.NumeroAlbaran ,
            a.Importe ,
            ve.total_importe ,
            SUM(a.Importe) OVER(PARTITION BY a.IdCliente, MONTH(a.Fecha)) / (SELECT COUNT(DISTINCT NumeroAlbaran) FROM @Ventas v WHERE (v.IdCliente = a.IdCliente) AND (MONTH(v.Fecha) = MONTH(a.Fecha))) [ImporteAlbaranMedioMensual]        
     from  @Ventas as a
                        join ( select   sum(v2.Importe) total_importe ,
                                        v2.numeroAlbaran
                               from     @Ventas as v2
                               group by v2.numeroAlbaran
                             ) ve on ve.numeroAlbaran = a.numeroAlbaran
            
            
          

    OBS: Favor vota si te es útil la información.
    Saludos
    Bader Molinas - Paraguay
    https://deveintel.wordpress.com/



    • Editado Bader Molinas viernes, 29 de julio de 2016 19:55
    • Marcado como respuesta Angeleci lunes, 1 de agosto de 2016 7:59
    viernes, 29 de julio de 2016 19:35
  • Buen dia Bader, como estas puedo hacerte una consulta?
    lunes, 1 de agosto de 2016 14:25
  • Buenos dias.. Si decime nomas..

    OBS: Favor vota si te es útil la información.
    Saludos
    Bader Molinas - Paraguay
    https://deveintel.wordpress.com/

    • Propuesto como respuesta ManuelAguila lunes, 1 de agosto de 2016 15:06
    lunes, 1 de agosto de 2016 14:58
  • Hola Bader.

    Me ha servido correctamente tu propuesta de solución.

    Voy a estudiarla a fondo pues no me imaginaba que se podia solventar como tu lo has hecho.

    Mil gracias

    Angel

    lunes, 1 de agosto de 2016 15:01
  • Muchas gracias Willams.

    Me sirvió tu respuesta correctamente.

    Un saludo


    Angel

    lunes, 1 de agosto de 2016 15:03
  • muchas gracias, disculpa si me desvio del tema tratado aqui, como puedo diseñar un modulo contable para añadir a un modelo de compras, inventario y facturacion.

    Saludos

    lunes, 1 de agosto de 2016 15:09