Handling matieral consumption - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Handling matieral consumption

Author

Topic

priestlm
Starting Member

13 Posts

Posted - 2010-09-29 : 08:43:18

Hey guys, im a bit stuck on this 1 & wonder if anyone can help:

i have a select query which selects the bill of materials associated with an order, i now want to reduce the stock level of the compoents in question. Table1 = the materials i am consuming, Table2 = the stock levels & the expected results is Table1 - Table. Any help would be greatfully recieved.

Thanks
Mark

Table1_BOM
Compoent - QTY
A - 10
B - 7
C - 9

Table2_Stock
Compoent - QTY
A - 1000
B - 1000
C - 1000

Expected results:
Table2_Stock
Compoent - QTY
A - 990
B - 993
C - 991

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 09:07:56

Update Table2_Stock set Table2_Stock.Qty=Table2_Stock.Qty-Table1_BOM.Qty
from Table2_Stock inner join Table1_BOM on Table1_BOM.Component=Table2_Stock.Component.

PBUH

priestlm
Starting Member

13 Posts

Posted - 2010-09-29 : 09:45:38

Thank u PBUH - it worked perfectly, here is my final solution:

UPDATE Production_Component_Stock
set Production_Component_Stock.STOCK = Production_Component_Stock.STOCK - tblBOM.Allowcate
From Production_Component_Stock inner join
(SELECT Production_Request_BOM.Component,
Production_Requests.Qty_Ord * Production_Request_BOM.Qty AS Allowcate,
FROM Production_Requests INNER JOIN
Production_Request_BOM ON Production_Requests.Item LIKE Production_Request_BOM.Item INNER JOIN
Production_Component_Stock ON Production_Request_BOM.Component like Production_Component_Stock.Component
WHERE (Production_Requests.WO_ID = 5879433)) as tblBOM on tblBOM.Component = Production_Component_Stock.Component

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources