Author |
Topic |
taunt
Posting Yak Master
128 Posts |
Posted - 2013-01-07 : 14:53:22
|
Hello, I'm trying to figure our what would the best way be to update our products table from inventory that we did from PO's. I am wanting to add up the newqty column from each PO. So my temp table would be stocktemp and have two columns a pid column and newqty column. I would need it to go thought all the PO's that are greater than 50000 and add the newqty by pid and put the total in stocktemp. What would be the best way to do this?Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-07 : 23:24:07
|
you can wrap this logic in the insert procedure for PO. Other way to do this is via a trigger created on PO table to do automatic updation of qty in stock table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2013-01-08 : 12:04:47
|
But what would be the best way to add everything up. For instance one PO would have pid6445 qty of 5 and another would have pid6445 qty of 7. Didn't know what would be the easiest way to add items up by the pid. The temp table would need to read after its done pid6445 qty 12. Didn't know if a loop query would be the best way to add everything up or something else. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-08 : 12:21:28
|
Do you want to add a new column to the products table or to cteate a stocktemp table?Is your stocktemp table justselect pid, sum(newqty)from inventorygroup by pid==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2013-01-08 : 13:05:35
|
The 2 columns in the stocktemp table are pid and newqty. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-08 : 23:29:46
|
quote: Originally posted by taunt The 2 columns in the stocktemp table are pid and newqty.
just aggregate on pid and apply SUM over qty as Nigel showed. put in any additional filters you require in WHERE like date range, line status etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-09 : 21:51:15
|
quote: Originally posted by taunt Hello, I'm trying to figure our what would the best way be to update our products table from inventory that we did from PO's. I am wanting to add up the newqty column from each PO. So my temp table would be stocktemp and have two columns a pid column and newqty column. I would need it to go thought all the PO's that are greater than 50000 and add the newqty by pid and put the total in stocktemp. What would be the best way to do this?Thanks
Are you asking for a "running total" like you might find in a checkbook?--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2013-01-10 : 13:43:20
|
quote: Originally posted by nigelrivett Do you want to add a new column to the products table or to cteate a stocktemp table?Is your stocktemp table justselect pid, sum(newqty)from inventorygroup by pid==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Yep , this is what I'm looking for. How would I go updaing a table by a query? This is what I tried:UPDATE xtempSET pid = PO.pid, Totalqty = PO.totalFROM PO WHERE (SELECT pid, SUM(Quantity) AS total FROM PO WHERE (ponum = '19159') OR (ponum = '19158') GROUP BY pid) and that gives me an error. What would be the correct way to have it update xtemp with the pid and SUM(Quantity) from PO?Thanks |
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2013-01-10 : 15:10:20
|
OK I kinda got it. What I did was create a view:CREATE VIEW test AS SELECT pid, SUM(Quantity) AS total FROM PO WHERE (ponum = '19159') OR (ponum = '19158') GROUP BY pidthen UPDATE ProductsSET StockQty = test.totalFROM test INNER JOIN Products ON test.ProductsID = Products.ProductsIDThat worked for me. Is there a better way to do it? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-10 : 22:40:11
|
no need of viewyou could simply useUPDATE pSET StockQty = po.totalFROM Products pINNER JOIN (SELECT pid, SUM(Quantity) AS total FROM PO WHERE (ponum = '19159') OR (ponum = '19158') GROUP BY pid)poON po.pid = p.ProductsID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-11 : 15:38:48
|
quote: Originally posted by taunt
quote: Originally posted by nigelrivett Do you want to add a new column to the products table or to cteate a stocktemp table?Is your stocktemp table justselect pid, sum(newqty)from inventorygroup by pid==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Yep , this is what I'm looking for. How would I go updaing a table by a query? This is what I tried:UPDATE xtempSET pid = PO.pid, Totalqty = PO.totalFROM PO WHERE (SELECT pid, SUM(Quantity) AS total FROM PO WHERE (ponum = '19159') OR (ponum = '19158') GROUP BY pid) and that gives me an error. What would be the correct way to have it update xtemp with the pid and SUM(Quantity) from PO?Thanks
If you get an error on something, always post the exact error you're getting or we're just guessing.Also, that doesn't look like a running total. It would appear that Visakh has the correct answer for you though.Do you understand why his answer works?--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
|