Click here to Skip to main content
12,820,914 members (32,348 online)
Rate this:
Please Sign up or sign in to vote.
See more: VB VB.NET
Hi Sir,

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

Here is my table design:



Posted 16-Dec-12 21:26pm
Updated 16-Dec-12 21:31pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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!
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?
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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
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)

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170308.1 | Last Updated 17 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100