Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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
Edited 16-Dec-12 20:31pm
v2
Rate this: bad
good
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!
  Permalink  
Comments
Ashok19r91d at 17-Dec-12 2:44am
   
Yeah! we can calculate Closing Stock value using data available in Delivery and Order Tables (DeliveryQty and OrderQty)
OriginalGriff at 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 at 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 at 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 at 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
good
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
  Permalink  
Comments
ianshack at 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 at 18-Dec-12 20:16pm
   
Thank you all for your appreciation.
Ashok19r91d at 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...

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 359
1 Richard MacCutchan 320
2 OriginalGriff 240
3 Jared Sanow 145
4 Snehasish_Nandy 115
0 Sergey Alexandrovich Kryukov 5,969
1 OriginalGriff 5,005
2 CPallini 2,473
3 Richard MacCutchan 1,547
4 Abhinav S 1,507


Advertise | Privacy | Mobile
Web04 | 2.8.140814.1 | Last Updated 17 Dec 2012
Copyright © CodeProject, 1999-2014
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