Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a sale order Table and Product inventory table
Inventory contains these fields

InventoryId,
ProductId,
Quantity,
LocationId,
Rate,
QuantityInHand,
ReceivedDate


while Sale Order Columns are these which are also i have in Datagridview

ProductId
Price
Quantity
Discount
Subtotal


Now, i want to check the Order Quanity into my Inventory so that i know that
desired quantity is available or not
i am using this query to ensure that desired quantity available or not

SQL
Select 
sum(QuantityInHand) as TotalQuantityInHand
From tblInventory
Where ProductId = @ProductId
and DeleteStatus is not null
and QuantityInHand is not null
and QuantityInHand >= @QuantityInHand
group by ProductId


This will ensure me that order quantity is available but now problem is that i want to MINU product quanityInHand stock from the old Inventtory received date how can i do this??
what is the update query i required??

SQL
update tblInventory
set
QuantityInHand=@QuantityInHand
where InventoryId = ???? WHAT TO DO HERE


remember rate also become change of differet inventory
Posted
Updated 9-Apr-21 18:32pm
Comments
ZurdoDev 5-Jun-14 7:23am    
Where are you stuck?
Muhamad Faizan Khan 5-Jun-14 7:23am    
want to MINU product quanityInHand stock from the old Inventtory received date how can i do this??
Debabrata_Das 5-Jun-14 10:31am    
Hello friend, I have a query. Can you confirm if there could be multiple records of same ProductId in Product Inventory table?
Muhamad Faizan Khan 6-Jun-14 3:21am    
yes

Here is my understanding of the problem:

For example, there are 10 quantity in hand of ProductId = 1

InventoryId | ProductId | QuantityInHand
1 | 1 | 10

Whenever a new inventory comes in, a new entry is made in PRODUCT_INVENTORY table. For example, another 15 quantity has come in, so the table would look like as follows:

InventoryId | ProductId | QuantityInHand
1 | 1 | 10
2 | 1 | 15

Now, there are 10 + 15 = 25 quantity in stock. Let's assume customer has ordered for 20 items of ProductId = 1. Since we have more quantity in stock than ordered quantity, items can be sold.

Now the question is how we should adjust the Sold Items from the table.

Option 1 - Should we deduct 10 from the first record and 10 from the second record?
OR
Option 2 - Should we deduct 15 from the second record and 5 from the first record?

Actually stocks are maintained in either FIFO or LIFO method. First In First Out (FIFO) method says that you should clear stock which has come first. LIFO is just opposite of FIFO.

Now it would not be a very simple SQL which can update the stock in hand as per the method (i.e. FIFO/LIFO) you select.


If you can confirm my understanding of the problem is correct, then I can give a try.

- DD
 
Share this answer
 
v2
Comments
Muhamad Faizan Khan 6-Jun-14 3:23am    
yes absolutely right Mr Debabrata; excellent. i want FIFO Method. please provide any solution. m waiting
Debabrata_Das 6-Jun-14 4:23am    
Good to hear that. Well, the logic cannot be implemented using a single SQL statement. I would suggest start writing a STORED PROC. If you are stuck anywhere, we are here to help you out.

BTW, since you liked my post, may I request you to provide your rating :p

- DD
Muhamad Faizan Khan 6-Jun-14 8:04am    
rating done
Debabrata_Das 6-Jun-14 9:39am    
Thank you :)

Please check Solution 2 where I've written a Stored Proc. Hope it will be helpful.
Debabrata_Das 20-Jun-14 2:29am    
Muhamad, could you please accept this solution as answer?
Hello friend, you may try this STORED PROC written in SQl Server:


SQL
CREATE PROCEDURE UpdateStock 
	-- Add the parameters for the stored procedure here
	@ProductID int, 
	@OrderQuantity int
AS
BEGIN
	SET NOCOUNT ON;
	
	-- Fetch total stock in hand
	DECLARE @TotalStock INT
	SET @TotalStock = (Select SUM(Quantity) from PRODUCT_INVENTORY where ProductID = @ProductID)
	
	-- Check if the available stock is less than ordered quantity
	IF @TotalStock < @OrderQuantity
	BEGIN
		PRINT 'Stock not available'
		RETURN -1
	END
	
	DECLARE @InventoryID INT
	DECLARE @QuantityInHand INT
	-- Declare a CURSOR to hold ID, Quantity
	DECLARE @GetInventoryID CURSOR
 
	SET @GetInventoryID = CURSOR FOR
	SELECT ID, Quantity
	FROM PRODUCT_INVENTORY
	WHERE ProductID = @ProductID
	ORDER BY ReceivedDate
 
	-- Open the CURSOR
	OPEN @GetInventoryID

	-- Fetch record from the CURSOR
	FETCH NEXT
	FROM @GetInventoryID INTO @InventoryID, @QuantityInHand

	-- Loop if record found in CURSOR
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Check if Order quantity becomes 0
		IF @OrderQuantity = 0
		BEGIN
			PRINT 'Updated Successfully'
			RETURN 1
		END
		-- If Order Qty is less than or equal to Quantity In Hand
		IF @OrderQuantity <= @QuantityInHand 
		BEGIN
			UPDATE PRODUCT_INVENTORY
			SET Quantity = Quantity - @OrderQuantity
			WHERE ID = @InventoryID
			
			SET @OrderQuantity = 0
		END
		-- If Order Qty is greater than Quantity In Hand
		ELSE
		BEGIN
			UPDATE PRODUCT_INVENTORY
			SET Quantity = 0
			WHERE ID = @InventoryID

			SET @OrderQuantity = @OrderQuantity - @QuantityInHand
		END
		
		FETCH NEXT
		FROM @GetInventoryID INTO @InventoryID, @QuantityInHand
	END
		
	-- Close and  Deallocate CURSOR
	CLOSE @GetInventoryID
	DEALLOCATE @GetInventoryID
	
END


Note: The above example is only for demo purpose. Before you use in any real life program, make sure that you implement Exception handling, Transaction properly.
 
Share this answer
 
Comments
Member 13923767 5-Aug-18 12:30pm    
where i have to write this proc , in the database or in c#?
Debabrata_Das 31-Aug-18 7:52am    
It's a Stored Procedure which should be written in the DB. Hope this helps.
If R.State = adStateOpen Then R.Close
R.Open "select * from Item where code='" & Form39.Text3.Text & "'", v, adOpenStatic, adLockOptimistic

R!qty = R.Fields("qty").Value - Val(Text41.Text)
R.UpdateBatch adAffectAllChapters
R.Close
MsgBox ("YOUR DATA SAVE SUCCESSFULLY")
 
Share this answer
 
v2
Comments
Dave Kreskowiak 26-Dec-16 21:16pm    
You had to resurrect this question after it was answered two and half years ago? Don't do that again.

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