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?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2021 at 4:38 pm
Are you hoping for some T-SQL, or are the suggestions already provided sufficient?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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
Change is inevitable... Change for the better is not.
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