| Author |
 Topic  |
|
|
taunt
Yak Posting Veteran
58 Posts |
 Posted - 01/07/2013 : 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
India
47035 Posts |
Posted - 01/07/2013 : 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 MVP
http://visakhm.blogspot.com/
|
 |
|
|
taunt
Yak Posting Veteran
58 Posts |
Posted - 01/08/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/08/2013 : 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 just
select pid, sum(newqty)
from inventory
group 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
Yak Posting Veteran
58 Posts |
Posted - 01/08/2013 : 13:05:35
|
| The 2 columns in the stocktemp table are pid and newqty. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47035 Posts |
Posted - 01/08/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/09/2013 : 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
Yak Posting Veteran
58 Posts |
Posted - 01/10/2013 : 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 just
select pid, sum(newqty)
from inventory
group 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 xtemp
SET pid = PO.pid, Totalqty = PO.total
FROM 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
Yak Posting Veteran
58 Posts |
Posted - 01/10/2013 : 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 pid
then
UPDATE Products
SET StockQty = test.total
FROM test INNER JOIN
Products ON test.ProductsID = Products.ProductsID
That worked for me. Is there a better way to do it? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47035 Posts |
Posted - 01/10/2013 : 22:40:11
|
no need of view
you could simply use
UPDATE p
SET StockQty = po.total
FROM Products p
INNER JOIN (
SELECT pid, SUM(Quantity) AS total
FROM PO
WHERE (ponum = '19159') OR (ponum = '19158')
GROUP BY pid
)po
ON po.pid = p.ProductsID
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/11/2013 : 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 just
select pid, sum(newqty)
from inventory
group 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 xtemp
SET pid = PO.pid, Totalqty = PO.total
FROM 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." |
|
|
| |
Topic |
|
Inventory question
