Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Little calculation help?

Discussion in 'Information Technology' started by DevourU, Oct 28, 2005.

  1. DevourU

    DevourU
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Hey little girl-Icehouse Blogs Oct 16, 2015
    Self the little bear Blogs Oct 16, 2015
    Self The Little Bear Blogs Dec 29, 2014
    A Little EGO May Be A Good Thing By IJ Singh Sikh Sikhi Sikhism May 6, 2015
    USA Sikh Little League Coach Allegedly Victim of Anti-Hindu Hate Crime Breaking News Oct 5, 2013

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    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.

    --
    Joseph Meehan

    Dia duit
     
  4. Nick Stansbury

    Nick Stansbury
    Expand Collapse
    Guest

    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


    > 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
    >
    >
     
  5. DevourU

    DevourU
    Expand Collapse
    Guest

    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
    "
    "



    "Joseph Meehan" <sligojoe_Spamno@hotmail.com> wrote in message
    news:Eh78f.159759$lI5.42848@tornado.ohiordc.rr.com...
    > 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.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
     
  6. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    DevourU wrote:
    > 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
    > "
    > "
    >
    >
    >
    > "Joseph Meehan" <sligojoe_Spamno@hotmail.com> wrote in message
    > news:Eh78f.159759$lI5.42848@tornado.ohiordc.rr.com...
    >> 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.
    >> --
    >> Joseph Meehan
    >>
    >> Dia duit


    --
    Joseph Meehan

    Dia duit
     
  7. DevourU

    DevourU
    Expand Collapse
    Guest

    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

    "Joseph Meehan" <sligojoe_Spamno@hotmail.com> wrote in message
    news:8D98f.159774$lI5.92351@tornado.ohiordc.rr.com...
    > DevourU wrote:
    >> 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
    >> "
    >> "
    >>
    >>
    >>
    >> "Joseph Meehan" <sligojoe_Spamno@hotmail.com> wrote in message
    >> news:Eh78f.159759$lI5.42848@tornado.ohiordc.rr.com...
    >>> 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.
    >>> --
    >>> Joseph Meehan
    >>>
    >>> Dia duit

    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
     

Share This Page