Split a given number in Percentages and round to integer

Question

Post reply

Split a given number in Percentages and round to integer

Steve Dell

SSCarpal Tunnel

Points: 4623
More actions
December 7, 2021 at 2:44 pm

#3961307

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 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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 2

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.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 3

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".

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

Steve Dell wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

;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)