Divide by Zero error encountered (1 Viewer)

[Mittle]

I have converted the following access IIF statements to SQL Server TSQL and getting DIVIDE by Zero error . Can anyone help please

Access
------([EndDateFor]-(IIf([EndDateLastBase] Is Null,[EndDateBase],
[EndDateLastBase])))/(IIf([EndDateLastBase] Is Null,[EndDateBase],[EndDateLastBase])-IIf([StartDateLastBase] Is Null,[StartDateBase],[StartDateLastBase])) AS [End
Date - Variance (%)],


SQL Server
--[End Date - Variance (%)] =datediff(day,[EndDateFor],Coalesce([EndDateBase],[EndDateLastBase]))/datediff(day,Coalesce([EndDateBase],[EndDateLastBase]),Coalesce([StartDateBase],[StartDateLastBase]))

 

[Minty]

I would simply debug this by using this;

SELECT datediff(day,[EndDateFor],Coalesce([EndDateBase],[EndDateLastBase]))

SELECT datediff(day,Coalesce([EndDateBase],[EndDateLastBase]),Coalesce([StartDateBase],[StartDateLastBase]))

to see what values are being returned.

I don't think the first statement (the Coalesce) is the right way around, it will use EndBaseDate if it's not null regardless of EndDateLastBase. You want it the other way around.

Same with the second part.
They are both doing the opposite of your access function.

 

[plog]

You need to not divide by zero. You are doing division and the bottom part is evaluating to 0. You need to catch it and not do division when the bottom will be 0. Here's essentially what you have:

[End Date - Variance(%)] = A/B

Where A and B are the datediff/Coalesce functions. In some cases B is 0. So you need to catch those cases and not do the division.

For that you will need a Case statement:

CASE (Transact-SQL) - SQL Server​

Transact-SQL reference for the CASE expression. CASE evaluates a list of conditions to return specific results.
docs.microsoft.com

[End Date - Variance(%)] = CASE WHEN B = 0 THEN 0
ELSE A/B
END

 

[Mittle]

I would simply debug this by using this;

SELECT datediff(day,[EndDateFor],Coalesce([EndDateBase],[EndDateLastBase]))

SELECT datediff(day,Coalesce([EndDateBase],[EndDateLastBase]),Coalesce([StartDateBase],[StartDateLastBase]))

to see what values are being returned.

I don't think the first statement (the Coalesce) is the right way around, it will use EndBaseDate if it's not null regardless of EndDateLastBase. You want it the other way around.

Same with the second part.
They are both doing the opposite of your access function.

Thanks yes you are correct, it is meant to be the other way round .I have now corrected in my code

 

[Mittle]

I would simply debug this by using this;

SELECT datediff(day,[EndDateFor],Coalesce([EndDateBase],[EndDateLastBase]))

SELECT datediff(day,Coalesce([EndDateBase],[EndDateLastBase]),Coalesce([StartDateBase],[StartDateLastBase]))

to see what values are being returned.

I don't think the first statement (the Coalesce) is the right way around, it will use EndBaseDate if it's not null regardless of EndDateLastBase. You want it the other way around.

Same with the second part.
They are both doing the opposite of your access function.

you are correct .I have changed the Code the other way round and yes thats exactly how I debugged and was still getting the Divide by zero error. This issue has now been resolved. thanks very much