Solved

# Calculate Percentage share of Qty

Posted on 2009-04-29
647 Views
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
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?
0

Author Comment

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

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

0

Author Closing Comment

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

## Featured Post

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 (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…