T-SQL (SS2K8) :: Predefined Size Groups - Factor / Rate Rounding

When sizing products we use predefined size groups that the users can choose any or all of the sizes from. For example if i size group consisted of sizes (6,8,10) they could use all sizes (6,8,10) or just (6,8) or just (10) if required. Similarly, if a group consisted of (S,M,L,XL) they could choose to only buy (S,L). They cannot choose across groups, so would not be able to choose (6,S)

Once the required sizing is determined they then assign size mixes to the sizes to denote how much of the buy will be in that size. So for example if we had 3 sizes: (6,8,10) and they had the associated mixes (25%,25%,50%) that would mean we would buy 25% of size 6 and 50% of size 10. All size mixes must add up to 100% in total.

The users do analysis to determine what sizes they wish to buy and how much of it.

We also have a franchise portion of the business that have some predefined size mixes. They use the same base size groups as above, but the rule is that they can only use sizes that the particular product is being bought in.

So if the assigned franchise mix is S (50%), M (50%) and the main mix was S (100%) then the franchise mix would only be able to then have the S size.

We would then eliminate the sizes from the franchise mix and then to ensure that the franchise mix still adds to 100 we would then pro-rate up the franchise mix to give a new mix. To do this I divide one by the total the remaining size mixes to get a ratio and then multiple the mixes by this factor.

In the case above not be able to use the M size and would only use the S.This would be

-Total of remaining mixes, in this case only size S for simplicity
1 / 0.5 = 2

-multiple original mix by this factor
0.5 * 2 = 1

size S would now be 100% instead of 50%

The issue I'm having is that on occasion some of the totals are adding up to 100.01% because another one of the requirements is that it needs to be 4 decimal places (0.1015 would represent 10.15% in excel)

Here is a shortened version of the code with some test data:

DECLARE @SizeRange TABLE
(
SizeProfileIDINTNOT NULL,
CPCVARCHAR(3)NOT NULL,
SizeNameVARCHAR(5)NOT NULL,
SizeMixDECIMAL(14,4)NOT NULL

[Code] ...

How to get this to round to 4dp and still total 100%?