Hi, we have raw data for counts of items within various groupings, counts of how many items errored in each grouping and counts of how many items succeeded in each grouping. The sum of errored + sum succeeded always = that groupings count of items.
i simulated to four decimal places (i didnt think more decimal places would tell me anything different) all combos of 2 outcome percentages to see if the rounding function (applied separately to each) could guarantee both percentages for each group would sum to 100% after fractions of percentages were removed. You can see the script below. There are 101 cases between .0000 and 1.0000 when incrementing / decrementing by .0001 where rounding doesn't guarantee such a thing.
i kept the rounding in but applied a final tweak you can see in the partial selection list under the script. This tweak applies to cases where the 2 percentages do not total 100%. The tweak for these 101 cases is to set the second pct to 100 - the first pct.
if there is a more elegant / balanced way to do this, it would be easier for me to explain to my user.
declare @pct1 decimal (5,4)=.0000
declare @pct2 decimal (5,4)
declare @result decimal(5,4)
while @pct1 < 1.0
begin
set @pct2 = 1.0-@pct1
set @result = round(@pct1,2) + round(@pct2,2)
if 100 * @result <> 100.0 select 'an issue ' , @pct1,@pct2,round(@pct1,2) rounded1,round(@pct2,2) rounded2,
100*round(@pct1,2) dec1removed,
100*round(@pct2,2) dec2removed,
100-100*round(@pct1,2) recalcddec2removed
set @pct1 = @pct1 + .0001
end
select 'finished'
.............PctErrored, -- i dont believe these will ever exceed 100 with the rounding choice being used
case when (PctErrored is not null) and
(PctCompleted is not null) and
(PctErrored + PctCompleted <> 100.0)
then 100.0 - PctErrored
else PctCompleted end PctCompleted