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]))
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]))