Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Unanswered: workaround "Divide by zero error encountered " message

    Hey all,

    When i exec an sp it runs a sum, sometimes it is possible that there is a 0 value, it then returns an error Divide by zero error encountered
    How can i work around this error

    My sp code is

    Code:
    CREATE proc CP_avgloss_rings
    @mID varchar(10),
    @startdate datetime,
    @enddate datetime 
    as 
    select ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600) as avgloss
    from dbo.rings
    where 
    	machineID = @mID 
    	and convert(varchar,njdate,121) 
    		between convert(varchar,@startdate,121) 
    		and convert(varchar,@enddate,121)
    GO
    Maybe someone can help me
    Cheers Wimmo

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    CREATE proc CP_avgloss_rings
    @mID varchar(10),
    @startdate datetime,
    @enddate datetime 
    as 
    select 
    	case 
    		((sum(playtime))/ 3600)
    		when 0 
    		then 0
    		else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600) 
    	end avgloss,
    from dbo.rings
    where 
    	machineID = @mID 
    	and convert(varchar,njdate,121) 
    		between convert(varchar,@startdate,121) 
    		and convert(varchar,@enddate,121)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Get this error message
    Server: Msg 156, Level 15, State 1, Procedure CP_avgloss_rings2, Line 13
    Incorrect syntax near the keyword 'from'.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    You don't need the comma after avgloss.

  5. #5
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    This solves it all
    Thanx you guys you are the bomb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •