I have this query on sql server:
SELECT distinct(em.id_empleado), al.region, al.des_almacen, al.cadenaventa, em.des_empleado, em.cargo, em.pais, al.id_almacen,
T1.ventas, T1.boletas, T1.unidades, T2.trafico,
RANK() OVER (ORDER BY T1.boletas DESC) AS RankByBoletas
FROM dim.almacen as al, dim.empleado as em,
( SELECT id_almacen, id_empleado, sum(unidades_ytd) as unidades, sum(documentos_ytd) as boletas, sum(venta_ytd) as ventas
FROM fact.venta_comparada_empleado WHERE id_calendario between 20180101 AND 20180630 GROUP BY id_almacen, id_empleado ) T1
INNER JOIN
( SELECT id_almacen, sum(val_trafico_ytd) as trafico FROM fact.trafico_comparada
WHERE id_calendario between 20180101 AND 20180630 GROUP BY id_almacen ) T2
ON T1.id_almacen = T2.id_almacen
WHERE T1.id_almacen = al.id_almacen AND T1.id_empleado = em.id_empleado AND al.canalventa like 'RETAIL'
ORDER BY id_almacen, id_empleado
I need to get the ranking according to the number of units invoiced * ballot, which would be something more or less:
RANK() OVER (ORDER BY (T1.unidades/T1.boletas) DESC) AS RankByUniXBoletas
But there are records where the T1.boletas
is 0
; I try to incorporate a case of T1.boletas > 0
but I get an error, that is:
SELECT distinct(em.id_empleado), al.region, al.des_almacen,
al.cadenaventa, em.des_empleado, em.cargo, em.pais, al.id_almacen,
T1.ventas, T1.boletas, T1.unidades, T2.trafico,
RANK() OVER (ORDER BY T1.boletas DESC) AS RankByBoletas,
case when T1.boletas > 0 then
RANK() OVER (ORDER BY (T1.unidades/T1.boletas) DESC)
else '700000' end AS RankByUniXBoletas
FROM dim.almacen as al, dim.empleado as em,
( SELECT id_almacen, id_empleado, sum(unidades_ytd) as unidades, sum(documentos_ytd) as boletas, sum(venta_ytd) as ventas
FROM fact.venta_comparada_empleado WHERE id_calendario between 20180101 AND 20180630 GROUP BY id_almacen, id_empleado ) T1
INNER JOIN
( SELECT id_almacen, sum(val_trafico_ytd) as trafico FROM fact.trafico_comparada
WHERE id_calendario between 20180101 AND 20180630 GROUP BY id_almacen ) T2
ON T1.id_almacen = T2.id_almacen
WHERE T1.id_almacen = al.id_almacen AND T1.id_empleado = em.id_empleado AND al.canalventa like 'RETAIL'
ORDER BY id_almacen, id_empleado
But I get an error:
Msg 8134, Level 16, State 1, Line 1
Error de división entre cero.