• Status: Solved
• Priority: Medium
• Security: Public
• Views: 664

Calculate Percentage share of Qty

I have to create a Function which allocates Qty (Full Pieces) based on Percentage.
I have on Table with Percentage Share
e.g.
ID=1, Percent = 0.76
ID=2, Percent = 0.24
ID=3, Percent = 0.00

My Result schould be if I Allocate 51 Pcs:
ID=1, Percent = 0.76, Qty=40
ID=2, Percent = 0.24, Qty=11
ID=3, Percent = 0.00, Qty= 0

Any Idea how to code this in a single Statement.

0
waro
• 3
• 2
1 Solution

Commented:
I don't believe that this can be done in a 'single statement' - but if you're coding a function that's not a limitation.
There are two steps:
1) Calculate the rounded allocations
2) Adjust for any over/under allocation from the rounding process
I'll post some sample code shortly
By the way, my calculations show allocations of 38/13/0 rather than 40/11/0 - am I missing something?
0

Author Commented:
Thank you Mike.

Its a Question of Rounding.
Sorry I wrote not my exact Factors and I allways rounded Up.

I calculated as follows:
Ceiling(51*0.7692307692307)  = 40
Ceiling(51*0.2307692307692) = 12 (but should be only 11)

But it does not matter. Goal is to allocate Full Qty (51) and allways Full Pieces.

A Example woult be great.
0

Commented:
The attached function should show the way.
It allocates the quantities using round up/down, then checks to see if the the target is hit.
If there's a difference between the allocations and the target it adjusts sufficient rows to hit the target.
``````Alter FUNCTION test.AllocateItems (	@TargetQuantity int)
RETURNS
@Allocation table(id Integer, result integer, qty decimal(18,2), UpDown integer, Delta  decimal(2,2))

AS
BEGIN
declare @Delta as integer
declare @UpDown as integer
-- Get the initial result
insert into @allocation(id, result, qty)
select id, 	cast(round(pct*@TargetQuantity,0) as integer) as result, pct*@TargetQuantity as qty
from Test.percentage
-- Mark the roundings
update @Allocation
set UpDown = Case when qty > result then 1  when qty < result then -1 else 0 end,
Delta = abs(qty-result)
select @Delta = @TargetQuantity - (select SUM(result) from @Allocation)
If @Delta = 0
RETURN -- The target is hit
-- Adjust the quantities to hit the target
select @UpDown = case when @Delta < 0 then -1 when @Delta > 0 then 1 else 0  end
Insert into @AdjustID select top(ABS(@delta)) id from @Allocation where UpDown = @UpDown order by Delta desc
Update @Allocation set result = result + @UpDown where id in (select id from @AdjustID)
RETURN
END
``````
0

Author Commented:
Dear Mike,

Thank you for you great Example.
It works as expected and gave me some Ideas how to code my UDF.
As I can see in you code you are really an expert.

0

Author Commented:
Many thanks for complete, accurate and easy to unterstand Sample
0

Featured Post

• 3
• 2
Tackle projects and never again get stuck behind a technical roadblock.