Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Trying to NOT use a cursor...

    I need to write a sproc to supply records for a report. The boss has asked
    "Of all the tons on order right now, how much is already in inventory, and how much needs to be produced." And "Apply the same logic to just the orders that came in yesterday." It would have been easy, if he hadn't asked for the second part, because now I have to look at each product on each order, rather than comparing total orders for a product to total available inventory.

    Here's some sample data for what I need to do:

    Code:
    CREATE TABLE ORDER_ITEM (
    ORDER_NUM   VARCHAR(10),
    SHIP_DATE SMALLDATETIME,
    PRODUCT   VARCHAR(10),
    ORD_TONS    REAL)
    
    INSERT INTO ORDER_ITEM (ORDER_NUM, SHIP_DATE, PRODUCT, ORD_TONS)
    SELECT '001', '3/1/2006', 'ABC', 4 UNION ALL
    SELECT '002', '3/4/2006', 'ABC', 2 UNION ALL
    SELECT '002', '3/4/2006', 'DEF', 6 UNION ALL
    SELECT '003', '3/7/2006', 'DEF', 8
    
    CREATE TABLE PROD_INVENTORY (
    PRODUCT VARCHAR(10),
    INV_TONS REAL)
    
    INSERT INTO PROD_INVENTORY (PRODUCT, INV_TONS)
    SELECT 'ABC', 5 UNION ALL
    SELECT 'DEF', 13
    The final recordset needs to be something like:
    Code:
    PRODUCT     ORDER_NUM     ORD_TONS    SFI    SFP   END_INV
    ABC               001   4               4       0        1
    ABC               002   2               1       1        0 
    DEF               002   6               6       0        7
    DEF               003   8               7       1        0
    SFI = Sales from Inventory
    SFP = Sales from production

    I need a little help in how to do a running inventory balance (END_INV)
    for each item. Once I have that, then I can calculate SFI and SFP.
    I could figure out how to do it with a cursor, but it
    would probably be pretty slow. I'll have about 10,000 records to sort thru,
    and of course there will be more columns than what I show here.

    Any ideas would be appreciated.
    Last edited by RedNeckGeek; 03-05-06 at 07:27.
    Inspiration Through Fermentation

  2. #2
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106

    I'm afraid I'm not following you...

    Is the inventory balance (END_INV) calculated for every row?

    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Yes. For each product, I would get the begining inventory from prod_inventory, and decrease it for each order until I ran out. I don't
    really need the end_inv column in my result set, I was just trying to
    show an example of what I was trying to do.
    Inspiration Through Fermentation

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    When you want to keep running totals then using cursors is the better option

    What did your query look like for the first part for which you say
    It would have been easy, if he hadn't asked for the second part
    I assume somethig like:
    Code:
    SELECT P.PRODUCT, "already_in_iventory"=P.INV_TONS
    , "needs_to_be_produced"=CASE 
    WHEN SUM(O.ORD_TONS)-P.INV_TONS > 0 THEN SUM(O.ORD_TONS)-P.INV_TONS
    ELSE 0
    END
    FROM PROD_INVENTORY P, ORDER_ITEM O 
    WHERE O.PRODUCT=P.PRODUCT
    GROUP BY P.PRODUCT, P.INV_TONS
    ORDER BY P.PRODUCT
    
    PRODUCT    already_in_iventory      needs_to_be_produced
    ---------- ------------------------ ---------------------- 
    ABC        5.0                      1.0
    DEF        13.0                     1.0
    To add the date in your where clause you can run somethig like this
    Code:
    SELECT P.PRODUCT, "already_in_iventory"=case 
    when P.INV_TONS-SUM(O2.ORD_TONS) > 0 then P.INV_TONS-SUM(O2.ORD_TONS)
    else 0
    end
    , "needs_to_be_produced"=CASE 
    WHEN sum(O.ORD_TONS)-(P.INV_TONS-SUM(O2.ORD_TONS)) > 0 THEN sum(O.ORD_TONS)-(P.INV_TONS-SUM(O2.ORD_TONS))
    ELSE 0
    END
    FROM PROD_INVENTORY P, ORDER_ITEM O, ORDER_ITEM O2
    WHERE O.PRODUCT=P.PRODUCT
      AND O.SHIP_DATE='3/7/2006'
      and O2.PRODUCT=P.PRODUCT
      AND O2.SHIP_DATE<'3/7/2006'
    GROUP BY P.PRODUCT, P.INV_TONS
    ORDER BY P.PRODUCT
    
    PRODUCT    already_in_iventory needs_to_be_produced
    ---------- ------------------- -----------------------
    DEF        7.0                 1.0
    And to produce the set as in your sample output e.g.:
    Code:
    SELECT O.PRODUCT, O.ORDER_NUM, 'ORD_TONS'=max(O.ORD_TONS)
    ,SFI=case when P.INV_TONS-SUM(O2.ORD_TONS)>=0 then max(O.ORD_TONS)
    else max(O.ORD_TONS)+(P.INV_TONS-SUM(O2.ORD_TONS))
    end
    ,SFP=case when P.INV_TONS-SUM(O2.ORD_TONS)>=0 then 0
    else SUM(O2.ORD_TONS)-P.INV_TONS
    end
    FROM PROD_INVENTORY P, ORDER_ITEM O, ORDER_ITEM O2
    WHERE O.PRODUCT=P.PRODUCT
      AND O2.PRODUCT=P.PRODUCT
      AND O.ORDER_NUM>=O2.ORDER_NUM
    GROUP BY O.PRODUCT, O.ORDER_NUM, P.INV_TONS
    ORDER BY O.PRODUCT, O.ORDER_NUM
    
    PRODUCT    ORDER_NUM  ORD_TONS  SFI  SFP  
    ---------- ---------- --------- ---- ---- 
    ABC        001        4.0       4.0  0
    ABC        002        2.0       1.0  1.0
    DEF        002        6.0       6.0  0.0
    DEF        003        8.0       7.0  1.0
    Last edited by pdreyer; 03-07-06 at 02:52.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Thanks, pdreyer

    Some new ideas for me to toy with. Exactly what I was hoping to get.
    I'll try it out later this week.
    Inspiration Through Fermentation

Posting Permissions

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