Calculate Percentage share of Qty

Posted on 2009-04-29
Last Modified: 2012-05-06
I have to create a Function which allocates Qty (Full Pieces) based on Percentage.
I have on Table with Percentage Share
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.

Question by:waro
    LVL 27

    Expert Comment

    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?  

    Author Comment

    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.
    LVL 27

    Accepted Solution

    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)
    @Allocation table(id Integer, result integer, qty decimal(18,2), UpDown integer, Delta  decimal(2,2))
    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) 

    Open in new window


    Author Comment

    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.


    Author Closing Comment

    Many thanks for complete, accurate and easy to unterstand Sample

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now