Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Inventory question

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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 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.
Go to Top of Page

taunt
Posting Yak Master

128 Posts

Posted - 2013-01-08 : 13:05:35
The 2 columns in the stocktemp table are pid and newqty.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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."
Go to Top of Page

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 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
Go to Top of Page

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 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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 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/

Go to Top of Page

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 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."
Go to Top of Page
   

- Advertisement -