Query for Remaining Stocks

Question

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:

DeliveryDetails
Del_detID
DeliveryID
ProdID
DeliveryPrice
DeliveryQty

Product
ProdID
Name
Description
ReOrderLevel

OrderDetails
Order_detID
OrderID
ProdID
OrderPrice
OrderQty

Posted 16-Dec-12 20:26pm

ianshack

Updated 16-Dec-12 20:31pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2012-12-16T20:29:00

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!

Posted 16-Dec-12 20:29pm

OriginalGriff

Comments

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?

Ashok19r91d · Answer 2 · 2012-12-16T21:06:00

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

Posted 16-Dec-12 21:06pm

Ashok19r91d

Comments

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