Monthly Archives: June 2013

Software

División Condicionada por número de filas retornadas SQLServer

Leave a comment

dividir según número de filas encontradas sqlserver

Si tienes un select en el que deseas calcular un promedio, necesitas dividir el valor total entre el número de repeticiones.
Por ejemplo necesitamos saber el promedio de compras de un cliente en los ultimos 12 meses, entonces sumamos el valor de sus compras de sus ultimos 12 meses y los dividimos entre 12, muy fácil un simple promedio bastaría, pero que pasa si ejecutamos la consulta y el cliente apenas ha comprado durante los ultimos 4 meses?… el promedio quedaría errado al dividir entre 12, así que debemos calcular cuantos meses ha comprado para dividir entre los meses verdaderos, usamos la función COUNT ( ) .
Solución:
–Creamos una tabla temporal
CREATE TABLE #factura
(
numero       INT,
fechafactura DATETIME,
cliente      VARCHAR(4),
valorfactura MONEY
)
–Ahora unos datos para realizar la consulta:
INSERT #factura
VALUES (1,
‘2011-01-01’,
‘0009’,
10500)
INSERT #factura
VALUES (2,
‘2011-02-02’,
‘0009’,
500)
INSERT #factura
VALUES (3,
‘2011-03-03’,
‘0009’,
13500)
INSERT #factura
VALUES (4,
‘2011-04-04’,
‘0009’,
10500)
INSERT #factura
VALUES (5,
‘2011-05-05’,
‘0009’,
1000)
INSERT #factura
VALUES (6,
‘2011-06-06’,
‘0009’,
2300)
INSERT #factura
VALUES (7,
‘2011-06-06’,
‘0010’,
5000)
INSERT #factura
VALUES (8,
‘2011-07-07’,
‘0009’,
1500)
INSERT #factura
VALUES (9,
‘2011-08-08’,
‘0009’,
8000)
INSERT #factura
VALUES (10,
‘2011-09-09’,
‘0009’,
9800)
INSERT #factura
VALUES (11,
‘2011-10-10’,
‘0009’,
100)
INSERT #factura
VALUES (12,
‘2011-11-11’,
‘0009’,
1500)
INSERT #factura
VALUES (13,
‘2011-12-12’,
‘0009’,
1000)
INSERT #factura
VALUES (14,
‘2012-01-01’,
‘0009’,
23500)
INSERT #factura
VALUES (15,
‘2012-01-01’,
‘0010’,
4500)
INSERT #factura
VALUES (16,
‘2012-01-01’,
‘0011’,
2300)
INSERT #factura
VALUES (17,
‘2012-02-02’,
‘0009’,
19500)
INSERT #factura
VALUES (18,
‘2012-02-01’,
‘0010’,
5500)
INSERT #factura
VALUES (19,
‘2012-03-03’,
‘0009’,
9500)
INSERT #factura
VALUES (20,
‘2012-03-03’,
‘0010’,
4000)
INSERT #factura
VALUES (21,
‘2012-03-03’,
‘0011’,
4000)
INSERT #factura
VALUES (22,
‘2012-03-03’,
‘0011’,
10500)
INSERT #factura
VALUES (23,
‘2012-04-03’,
‘0011’,
10500)
INSERT #factura
VALUES (24,
‘2012-05-03’,
‘0011’,
10500)

— Este primer query muestra un resumen de los datos ingresados agrupados por Cliente
SELECT cliente,
Count(*)               AS TOTFACS,
Sum(valorfactura)      AS totalfac,
Sum(valorfactura) / 12 AS promedio
FROM   #factura
WHERE  Datediff(mm, fechafactura, ‘2012-03-10’) <= 12
GROUP  BY cliente
Promedio calculado 12 meses fijos
Promedio Ultimos 12 Meses Fijo

En esta consulta se muestra cada cliente con el total de facturas, el valor total de dichas facturas y un promedio al dividir entre los últimos 12 meses. Por eso el promedio del cliente ‘0010’ queda tan bajo.

— Ahora el query que nos devuelve un promedio exacto por Cliente
SELECT cliente,
Count(*) AS TOTFACS,
Sum(valorfactura) AS totalfac,
Count(DISTINCT Datepart(mm, fechafactura)) AS totmeses,
Sum(valorfactura) / Count(DISTINCT Datepart(mm, fechafactura)) AS promedio
FROM   #factura
WHERE  Datediff(mm, fechafactura, ‘2012-03-10’) <= 12
GROUP  BY cliente

Hasta 12 meses o menos
Promedio Ultimos meses facturados máximo 12

En esta consulta se muestra cada cliente con el total de facturas, el valor total de dichas facturas, el número de meses en los que hizo compras y un promedio al dividir entre los últimos meses calculados(totmeses). Por eso el promedio del cliente ‘0010’ ahora si se muestra correcto. Hay que tener en la cuenta que si el requerimiento dice que es el promedio de los últimos 12 meses se debe especificar que se muestra un promedio relativo al número de meses en que ha comprado durante el último año.