divide by zero error

The standard way is to test the denominator with a CASE block...

CASE denominator WHEN 0 THEN 0 ELSE numerator / denominator END as column_name

Select allOpen in new window

So to avoid any fat-finger typos I'll let you take the above and alter your code accordingly.

One method would be to use the CASE statement to see if ISNULL(Prev, 0) equals zero. If it does, then substitute whatever value you want for ActualVariance when this situation occurs. Otherwise, you would perform your calculation.

Another option would be to use the WHERE clause to filter out data where ISNULL(Prev, 0) doesn't equal zero.

What you want to do in the prev column is NULL?
Modifying it to zero it will give you the actual error so do you think it should be changed to one (1)?

UPDATE dbo.TblCompareEvents
SET ActualVariance = ((CAST(isnull(prev,0) AS REAL) - CAST(change AS REAL))/(CAST(isnull(prev,1) AS REAL)*100))
WHERE TRY_CONVERT(REAL,Prev)IS NOT NULL
    AND TRY_CONVERT(REAL,change) IS NOT NULL

Select allOpen in new window

in the previous iteration with just the subtraction I had tried to get it to put 0 instead of null. so it would do the calculation. the same applies albeit a different calculation BUT there is a high probability that I didnt understand it fully

ive put to:

	UPDATE dbo.TblCompareEvents

SET ActualVariance = ((CAST(prev AS REAL) - CAST(change AS REAL))/(CAST(prev AS REAL)*100))
WHERE TRY_CONVERT(REAL,Prev)IS NOT NULL
    AND TRY_CONVERT(REAL,change) IS NOT NULL

Select allOpen in new window

That would work as long as Prev can never be zero.

and ive added this before i run thie current question code so as to correct that issue

UPDATE dbo.TblCompareEvents
SET ActualVariance =0
WHERE Change not like '%[^0-9]%' and Change != '' and prev is null

Select allOpen in new window

You need to check if there's no prev with '0' on it:

SELECT *
FROM TblCompareEvents
WHERE prev='0'

Select allOpen in new window

If this query returns rows then you still have the issue.

I still get the divide by zero although i guess that means the sql is actually ok

yes vitor ok I will create an update to deal with that too now i get your earlier statement change it to 1

I thought this might work but it gives conversion failed when converting nvarchar '39.8' to data type int

I was following on trying the case doesnt look right

select  PercDiff =

CASE prev

WHEN 0 THEN 0.01
WHEN '0' THEN 0.01
else (cast([ActualVariance] as int)/(CAST([prev] AS REAL)*100))

END 
from TblCompareEvents
where ActualVariance is not null

Select allOpen in new window

39.8 is not integer. Is a decimal number so you need to convert it to float or real:
else (cast([ActualVariance] as float)/(CAST([prev] AS REAL)*100))

ive taken the maths out and attached an excel file so DONT open if your on a phone please.

select  prev, change, actualvariance
--, PercDiff =
--CASE prev

--WHEN 0 THEN 0.01
--WHEN '0' THEN 0.01
--else (cast([ActualVariance] AS REAL)/(CAST([prev] AS REAL)*100))

--END 

from TblCompareEvents
where ActualVariance >0 and change<>'0'

Select allOpen in new window

it was leading up to your idea from previous calculation

	UPDATE dbo.TblCompareEvents
SET PercentageDiff = (cast([ActualVariance] as real)/(CAST([prev] AS REAL)*100))
WHERE TRY_CONVERT(real,Prev)IS NOT NULL
AND TRY_CONVERT(real,change) IS NOT NULL

Select allOpen in new window

the actual variance is now populated its just that zero or data type clash
ee.xlsx

I didn't see the Excel file yet but if your ActualVariance field is already REAL you don't need to convert it:

[ActualVariance] /(CAST([prev] AS REAL)*100)

Select allOpen in new window

yes i changed at one point.

its still flickering so its partially working i dont know where its trying to change 39.8 to an int

i tried this and its ran:

	UPDATE dbo.TblCompareEvents
SET PercentageDiff = [ActualVariance]/CAST([prev] AS REAL)*100
WHERE TRY_CONVERT(real,Prev)IS NOT NULL
AND TRY_CONVERT(real,change) IS NOT NULL
and TRY_CONVERT(real,Prev) <>0

Select allOpen in new window

its led to this if i can solve this part where it could substitute where 0 to 0.001 to allow the calc to take place

Those are the cases when prev = '0'. You can use a CASE statement for those cases:

UPDATE dbo.TblCompareEvents
SET PercentageDiff = CASE Prev
				WHEN '0' THEN 0
				ELSE [ActualVariance]/CAST([prev] AS REAL)*100
			END
WHERE TRY_CONVERT(real,Prev)IS NOT NULL
	AND TRY_CONVERT(real,change) IS NOT NULL

Select allOpen in new window

now it went to the divide by zero again maybe it has another case

(3920 row(s) affected)

(82298 row(s) affected)

(82298 row(s) affected)
Msg 8134, Level 16, State 1, Procedure usp_UpdateActualVariance, Line 38 [Batch Start Line 3]
Divide by zero error encountered.

Select allOpen in new window

UPDATE dbo.TblCompareEvents
SET PercentageDiff = CASE Prev
				WHEN '0' THEN 0
				WHEN 0 Then 0.1
				ELSE [ActualVariance]/CAST([prev] AS REAL)*100
			END
WHERE TRY_CONVERT(real,Prev)IS NOT NULL
	AND TRY_CONVERT(real,change) IS NOT NULL

Select allOpen in new window

i did this to discount the actual variance column and that processed so a zero is getting through on the prev

SET PercentageDiff = CASE Prev
				WHEN '0' THEN 0	
				--[ActualVariance]/CAST([prev] AS REAL)*100
				ELSE [ActualVariance]/6*100
			END

Select allOpen in new window

ive got it to do it but as a separate step

	UPDATE dbo.TblCompareEvents
SET PercentageDiff = [ActualVariance]/CAST([prev] AS REAL)*100
WHERE TRY_CONVERT(real,Prev)IS NOT NULL
AND TRY_CONVERT(real,change) IS NOT NULL
AND TRY_CONVERT(real,Prev) <>0


UPDATE dbo.TblCompareEvents
SET PercentageDiff = [ActualVariance]/CAST([prev]+1 AS REAL)*100
where ActualVariance >0 and prev='0'

Select allOpen in new window

thank you