SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Inventory question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

taunt
Posting Yak Master

111 Posts

Posted - 01/07/2013 :  14:53:22  Show Profile  Reply with Quote
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
52309 Posts

Posted - 01/07/2013 :  23:24:07  Show Profile  Reply with Quote
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

111 Posts

Posted - 01/08/2013 :  12:04:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  12:21:28  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

111 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/08/2013 :  23:29:46  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 01/09/2013 :  21:51:15  Show Profile  Reply with Quote
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

111 Posts

Posted - 01/10/2013 :  13:43:20  Show Profile  Reply with Quote
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

111 Posts

Posted - 01/10/2013 :  15:10:20  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/10/2013 :  22:40:11  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 01/11/2013 :  15:38:48  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.64 seconds. Powered By: Snitz Forums 2000