I'd like to find an iterative way to get the percentage difference from data returned on a full outer join that allows for both current and previous values to be nulls as they may have dropped out of either result set.
Allowing for the previous value is fine under the usual calc which would be....
round(abs(100.0*(isnull(am.[Current Value],0) - isnull(pm.[Previous Value],0)) / isnull(pm.[Previous Value],0)), 2) <> 0
However this wont allow the current value to be zero and you'll get a divide by zero error. The only relatively safe workaround I've thought of so far is by setting an isnull value to 1 and then checking to see the amount matches the current and setting to 100, but I don't think I can rely on it long term, does anyone have a better offering?
case when round(abs(100.0*(isnull(am.[Current Value],0) - isnull(pm.[Previous Value],0)) / isnull(pm.[Previous Value],1)), 2) = (isnull(am.[Current Value],0)*100) then 100
else round(abs(100.0*(isnull(am.[Current Value],0) - isnull(pm.[Previous Value],0)) / isnull(pm.[Previous Value],1)), 2)
end '%Diff'
No comments:
Post a Comment