I have the following query below, however, when I run it, the following message occurs: erro de divisão por zero . I tried to use the nullif function, but to no avail. How to solve?
SELECT count(f.SolID) / cast(dbo.horas_uteis_trabalhadas('01-11-2017','30-11-2017') / nullif(144000,NULL) AS decimal(7,1)) [DPI]
FROM tarefa f
Testing for null will not help you much the way you are doing, because if the value of working hours continues to be zero, you will still get the divide-by-zero error.
Most likely your horas_uteis_trabalhadas fault function should be returning an integer or a value less than 144000 and in the sql server by dividing any value by an integer, it will always return an integer, which in its case will be zero. >
What you can do, is to test if working hours are greater than zero, I set up an example using the case and make a cast to float :
with v1 as (
select
cast(dbo.horas_uteis_trabalhadas('01-11-2017','30-11-2017') as float) as horas,
cast(nullif(144000,null) as float) as valor,
count(f.SolID) as SolId
from tarefa f)
select iif(horas > 0, SolId / cast(horas/valor as float), 1) as [DPI]
from v1