Wednesday, 20 April 2016

tsql - How to find percentage difference when using a full outer join on a rec

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

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...