Want to populate Stock on the base of purchase,purchasereturn,sale,salereturn

First thing you have to required all the Items which is purchase or sales table insert with stock zero into stock table.

now when you are purchase at that time you are insert record into the purchase table at that time you have to add purchase qty into current stock qty of that item and update stock table.

at the time of the purchase return , you have to deduct that qty from the current stock of that item and update that value into the stock table.

when you are sales at that time you have to deduct that qty from the current stock of that item and update that value into the stock table.

and at the time of the sales return, you have to add that qty from the current stock of that item and update that value into the stock table.

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.