r3nder
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
)
Another division here: SUM(RentalDays)*24/SUM(Fai lure)
It didn't return the line number with the error?
It didn't return the line number with the error?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
But this one does --> SELECT MAX(Temp_Failures.Yr) FROM Temp_Failures GROUP BY Yr,Mo
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.
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.
Open in new window
equals zero?