Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    20

    Unanswered: carrying metric through a table

    Hi. this is a tricky one to explain but I'm at a bit of a loss

    Consider a product demand table such as
    Product Demand Qty
    Prod 1 500
    Prod 2 300
    Prod 3 0

    Now consider a Inventory table such as
    Product Batch Total qty Free qty
    Prod 1 Batch 1 300 0
    Prod 1 Batch 2 300 100
    Prod 1 Batch 3 200 200
    Prod 2 Batch 22 600 300

    Wha I am trying to achieve is the "Free Qty" column such that, within each product, the demand is "comsumed" by each batch in turn until completly comsumed (or no batches are left). Hence the demand of 500 would result in 300 going to zero for batch 1, carry forward 200 demand which would be completly comsuemd by batch 2 leaving 100 "free". Subsequnet batches would remain all free for that first product. Then the operation continues for the next product.

    How on earth do I do it ?????

    Thanks for you thoughts

    Gerry
    Last edited by GerryP; 04-15-04 at 13:26.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just guessing, but I can't get mental "traction" on this problem. I passed on trying to answer, to let somebody else get a clear shot at it. Since nobody else has posted, I'm betting that they can't get a handle on it either. Maybe it would help if you explain just a wee bit more ?!?

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    20
    Thanks Pat. I'll try

    Perhaps I shoudl explain the physical process.
    you have a food store and you have all the "sell buy" dates for each incoming shipment. You have some orders for a given quantity and you want to simulate a FIFO (First in First out) process of use. So the "free to sell" of the oldest stock is reduced by the open orders leaving the newest stock to last. In fact I'm talking about electronic devices and the physical date of manufacture but the analogy is good. Does that explain ?

    Gerry

  4. #4
    Join Date
    Mar 2004
    Posts
    20
    [QUOTE][SIZE=1]Originally posted by GerryP
    Thanks Pat. I'll try

    Perhaps I shoudl explain the physical process.
    you have a food store and you have all the "sell buy" dates for each incoming shipment. You have some orders for a given quantity and you want to simulate a FIFO (First in First out) process of use. So the "free to sell" of the oldest stock is reduced by the open orders leaving the newest stock to last. In fact I'm talking about electronic devices and the physical date of manufacture but the analogy is good. Does that explain ?

    TO put it another way. here is a slice of the documentation I ma doing (ahead of the code.. Weird!)

    Take a demand of 1000 and a Batch content as here
    Batch Qty Free
    Batch 1 500 0 (1000-500 carried forward demand= 500)
    Batch 2 300 0 (500 300 Carried forward demand= 200
    Batch 3 450 250 All consumed: nothing carried forward
    Batch 4 400 400 All consumed: nothing carried forward

  5. #5
    Join Date
    Mar 2004
    Posts
    45
    CREATE TABLE Demand(
    Product smallint PRIMARY KEY,
    Qty smallint)

    INSERT Demand
    SELECT 1, 500
    UNION ALL SELECT 2, 300
    UNION ALL SELECT 3, 0

    CREATE TABLE Inventory(
    Product smallint,
    Batch smallint,
    Totalqty smallint,
    PRIMARY KEY(Product,Batch))

    INSERT Inventory
    SELECT 1, 1, 300, 0
    UNION ALL SELECT 1, 2, 300, 100
    UNION ALL SELECT 1, 3, 200, 200
    UNION ALL SELECT 2, 22, 600, 300

    SELECT i.Product, i.Batch, i.TotalQty,
    CASE
    WHEN
    (SELECT SUM(TotalQty)
    FROM Inventory
    WHERE Product = i.Product AND Batch <= i.Batch) <= d.Qty
    THEN 0
    WHEN
    (SELECT SUM(TotalQty)
    FROM Inventory
    WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty < TotalQty
    THEN
    (SELECT SUM(TotalQty)
    FROM Inventory
    WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty
    ELSE TotalQty
    END FreeQty
    FROM Inventory i LEFT JOIN Demand d ON i.Product = d.Product

    If there will always be row in Demand for each product, then just use inner join instead of left join.
    Hans.

  6. #6
    Join Date
    Mar 2004
    Posts
    20
    It worked a DREAM.
    I think you missed out defining a fourth metric in Inventory but thats fine. It's good for me to add just a little value.

    My question now is.. How does it work ?
    reformating the core somewhat we see
    SELECT i.Product, i.Batch, i.TotalQty,
    CASE
    WHEN
    (SELECT SUM(TotalQty) FROM Inventory WHERE Product = i.Product AND Batch <= i.Batch) <= d.Qty THEN 0
    WHEN
    (SELECT SUM(TotalQty) FROM Inventory WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty < TotalQty
    THEN
    (SELECT SUM(TotalQty) FROM Inventory WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty
    ELSE TotalQty
    END FreeQty
    FROM Inventory i LEFT JOIN Demand d ON i.Product = d.Product

    what is not clear is how it carries the 'remainder' down from one batch to the next ? There is no question it works... but how ?

    Thanks again

    Gerry

  7. #7
    Join Date
    Mar 2004
    Posts
    45
    Do not use code unless you understand it.

    Try this query to understand value of subquery:

    SELECT i.Product, i.Batch, i.TotalQty, d.Qty,
    (SELECT SUM(TotalQty)
    FROM Inventory
    WHERE Product = i.Product AND Batch <= i.Batch) CumulativeTotal
    FROM Inventory i LEFT JOIN Demand d ON i.Product = d.Product

    Unless this is static OLAP database, FreeQty column should not be denormalized into Inventory table because that will add complexity to OLTP code and hinder performance.
    Hans.

  8. #8
    Join Date
    Mar 2004
    Posts
    20

    Smile

    Ah. The proverbial penny has dropped and I understand !

    I've applied it to my real world example with excellent results

    Thanks for your help and have a good weekend


    Gerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •