error with rank on sql server

1

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.
    
asked by Virginia 21.11.2018 в 20:18
source

0 answers