Little calculation help?

[DevourU]

I am working on an inventory cycle count database. I have a table with part
numbers, qty, and cost. The task at hand is to figure out which parts are
the top 80% of the total inventory value, then the next 15%, then the last
5%. A very basic example would be:

Total Part A $60
Total Part B $20
Total Part C $10
Total Part D $5
Total Part E $4
Total Part F $1

Group 1 (80%) is part A and part B, Group 2 (15%) is part C and D, then
Group 3 (5%) is part E and F.

I'm hoping someone has been here already and can save me from all the pain
and suffering. TIA!

-JS

 

[Nick Stansbury]

I've just noticed an earlier reply to your post asking for more
information - I think Joseph is right - more information would help. But
I've presumed a structure below - I hope it's close to what you want. I
haven't tested any of this code - so there may be some typo's etc. But I
think the concept is sound. I'm sure someone out there will point out a good
reason why my method won't work - or the multitude of errors I've made
though!

Trying doing it in stages:

Select Sum(Amount) as Total, Count(0) as CountOfItems from Inventory -
should give you

Total Count
$100 6

So if you derive from this you can do :

Select Item, [Amount] / TotalAmount as Portion, CountOfItems from (Select
Sum(Amount) as Total, Count(0) as CountOfItems from Inventory) dtTotals,
Inventory order by Portion

This should give you:
Item Portion CountOfItems
A 0.6 6
B 0.2 6
C 0.1 6
D 0.05 6
E 0.04 6
F 0.01 6

Now the question is how to derive these preset limits you've specified - our
first inclination is naturally to use a row by row methodology - but
actually it isn't necceesary if you use a temp table and try something
sneaky (note that I am relying on our earlier order by clause here - it's
very important to order these by their portions or this method won't work at
all!) -

create table #InventoryPortions (ItemNo int, Portion int, CumulativePortion
int, Group int)

insert into #InventoryPortions (ItemNo, Portion, CumulativePortion)
select Item, Portion, (Select isNull(Max(CumulativePortion),0) from
#InventoryPortions) + dtOuter.Portion from
(Select Item, [Amount] / TotalAmount as Portion, CountOfItems from (Select
Sum(Amount) as Total, Count(0) as CountOfItems from Inventory) dtTotals,
Inventory ) dtOuter

This should give us:
Item Portion CumulativePortion Group
A 0.6 0.6
B 0.2 0.8
C 0.1 0.9
D 0.05 0.95
E 0.04 0.99
F 0.01 1.0

Then you should be able to calculate your groups pretty easily I hope -
three queries will now do it

update #InventoryPortions set Group = 1 from #InventoryPortions where
CumulativePortion < 0.81
update #InventoryPortions set Group = 2 from #InventoryPortions where
CumulativePortion > 0.8 and CumulativePortion < 0.951
update #InventoryPortions set Group = 3 from #InventoryPortions where
CumulativePortion > 0.95

select * from #InventoryPortions

drop table #InventoryPortions

Now I'm afraid I've got no clue about Jet SQL so this is all T-SQL - I hope
it helps a little!

Nick

 

[DevourU]

The trick is which parts make up each group. Group 1 are the parts making up
top 80% of total inventory value, then Group 2 is the next top 15% of the
total inventory, then the rest of the parts making up the last 5%. I know it
may be confusing. In my example Part A and B make up the top 80%, Part C and
D make up the next 15 %, then E and F for the last 15%. Clear as mud?

[partno]*[qty]=total
"
"

 

[DevourU]

Thankx for the replies, sorry for not getting it clear. It is extended cost
of on hand parts in inventory. This an over simplified example with a total
inventory of $100

Part1 ([qty]*[cost])=$30
Part2 ([qty]*[cost])=$30
Part3 ([qty]*[cost])=$20
Part4 ([qty]*[cost])=$10
Part5 ([qty]*[cost])=$5
Part6 ([qty]*[cost])=$4
Part7 ([qty]*[cost])=$1

Parts in the top 80% are part1, 2, and 3 ($80), Parts in the next 15% are
part4 and 5 ($15), and the rest of the parts will be in the last 5% ($5).
Look at it as grouping the most expensive top 80% of you onhand inventory
parts, then the next 15%, then 5%.
Better?

-JS

The trick is which parts make up each group. Group 1 are the parts
making up top 80% of total inventory value, then Group 2 is the next
top 15% of the total inventory, then the rest of the parts making up
the last 5%. I know it may be confusing. In my example Part A and B
make up the top 80%, Part C and D make up the next 15 %, then E and F
for the last 15%. Clear as mud?

What is "inventory value" Is it computed by looking at the individual
cost of each part? The cost of each part times the number on hand? The
number sold?

[partno]*[qty]=total
"
"

DevourU wrote:
I am working on an inventory cycle count database. I have a table
with part numbers, qty, and cost. The task at hand is to figure out
which parts are the top 80% of the total inventory value, then the
next 15%, then the last 5%. A very basic example would be:

Total Part A $60
Total Part B $20
Total Part C $10
Total Part D $5
Total Part E $4
Total Part F $1

Group 1 (80%) is part A and part B, Group 2 (15%) is part C and D,
then Group 3 (5%) is part E and F.

I'm hoping someone has been here already and can save me from all
the pain and suffering. TIA!

-JS

My first take on this is that it appears you may have a poorly
designed database. How about some more information. List all the
fields in the table(s). I am a little confused about where the
groups come in. Do you have multiple entries of the same part for
different groups or maybe the same part at different values or
invoices?
Giving you a good answer to your question may require more
information.