locked
Divide by zero error RRS feed

  • Question

  • Hi,

    I'm trying to get a "percent of overtime" from the query below:

    I get a 'divide by zero error'

    I need to dive the 'total_pr' by the 'total_ot' then multiply by 100 to get the percentage.

    SELECT Fullname AS [Full Name],pehempno AS [Emp No] ,pehearncode As [Earn Code],ERNSTUBDESC As [Stub Desc], pehratefactor As [Rate Factor], OT_Hrs AS [Overtime Hours], OT_Amt AS [Overtime Amt], OT_Shift AS [Overtime Shift],
    OT_Hrs + OT_amt + OT_shift as [Overtime Total], TOTAL_PR AS [PR Total], PayDate AS [Pay Date], Org2CodeDesc AS [Site Desc], TOTAL_PR / (OT_Hrs + OT_amt + OT_shift) * 100 as [Percent of Overtime]
    FROM (
    	Select FullName ,pehempno,pehearncode,ERNSTUBDESC
    		  ,pehratefactor 
    			 ,case
    			 when pehearncode in ('0002','0022','0025','0066','0104','AI02','N002','N104')
    			 then pehcurhrs  else 0
    		   end        AS OT_Hrs
    		  ,case
    			 when pehearncode in ('0002','0022','0025','0066','0104','AI02','0066','0104','N002','N104')
    			 then pehcuramt else 0
    		   end        AS OT_Amt
    		  ,case
    			 when pehearncode in ('0002','0022','0025','0066','0104','AI02','0066','0104','N002','N104')
    			 then PehShfShiftAmt  else 0
    		   end        AS OT_Shift
    		  ,pehcuramt  AS TOTAL_PR
    		  ,pehpaydate AS PayDate
    		  ,RTRIM(PEHORGLVL2) + ' - ' + DBO.WSI_F_GET_ORGDESC(PEHORGLVL2,2) AS Org2CodeDesc
    	from wsi_v_personnel_data
    		inner join pearhist   on eeid  = peheeid
    		INNER JOIN ORGLEVEL   ON PEHORGLVL2   = ORGCODE AND ORGLVL = 2
    		INNER JOIN EARNCODE   ON PEHEARNCODE  = ERNEARNCODE
    	 --where pehorglvl2 = 'AGIKEC'
    	 and pehpaydate >= '01/01/2013'
    	--ORDER BY PEHORGLVL2 ,fullname,PEHEMPNO
    ) X


    qeqw

    Thursday, November 14, 2013 2:33 PM

Answers

  • Hello,

    Add a CASE WHEN statement to check if the devider equals 0; if so return a fix value, otherwise the result of the division:

    SELECT ....
          ,CASE WHEN OT_Hrs + OT_amt + OT_shift = 0 THEN 0.0
                ELSE TOTAL_PR / (OT_Hrs + OT_amt + OT_shift) * 100
                END as [Percent of Overtime]


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by rqwew Thursday, November 14, 2013 2:48 PM
    Thursday, November 14, 2013 2:42 PM
  • Perfect thanks Olaf!

    qeqw

    • Marked as answer by rqwew Thursday, November 14, 2013 5:35 PM
    Thursday, November 14, 2013 2:54 PM

All replies

  • Hello,

    Add a CASE WHEN statement to check if the devider equals 0; if so return a fix value, otherwise the result of the division:

    SELECT ....
          ,CASE WHEN OT_Hrs + OT_amt + OT_shift = 0 THEN 0.0
                ELSE TOTAL_PR / (OT_Hrs + OT_amt + OT_shift) * 100
                END as [Percent of Overtime]


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by rqwew Thursday, November 14, 2013 2:48 PM
    Thursday, November 14, 2013 2:42 PM
  • Perfect thanks Olaf!

    qeqw

    • Marked as answer by rqwew Thursday, November 14, 2013 5:35 PM
    Thursday, November 14, 2013 2:54 PM
  • If you using SQL 2012, you can NULLIF function to avoid divide by zero errors.

    Declare @test int=0
    select 1/@test
    
    go
    Declare @test int=0
    select 1/NULLIF(@test,0)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 15, 2013 6:20 AM
  • If you using SQL 2012, you can NULLIF function to avoid divide by zero errors.

    Declare @test int=0
    select 1/@test
    
    go
    Declare @test int=0
    select 1/NULLIF(@test,0)

    Just an info, NULLIF is available right from SQL Server 2005 ownwards.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 15, 2013 6:49 AM
  • If you using SQL 2012, you can NULLIF function to avoid divide by zero errors.

    Declare @test int=0
    select 1/@test
    
    go
    Declare @test int=0
    select 1/NULLIF(@test,0)

    Just an info, NULLIF is available right from SQL Server 2005 ownwards.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Sorry, Its my bad. 

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 15, 2013 7:50 AM