Divide by zero error encountered sql

Can anyone see the division by zero - It is eluding me
 DELETE FROM calendar;  
 exec fill_calendar @start_date = '2012-01-01',@end_date = '2015-12-31'; 
 IF Object_id('Temp_Failures', 'U') IS NOT NULL  
 DROP Table Temp_Failures;  
 --create
 CREATE TABLE Temp_Failures
 (
 Yr int,
 Mo int,
 RentalDays int,
 Failure int,
 [Hours] int
 );
 IF Object_id('Temp_Totals', 'U') IS NOT NULL
 DROP Table Temp_Totals;  
 --create
 CREATE TABLE Temp_Totals
 (
 Yr int,
 Mo int,
 RentalDays int,
 Failure int,
 [Hours] int
 );
 INSERT INTO Temp_Failures(Yr,Mo,RentalDays,Failure,Hours)
 SELECT Year(offlocationdatetime)                                               AS Yr,  
 Month(offlocationdatetime)                                                     AS Mo,  
 nullif(Sum(sfrentaldays + wfrentaldays),0)                                     AS RentalDays,  
 nullif(Count(jde.RigFailureTime + jde.ToolFailureTime + jue.FailureTime),0)    AS Failure,  
 nullif((Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime)  * 24),0) AS 'Hours'  
 FROM   Job  
 LEFT JOIN JobRentalTicket ON Job.Jobid = JobRentalTicket.Jobid  
 LEFT JOIN JobDownholeEquipment AS jde ON Job.jobid = jde.jobid  
 LEFT JOIN JobUpholeEquipment AS jue ON Job.jobid = jue.jobid  
 WHERE  (( ( Job.offlocationdatetime ) > '2012-01-01 00:00:00'  AND ( Job.offlocationdatetime ) < '2015-12-31 23:59:59' ))  
 GROUP  BY Year(offlocationdatetime),  Month(offlocationdatetime),  Job.company  
 HAVING  Job.company  = 'JB Hunt' ;  
 INSERT INTO Temp_Totals(Yr,Mo,RentalDays,Failure,Hours)
 SELECT calendar.Yr AS Yr,calendar.Mo as Mo,  
 nullif(Temp_Failures.RentalDays,0) as RentalDays,  
 nullif(Temp_Failures.Failure,0) as Failure,  
 nullif(Temp_Failures.Hours,0) as Hours  
 FROM Temp_Failures 
 RIGHT JOIN calendar ON Temp_Failures.Yr = calendar.Yr AND Temp_Failures.Mo = calendar.Mo  
 WHERE calendar.Yr BETWEEN (SELECT MIN(Temp_Failures.Yr) FROM Temp_Failures)  
 AND 
 (
 SELECT MAX(Temp_Failures.Yr) FROM Temp_Failures GROUP BY Yr,Mo  
 )  
 
   
 Insert INTO Temp_Totals(Yr,Mo,RentalDays,Failure,Hours)
 Select CAST(''as CHAR) as Yr,Cast('' AS CHAR) as Mo,  
 nullif(SUM(RentalDays),0) as RentalDays,  
 nullif(SUM(Failure),0) as Failure,  
 nullif(SUM(RentalDays)*24/SUM(Failure),0) as Hours  From Temp_Failures;  
 Select  CAST(CASE WHEN Yr  = 0 THEN '' ELSE Yr END AS CHAR) as 'Yr',
 CAST(CASE WHEN Mo = 0 THEN '' ELSE Mo END AS CHAR) as Mo ,RentalDays,Failure,[Hours] 
 FROM Temp_Totals 

Open in new window

LVL 6
r3nderAsked:
Who is Participating?
 
sdstuberCommented:
this must be returning 0

COUNT(rigfailuretime + jde.toolfailuretime + jue.failuretime)


the other division you force NULL if 0, so that leaves this one

you also have a NULLIF()  for this value in the previous line; but that only applies to subsequent usage.  In the division itself it's still possible to be 0
0
 
Shaun KlineLead Software EngineerCommented:
Since you are grouping my year and month, are there any year/month where
RigFailureTime + jde.ToolFailureTime + jue.FailureTime

Open in new window

equals zero?
0
 
r3nderAuthor Commented:
I guess that fixed it now I have a new error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

INSERT INTO Temp_Totals(Yr,Mo,RentalDays,Failure,Hours)
 SELECT calendar.Yr AS Yr,calendar.Mo as Mo,  
 nullif(Temp_Failures.RentalDays,0) as RentalDays,  
 nullif(Temp_Failures.Failure,0) as Failure,  
 nullif(Temp_Failures.Hours,0) as Hours  
 FROM Temp_Failures 
 RIGHT JOIN calendar ON Temp_Failures.Yr = calendar.Yr AND Temp_Failures.Mo = calendar.Mo  
 WHERE calendar.Yr BETWEEN (SELECT MIN(Temp_Failures.Yr) FROM Temp_Failures)  
 AND 
 (
 SELECT MAX(Temp_Failures.Yr) FROM Temp_Failures GROUP BY Yr,Mo  
 )  
 

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Another division here: SUM(RentalDays)*24/SUM(Failure)

It didn't return the line number with the error?
0
 
Shaun KlineLead Software EngineerCommented:
Remove Mo from the GROUP BY in the SELECT MAX subquery.
0
 
r3nderAuthor Commented:
@shaun I don't get you about the grouping
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
This one doesn't have GROUP BY --- > SELECT MIN(Temp_Failures.Yr) FROM Temp_Failures
But this one does --> SELECT MAX(Temp_Failures.Yr) FROM Temp_Failures GROUP BY Yr,Mo  
0
 
r3nderAuthor Commented:
Thanks - on to the next question
Msg 512, Level 16, State 1, Line 38
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.