none
Divide by zero error encountered. RRS feed

  • Question

  • Hi

    I have a problem.

    My code is:

     

    DECLARE @DATA DATETIME
    SET @DATA = '06-30-2010'
    
    SELECT 5 as grupo,'' as COD,'MARGEM VENDAS - CUSTO' as Gastos,
    
    	case when datepart(mm,ft.fdata)=1 and datepart(yy,ft.fdata)=datepart(yy,@data) then ((sum(ft.ettiliq) - sum(ft.ecusto))*100) / sum(ft.ecusto) else 0 end as Jan
    	from ft (nolock)
    
    
    	WHERE datepart(yy,ft.fdata) between datepart(yy,@data)-1 and datepart(yy,@data) and datepart(mm,ft.fdata) <= datepart(mm,@data) and datepart(dd,ft.fdata)<= case when datepart(mm,ft.fdata)=datepart(mm,@data) then datepart(dd,@data) else 31 end
     			AND ft.fdata <= @data
     			AND (ft.tipodoc<>4) 
     			AND ft.no <>17999
    
    	group by ft.fdata,ft.ettiliq,ft.ecusto
    
    

     

    And i have error

    Msg 8134, Level 16, State 1, Line 4

    Divide by zero error encountered.

     

    Can help me??

     

    Thanks

     

    Monday, July 5, 2010 4:31 PM

Answers

  • Change this code ((sum(ft.ettiliq) - sum(ft.ecusto))*100) / sum(ft.ecusto)

    to

     

    ((sum(ft.ettiliq) - sum(ft.ecusto))*100.00) / NULLIF(sum(ft.ecusto),0)

    The above will give you NULL in case of sum(eCusto) = 0

    The whole code will be:

    DECLARE @DATA DATETIME
    SET @DATA = '06-30-2010'
    
    SELECT 5 as grupo,'' as COD,'MARGEM VENDAS - CUSTO' as Gastos,
    
    	case when datepart(mm,ft.fdata)=1 and datepart(yy,ft.fdata)=datepart(yy,@data) then ((sum(ft.ettiliq) - sum(ft.ecusto))*100.00) / NULLIF(sum(ft.ecusto),0) else 0 end as Jan
    	from ft (nolock)
    
    
    	WHERE datepart(yy,ft.fdata) between datepart(yy,@data)-1 and datepart(yy,@data) and datepart(mm,ft.fdata) <= datepart(mm,@data) and datepart(dd,ft.fdata)<= case when datepart(mm,ft.fdata)=datepart(mm,@data) then datepart(dd,@data) else 31 end
     			AND ft.fdata <= @data
     			AND (ft.tipodoc<>4) 
     			AND ft.no <>17999
    
    	group by ft.fdata,ft.ettiliq,ft.ecusto


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Night_devil Monday, July 5, 2010 4:52 PM
    Monday, July 5, 2010 4:35 PM
    Moderator
  • DECLARE @DATA DATETIME
    SET @DATA = '06-30-2010'
    
    SELECT 5 as grupo,'' as COD,'MARGEM VENDAS - CUSTO' as Gastos,
    
    	case when datepart(mm,ft.fdata)=1 and datepart(yy,ft.fdata)=datepart(yy,@data) then ((sum(ft.ettiliq) - sum(ft.ecusto))*100) / NULLIF(sum(ft.ecusto), 0) else 0 end as Jan
    	from ft (nolock)
    
    
    	WHERE datepart(yy,ft.fdata) between datepart(yy,@data)-1 and datepart(yy,@data) and datepart(mm,ft.fdata) <= datepart(mm,@data) and datepart(dd,ft.fdata)<= case when datepart(mm,ft.fdata)=datepart(mm,@data) then datepart(dd,@data) else 31 end
     			AND ft.fdata <= @data
     			AND (ft.tipodoc<>4) 
     			AND ft.no <>17999
    
    	group by ft.fdata,ft.ettiliq,ft.ecusto
    
    Tom
    • Marked as answer by Night_devil Monday, July 5, 2010 4:52 PM
    Monday, July 5, 2010 4:37 PM

All replies

  • Change this code ((sum(ft.ettiliq) - sum(ft.ecusto))*100) / sum(ft.ecusto)

    to

     

    ((sum(ft.ettiliq) - sum(ft.ecusto))*100.00) / NULLIF(sum(ft.ecusto),0)

    The above will give you NULL in case of sum(eCusto) = 0

    The whole code will be:

    DECLARE @DATA DATETIME
    SET @DATA = '06-30-2010'
    
    SELECT 5 as grupo,'' as COD,'MARGEM VENDAS - CUSTO' as Gastos,
    
    	case when datepart(mm,ft.fdata)=1 and datepart(yy,ft.fdata)=datepart(yy,@data) then ((sum(ft.ettiliq) - sum(ft.ecusto))*100.00) / NULLIF(sum(ft.ecusto),0) else 0 end as Jan
    	from ft (nolock)
    
    
    	WHERE datepart(yy,ft.fdata) between datepart(yy,@data)-1 and datepart(yy,@data) and datepart(mm,ft.fdata) <= datepart(mm,@data) and datepart(dd,ft.fdata)<= case when datepart(mm,ft.fdata)=datepart(mm,@data) then datepart(dd,@data) else 31 end
     			AND ft.fdata <= @data
     			AND (ft.tipodoc<>4) 
     			AND ft.no <>17999
    
    	group by ft.fdata,ft.ettiliq,ft.ecusto


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Night_devil Monday, July 5, 2010 4:52 PM
    Monday, July 5, 2010 4:35 PM
    Moderator
  • DECLARE @DATA DATETIME
    SET @DATA = '06-30-2010'
    
    SELECT 5 as grupo,'' as COD,'MARGEM VENDAS - CUSTO' as Gastos,
    
    	case when datepart(mm,ft.fdata)=1 and datepart(yy,ft.fdata)=datepart(yy,@data) then ((sum(ft.ettiliq) - sum(ft.ecusto))*100) / NULLIF(sum(ft.ecusto), 0) else 0 end as Jan
    	from ft (nolock)
    
    
    	WHERE datepart(yy,ft.fdata) between datepart(yy,@data)-1 and datepart(yy,@data) and datepart(mm,ft.fdata) <= datepart(mm,@data) and datepart(dd,ft.fdata)<= case when datepart(mm,ft.fdata)=datepart(mm,@data) then datepart(dd,@data) else 31 end
     			AND ft.fdata <= @data
     			AND (ft.tipodoc<>4) 
     			AND ft.no <>17999
    
    	group by ft.fdata,ft.ettiliq,ft.ecusto
    
    Tom
    • Marked as answer by Night_devil Monday, July 5, 2010 4:52 PM
    Monday, July 5, 2010 4:37 PM