December 7, 2021 at 2:44 pm
Hi ,I would like to split a given number into 3 numbers based on a percentage(98.6% , 1.2% and 0.2%) and round it to an integer value and the sum of 3 numbers should equal to the given number
For example a given number is 300 or 120,
for the given number 300, I would split into 3 Numbers bases on percentage 98.6%, 1.2% and 0.2% respectively and rounding to integer gives me 295 , 3 ,0 values but this sum of 3 numbers is 295+3+0 =298 and is not matching to the given number 300 .Any formula to avoid this kind of issues ? Thanks in advance
December 7, 2021 at 2:55 pm
Can you tell us what you would like the answer to be, for your example of 295, 3, 0?
One formula which avoids the problem is to calculate any two of the numbers and then subtract the sum of those from the number you started with. Is that accurate enough?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 7, 2021 at 4:08 pm
Hi Thank you for the response , I am looking for 3 values based on the defined percentages and the sum of 3 numbers should tally at the end to the given number .I am open for all options/suggestions.
December 7, 2021 at 4:13 pm
If you rounded the numbers, it seems like you'd end up with 296, 4, 1:
SELECT 300*.986, 300*.012, 300*.002 --295.800, 3.600, 0.600
The first round up seems clearest, so 296. You'd have to have rules to decide whether the other numbers should be 4, 0 or 3, 1. I'd say 3, 1 is more accurate, but that may not match your logic.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 7, 2021 at 4:38 pm
Are you hoping for some T-SQL, or are the suggestions already provided sufficient?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 7, 2021 at 7:24 pm
Hi ,I would like to split a given number into 3 numbers based on a percentage(98.6% , 1.2% and 0.2%) and round it to an integer value and the sum of 3 numbers should equal to the given number
For example a given number is 300 or 120,
for the given number 300, I would split into 3 Numbers bases on percentage 98.6%, 1.2% and 0.2% respectively and rounding to integer gives me 295 , 3 ,0 values but this sum of 3 numbers is 295+3+0 =298 and is not matching to the given number 300 .Any formula to avoid this kind of issues ? Thanks in advance
That doesn't sound like "rounding" to me. 300 * 98.6% = 295.8. Following the "general" rounding technique, that would round up to 296... not down to 295.
Same goes for 300 * 1.2% = 3.6, which would round up to 4, not down to 3.
Then, take the 300 and subtract the 2 previous results (like Phil said) and that would return 300-296-4 = 0 for the 300 * 0.2%. That's actually quite an error for the last value/smallest value when you compare it to 0. It should be 1.
So I'd reverse the process and start with the smallest percentage and the middle and subtract those two results for the total to get the 3rd value
--Jeff Moden
December 7, 2021 at 7:41 pm
;with cte as
(
select N
from (values (1000),(10001),(100002),(1000003),(1000004),(100000005),(1000000006)) T(N)
)
SELECT N,D+(N-(D+E+F)) D,E,F
from cte
cross apply(values (0.986 * N , 0.012*N, 0.02 * N )) T1(A,B,C)
cross apply(values (CONVERT(int,ROUND(A,0)),CONVERT(int,ROUND(B,0)),CONVERT(int,ROUND(C,0)))) T2(D,E,F)
December 9, 2021 at 11:40 am
This was removed by the editor as SPAM
December 9, 2021 at 11:41 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply