SELECT CON SUMA E IMPORTE MEDIO DE REGISTROS AGRUPADOS

Question

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

Answer 1

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/

Answer 2

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

Answer 3

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/

Answer 4

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

Answer 5

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.

Answer 6

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/

Answer 7

Buen dia Bader, como estas puedo hacerte una consulta?

Answer 8

Buenos dias.. Si decime nomas..

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

Answer 9

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

Answer 10

Muchas gracias Willams.

Me sirvió tu respuesta correctamente.

Un saludo

Angel

Answer 11

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