Click here to Skip to main content
15,946,342 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
i have two tables, one is stock in and other is stock out table. stock balance from query, but stock balance shows minus figure and stock out accept the transaction.

Stock in/stock out/Stock Balance

What I have tried:

SELECT tblRecvB.MCode, tblRecvB.GroupName, tblRecvB.MatName, tblRecvB.UOM, Sum(tblRecvB.Qty) AS Quantity, tblRecvB.Price
FROM tblRecvB
GROUP BY tblRecvB.MCode, tblRecvB.GroupName, tblRecvB.MatName, tblRecvB.UOM, tblRecvB.Price
UNION SELECT tblIssueB.MCode, tblIssueB.GroupName, tblIssueB.MaterialName, tblIssueB.UOM,Sum(-[Qty]) AS Quantity,  tblIssueB.Price
FROM tblIssueB
GROUP BY tblIssueB.MCode, tblIssueB.GroupName, tblIssueB.MaterialName, tblIssueB.UOM, tblIssueB.Price;
Updated 2-Nov-22 22:29pm
Richard Deeming 3-Nov-22 4:49am    
Nobody can tell you how to "correct" your stock balances, since the data you've shown is already correct given the description provided. If you have 20 in stock, and you remove 40, then your balance is correctly -20.

You either need to fix your code so that you can't take out more stock than is available, or explain precisely what "correction" you want to apply.
Md Jahangir Hossen 2022 3-Nov-22 8:49am    
Thank you Richard Deeming, I need to fix my code not to take out more stock than is available. So, pls let me help provide me the code with example.

1 solution

That's correct: you started with 120 items, you sold 100 leaving 20. You then removed another 40 items, which meant that you had a negative stock level.

This happens in real life: a large order cannot be filled immediately, but the order gets processed and the remaining balance will be shipped on arrival. The negative stoick level means that that number of items from the next incoming delivery are already allocated (so you many need to change the order on your supplier prior to dispatch).

Remember, the names you give columns in a DB mean nothing to the database: "Stock in", "Stock out", and "Stock balance" do not automatically cause SQL to take any particular actions - if you want to prevent a negative stock level that is up to your code to monitor and reject the order - but that's Business Rules and hence BL code not DL code.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900