如何从库存表中减去库存 [英] how to minus stock from inventory tables

问题描述

我有销售订单表和产品库存表

库存包含这些字段



InventoryId，

ProductId，

数量，

LocationId，

Rate，

QuantityInHand，

ReceivedDate



而销售订单列是这些我也有在Datagridview



ProductId

价格

数量

折扣

小计



现在，我想检查订单数量到我的库存中，以便我知道

所需数量是否可用

i我正在使用此查询以确保所需数量是否可用

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

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

这将确保我的订单数量是可用的，但现在问题是我想从MINH产品quanityInHand库存旧的Inventtory收到日期我该怎么办？

什么是更新查询我需要??

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

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

记住利率也会变成差异库存

remember rate also become change of differet inventory

推荐答案

以下是我对问题的理解：



例如，手中有10个数量ProductId = 1



InventoryId | ProductId | QuantityInHand

1 | 1 | 10



每当有新库存进入时，都会在PRODUCT_INVENTORY表中生成一个新条目。例如，另外15个数量已经进入，因此表格如下所示：



InventoryId | ProductId | QuantityInHand

1 | 1 | 10

2 | 1 | 15



现在有10 + 15 = 25库存数量。我们假设客户订购了20件ProductId = 1。由于库存数量超过订购数量，因此可以出售商品。



现在的问题是如何我们应该从表​​中调整已售物品。



选项1 - 我们应该从第一条记录中扣除10条，从第二条记录中扣除10条吗？

OR

选项2 - 我们应该从第二条记录中扣除15条，从第一条记录中扣除5条吗？



其实股票以FIFO或LIFO方式维持。先进先出（FIFO）方法说你应该清除先到先后的库存。 LIFO就在FIFO的对面。



现在它不是一个非常简单的SQL，它可以根据方法更新库存（即FIFO / LIFO）你选择。





如果您能确认我对问题的理解是正确的，那么我可以尝试一下。



- DD

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

您好朋友，您可以尝试使用SQl Server编写的这个STORED PROC：

Hello friend, you may try this STORED PROC written in SQl Server:

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

注意：以上示例仅用于演示目的。在任何现实生活中使用之前，请确保正确实现异常处理，事务处理。

如果R.State = adStateOpen则R.Close

R.打开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（您的数据保存成功）

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

这篇关于如何从库存表中减去库存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！