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

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
Asked:
waro
  • 3
  • 2
1 Solution
 
MikeTooleCommented:
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
 
waroAuthor Commented:
Thank you Mike.

Your allocation is o.k.
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.

Thank you for your Comment.
A Example woult be great.
0
 
MikeTooleCommented:
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.
The rows that had the most rounding are the ones adusted.
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 @AdjustID as table(id integer)
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

Open in new window

0
 
waroAuthor 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
 
waroAuthor Commented:
Many thanks for complete, accurate and easy to unterstand Sample
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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