如何在sql中实现FIFO [英] How to implement FIFO in sql

问题描述

我正在研究 sql 中的 FIFO 实现.我的应用程序中有批号概念.如果假设我在销售库存，那么我的应用程序应该告诉我哪个库存是先到的.让我们.假设我在 4 月 4 日、8 月 5 日和 8 月 5 日购买了库存A"8 月 6 日至 8 月

I working on FIFO implementation in sql. I have Batch number concept in my application. If suppose I am selling on inventory then my application should tell me that which inventory is the first come. Lets. Say I purchased Inventory 'A' on 4th-Aug, 5th-Aug & 6th-Aug

On 4th Aug - A Inventory has batch number   BT002 - 10 (Qty)
On 5th Aug - A's Inventory has batch number BT003 - 15 (Qty)
On 6th Aug - A's Inventory has batch number BT001 - 10 (Qty)

所以，现在我手上有如下库存:

So, Now I am having stock Now in my hand as following :

A Inventory
BT002 - 10 - 4-Aug
BT003 - 15 - 5-Aug
BT001 - 10 - 6-Aug

现在如果我想将该库存出售给任何人，那么我的应用程序应该告诉我我应该出售BT002(批号)库存在先，因为它在先.

Now If I want to sell that Inventory to anybody then my application should tell me that I should sell BT002 (Batch number) inventory first beacause it came first.

这就是我在我的应用程序中使用的概念.

That was the concept I am using in my application.

现在我想从A"(库存)中销售 15 个数量.

Now I want to sell 15 Qty from 'A' (Inventory).

那么O/p应该是这样的:

Then O/p Should be like this :

BT002 - 10
BT003 - 5

这是我的查询:

SELECT ISNULL(SUM(qty),0) AS Qty,batch_no,accept_date  FROM RS_GIN_Master 
GROUP BY batch_no,accept_date
HAVING ISNULL(SUM(qty),0) <= 15
ORDER BY accept_date asc

给定查询的 O/p:

我怎样才能得到这样的 O/P:

How can I get O/P like this :

BT002 - 10
BT003 - 5

任何帮助将不胜感激.先感谢您.

Any Help will be appreciated. Thank you in Advance.

推荐答案

这应该适合您:
Fiddle 上的工作示例

CREATE FUNCTION [dbo].[GetBatchAmounts]
(
    @requestedAmount int

)
RETURNS 
@tBatchResults TABLE 
(   
    Batch nvarchar(50),
    Amount int
)
AS
BEGIN
    /*This is just a mock of ersults of your query*/
    DECLARE @RS_GIN_Master TABLE( 

     Qty int,
     batch_no NVARCHAR(max),
     accept_date DATETIME
    )

    insert into @RS_GIN_Master(Qty,batch_no,accept_date)
    SELECT 10,'BT002', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(4 AS varchar) AS DATETIME)

    insert into @RS_GIN_Master(Qty,batch_no,accept_date)
    SELECT 10,'BT003', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(5 AS varchar) AS DATETIME)

    insert into @RS_GIN_Master(Qty,batch_no,accept_date)
    SELECT 10,'BT001', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(6 AS varchar) AS DATETIME)
    /*---------------------------*/

     DECLARE @Qty int
     DECLARE @batch_no NVARCHAR(max)
     DECLARE @accept_date DATETIME


    DECLARE myCursor CURSOR FOR

    SELECT Qty, batch_no, accept_date FROM @RS_GIN_Master ORDER BY accept_date ASC

    OPEN myCursor

    FETCH NEXT FROM myCursor INTO  @Qty, @batch_no,@accept_date

    WHILE (@@FETCH_STATUS = 0 AND @requestedAmount > 0 ) 
    BEGIN

        Declare @actualQty int
        IF @requestedAmount > @Qty
            SET @actualQty = @Qty
        ELSE    
            SET @actualQty = @requestedAmount


        INSERT INTO @tBatchResults (batch, Amount)
        SELECT @batch_no, @actualQty

        set @requestedAmount  = @requestedAmount - @actualQty

        FETCH NEXT FROM myCursor INTO @Qty, @batch_no,@accept_date

    END /*WHILE*/

    CLOSE myCursor
    DEALLOCATE myCursor

    RETURN
END

只需确保将函数的标记部分替换为您的查询...

Just make sure to replace the marked part of the function with your query...

这篇关于如何在sql中实现FIFO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！