The following SQL statement adds the products with the current stock value into the table.
INSERT INTO dbo.Stock SELECT ItemID, SUM(Quantity) AS TotalQuanity
FROM (SELECT ItemID, Quantity
FROM dbo.Purchase
UNION
SELECT ItemID, -Quantity
FROM dbo.PurchaseReturn
UNION
SELECT ItemID, -Quantity
FROM dbo.Sale
UNION
SELECT ItemID, Quantity
FROM dbo.SaleReturn) AS AllTransactions
GROUP BY ItemID
This assumes that the
Quantity
values in all the tables are positive.
First the four tables are put together using an
UNION[
^]. During this process the purchase returns and sales are made negative, as they will cause the stock to go down.
Next we perform a
SUM[
^] for each
ItemID
on the
UNION
results.
Finally the results of the
SUM
are inserted into the
Stock
table using the
INSERT[
^] statement.