Results 1 to 5 of 5
Thread: Trying to NOT use a cursor...

030306, 17:39 #1Village Idiot
 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
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
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; 030506 at 07:27.
Inspiration Through Fermentation

030406, 13:40 #2Registered User
 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?
KilkaThere is nothing more helpless and irresponsible than a man in the depths of an ether binge. HST

030506, 07:27 #3Village Idiot
 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

030706, 02:42 #4Registered User
 Join Date
 May 2005
 Location
 South Africa
 Posts
 1,365
Provided Answers: 1When 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 sayIt would have been easy, if he hadn't asked for the second part
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
Code:SELECT P.PRODUCT, "already_in_iventory"=case when P.INV_TONSSUM(O2.ORD_TONS) > 0 then P.INV_TONSSUM(O2.ORD_TONS) else 0 end , "needs_to_be_produced"=CASE WHEN sum(O.ORD_TONS)(P.INV_TONSSUM(O2.ORD_TONS)) > 0 THEN sum(O.ORD_TONS)(P.INV_TONSSUM(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
Code:SELECT O.PRODUCT, O.ORDER_NUM, 'ORD_TONS'=max(O.ORD_TONS) ,SFI=case when P.INV_TONSSUM(O2.ORD_TONS)>=0 then max(O.ORD_TONS) else max(O.ORD_TONS)+(P.INV_TONSSUM(O2.ORD_TONS)) end ,SFP=case when P.INV_TONSSUM(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; 030706 at 02:52.

030706, 14:42 #5Village Idiot
 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