Link to home
Start Free TrialLog in
Avatar of r3nder
r3nderFlag for United States of America

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
Avatar of r3nder

ASKER

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

Another division here: SUM(RentalDays)*24/SUM(Failure)

It didn't return the line number with the error?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of r3nder

ASKER

@shaun I don't get you about the grouping
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  
Avatar of r3nder

ASKER

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.