库存库存的FIFO

Question

我有一个查询，它返回数据库中所有可用的库存。我需要制作一个存储过程来获取用户输入的特定项目的itemCode、batchNo、数量、价格。

----------------------------------------------
| id | itemCode | batchNo | availQty | price |
----------------------------------------------
| 1  | item_1   | 07292016|   5      |  5.50 |
| 2  | item_1   | 07312016|   10     |  5.50 |
| 3  | item_1   | 08012016|   2      |  6.00 |

我的问题是，如果用户输入了6个要购买的数量，我如何才能得到前2行的结果，得到的总数量为6？

其结果必须是：

07292016  --- 5
07312016  --- 1

Answer 1

我怀疑这是否有效，查询也很难执行，但以下内容将为您提供您想要的内容：

-- Create Test Data
create table #Items
(
    id int not null primary key,
    itemCode varchar(30) not null,
    batchNumber varchar(30) not null,
    availQty int not null,
    price smallmoney not null
);

insert into #Items
values
    (1, 'item_1', '07292016', 5, 5.50),
    (2, 'item_1', '07312016', 10, 5.50),
    (3, 'item_1', '08012016', 2, 6.00)

select 
    *
from
    #Items
;

-- Set up required parameters
declare 
    @requiredItemCode varchar(30) = 'item_1',
    @requiredQty int = 6

-- The query to get the required result
select
    i.*,
    case
        when
            @requiredQty - 
            isnull(
                (
                    select
                        sum(availQty)
                    from
                        #Items i2
                    where
                        i2.itemCode = i.itemCode
                        and i2.id < i.Id
                ),
                0) < i.availQty
        then
            @requiredQty - 
            isnull(
                (
                    select
                        sum(availQty)
                    from
                        #Items i2
                    where
                        i2.itemCode = i.itemCode
                        and i2.id < i.Id
                ),
                0)
        else
            i.availQty
    end as qtyToTake
from 
    #Items i
where   
    i.ItemCode = @requiredItemCode
    and 
    case
        when
            @requiredQty - 
            isnull(
                (
                    select
                        sum(availQty)
                    from
                        #Items i2
                    where
                        i2.itemCode = i.itemCode
                        and i2.id < i.Id
                ),
                0) < i.availQty
        then
            @requiredQty - 
            isnull(
                (
                    select
                        sum(availQty)
                    from
                        #Items i2
                    where
                        i2.itemCode = i.itemCode
                        and i2.id < i.Id
                ),
                0)
        else
            i.availQty
    end > 0

-- Clean up test data
drop table #Items

输出：

id          itemCode batchNumber availQty    price
----------- -------- ----------- ----------- ---------------------
1           item_1   07292016    5           5.50
2           item_1   07312016    10          5.50
3           item_1   08012016    2           6.00

(3 row(s) affected)

id          itemCode batchNumber availQty    price                 qtyToTake
----------- -------- ----------- ----------- --------------------- -----------
1           item_1   07292016    5           5.50                  5
2           item_1   07312016    10          5.50                  1

(2 row(s) affected)

Answer 2

尝尝这个

DECLARE @Tbl TABLE (id INT, itemCode NVARCHAR(50), batchNo NVARCHAR(50), availQty INT, price DECIMAL(5,2))

INSERT INTO @Tbl VALUES        
(1, 'item_1'   , '07292016',   5      ,  5.50 ),
( 2  , 'item_1',    '07312016'   ,10,  5.50),
( 3  , 'item_1',    '08012016'   ,2 ,  6.00 )


DECLARE @Quantity INT = 6

SELECT * 
FROM
    @Tbl
WHERE
    id <= (
            SELECT TOP 1
                  A.id
            FROM
            (
                SELECT
                    id ,
                    itemCode ,
                    batchNo ,
                    availQty ,
                    price,
                    (SELECT SUM(availQty) FROM @Tbl IT WHERE IT.id <= T.id)  AS TotalAmount
                FROM
                    @Tbl T
            ) A
            WHERE
                A.TotalAmount >= @Quantity
            ORDER BY A.id
          )

输出：

id  itemCode    batchNo     availQty    price
1   item_1      07292016    5           5.50
2   item_1      07312016    10          5.50

已更新

SELECT
    R.id ,
    R.itemCode ,
    R.batchNo ,
    R.availQty ,
    R.price ,
    CASE WHEN R.MaxId = id THEN R.availQty - (TotalQuantity - @Quantity)
        ELSE R.availQty END OutQuantity
FROM
(
    SELECT 
        id ,
        itemCode ,
        batchNo ,
        availQty ,
        price,
        MAX(id) OVER (ORDER BY (SELECT NULL)) MaxId,
        SUM(availQty) OVER (ORDER BY (SELECT NULL)) TotalQuantity
    FROM
        @Tbl
    WHERE
        id <= (
                SELECT TOP 1
                      A.id
                FROM
                (
                    SELECT
                        id ,
                        itemCode ,
                        batchNo ,
                        availQty ,
                        price,
                        (SELECT SUM(availQty) FROM @Tbl IT WHERE IT.id <= T.id)  AS TotalAmount
                    FROM
                        @Tbl T
                ) A
                WHERE
                    A.TotalAmount >= @Quantity
                ORDER BY A.id
              )
) R
WHERE 
  @Quantity > 0

输出

id  itemCode    batchNo     availQty    price   OutQuantity
1   item_1      07292016    5           5.50    5
2   item_1      07312016    10          5.50    1

Answer 3

我还没有测试过，但是如果你有任何问题的话，你可以在这个基础上做一些改变。希望你想要这个

  CREATE OR ALTER PROCEDURE dbo.getStock @item_Code nvarchar(30), @quantity  int
    AS 
    //Declare variables
    DECLARE @id INT
    DECLARE @itemCode nvarchar(30)
    DECLARE @batchNo nvarchar(30)
    DECLARE @qty INT
    DECLARE @cumulativeQty INT
    DECLARE @price DECIMAL(18,2)

    //Declare cursor
    DECLARE @getStock CURSOR
    SET @getStock = CURSOR FOR
    SELECT Id, ItemCode, batchNo, availQty, price
    FROM your_table
    WHERE ItemCode = @item_Code
    ORDER BY  batchNo ASC

    //Create temp table to store all of the intermediate stock records into it

    //If always make sure if its already exist just drop and recreate



IF OBJECT_ID('tempdb..#TmpStock') IS NOT NULL 
    BEGIN
        DROP TABLE #TmpStock
    END
    ELSE
    BEGIN
        CREATE TABLE #TmpStock(Id INT, ItemCode nvarchar(30), BatchNo nvarchar(30), Quatnity INT, Price Decimal(18,2))
    END


    //Open the cursor
    OPEN @getStudents 

    //Read the next row record into the cursor
    FETCH NEXT 
    FROM @getStock INTO @id, @itmCode, @batchNo, @qty, @price


    SET @cumulativeQty = 0

    //Loop through one row by another which is selected above and stored in cursor

    WHILE @@FETCH_STATUS = 0
    BEGIN

    //If the cumulative quantity is less than user's input quantity 
    //then keep inserting into the temp table and deduct the inserted qty
    SET @cumulativeQty  = @cumulativeQty + @qty

    IF( @quantity <= @cumulativeQty)
        INSERT INTO #TmpStock VALUES(@id, @itmCode, @batchNo, @quantity, @price );
    ELSE
        INSERT INTO #TmpStock VALUES(@id, @itmCode, @batchNo, @qty, @price );
    @cumulativeQty= @quantity - @qty
    END

    FETCH NEXT
    FROM @getStock INTO @id, @itmCode, @batchNo, @qty, @price
    END


    //Closing the cursor
    CLOSE @getStock
    DEALLOCATE @getStock

//Now select your data from temp table
SELECT * FROM #TmpStock