Click here to Skip to main content
15,749,281 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Sir,

How to create a query for remaining stocks of a product?

Here is my table design:



Updated 16-Dec-12 20:31pm

From what I can see, you can't.

There is no obvious column in there to hold the stock level, without which you cannot estimate how many you had to start with, much less how many you have left!
Share this answer
Ashok19r91d 17-Dec-12 2:44am    
Yeah! we can calculate Closing Stock value using data available in Delivery and Order Tables (DeliveryQty and OrderQty)
OriginalGriff 17-Dec-12 3:03am    
Since when?
If you have 100 items in stock when you start the system, you can immediately sell 100 units. If you have none, you can't sell any.
Add a stock level to your Product table and keep it updated. Don't forget that things can leave or enter the stock the stock without orders, or deliveries. Theft is one example, returns, warantee exchange, defective on delivery, etc. are others.
Ashok19r91d 17-Dec-12 3:11am    
Mumm may be, But till the data structure enclosed here, we can ignore Theft, Wastage, Expired Stock, Purchase and Sales Return...
OriginalGriff 17-Dec-12 3:18am    
Why? Why not encourage him to do it the proper way?
Otherwise in two years time when it takes SQL five minutes per item to work out if there are any that should be in stock it is a much, much harder problem to solve before the company goes bust because customers don't wait ten minutes for the web site to download and go elsewhere?
Ashok19r91d 17-Dec-12 4:03am    
Oops, To the best of My Knowledge and Experience, There's much more Problem'll occur definitely If You store Closing Value in DB...
For Example
I've Purchase Good 1 with Product ID 001 in a Bill of 20 stock Next day
I'm Selling the Good 001 to 8 Qty, So Now Closing is 12, With your Idea? Where you recommend to Save this Value 12?
Just Write 2 Queries
"Select Sum(DeliveryQty) From DeliveryDetails Where ProdID = 1"
"Select Sum(OrderQty) From OrderDetails Where ProdID = 1"

These 2 queries return 2 Values so now simple maths
StockRemaining = OrderQty - DeliveryQty
Share this answer
ianshack 18-Dec-12 20:15pm    
Hi Sir, I obtained the query for remaining products.First step, I made a sql statement and made a view for the total_orders and calling a column from a view to subtract to my total numbers of product delivered.
ianshack 18-Dec-12 20:16pm    
Thank you all for your appreciation.
Ashok19r91d 24-Dec-12 1:54am    
If This Solution helps you in any way then accept this solution...
So Other User having similar question can find answer easily...
Sheikh Muhammad Amaan-Ullah 27-Feb-16 12:46pm    
but how to show this value to a secific report???

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