SQL Server - 如何解決 OHB 的 FIFO 成本…函式、游標？

SQL Server 2008R2 - 需要解決現有項目的 FIFO 成本。為此，我需要將最近收據數量的成本添加到目前手頭餘額 (OHB)。

所以可以說我有項目：b1234 OHB：5

Receipts:
RctNumb | Item  | QTYRct | RctCostPerItem | DATERECD
rct05   | b1234 |      1 |           1.00 | 20160822
rct04   | b1234 |      3 |           2.00 | 20160820
rct03   | b1234 |      3 |           1.25 | 20160819
rct02   | b1234 |      2 |           2.50 | 20160818
rct01   | b1234 |      8 |           2.00 | 20160810

在這裡我需要： (11.00)+(32.00)+(1*1.25) = CostofOHB = 8.25

我不知道如何用 SQL 解決這個問題。

Kathi Kellenberger 在 2010 年對 Redgate 的 FIFO 成本進行了比較。我使用獲勝輸入法來計算我們墨西哥業務的每月庫存數量和價值。如果此連結不起作用，您可以搜尋 Set based speed PHreakery。https://www.red-gate.com/simple-talk/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

由於其他來源的提示，我想出了這個。

累計

嵌套游標-w-錯誤

與 2012 年或以後的 over(order by) 選項相比，它非常慢。

DECLARE @FiFo TABLE
(
RctNumb | Item  | QTYRct | RctCostPerItem | DATERECD
   OHBFIFOCOST     decimal(19,5),
   OHB     decimal(19,5),
   SumRctInvcd decimal(19,5),
   QtyToCost       decimal(19,5),
   ITEM        nvarchar(65) PRIMARY KEY,
   ReportDate  [datetime2](0) 

);
declare @ITEM as char(31)
declare @OHB      as decimal(19,5)


declare @outer_cursor cursor
declare @inner_cursor cursor

declare    @RctNumb        nvarchar(32)
declare    @RCTITEM    as nvarchar(65) 
declare    @QTYRct     decimal(19,5)
declare    @SumRctInvcd    decimal(19,5)
declare    @QtyToCost      decimal(19,5)
declare    @RctCostPerItem     decimal(19,5)
declare    @OHBCOST        decimal(19,5)
declare    @OHBrem         decimal(19,5)
declare    @rowIndex       int
declare    @DATERECD       datetime


declare @fetch_outer_cursor int
declare @fetch_inner_cursor int

set @outer_cursor  = cursor static local for 

Select 
   ITEM
   ,OHB
   from ItemMaster
   where OHB > 0 

 /*loop through top level cursor*/
 open @outer_cursor 
 fetch next from @outer_cursor into @ITEM, @OHB 
 select @fetch_outer_cursor = @@FETCH_STATUS

 while @fetch_outer_cursor = 0
 begin
    /*loop through second level cursor*/
       set @inner_cursor  = cursor static local for 
       select 
           RctNumb
           ,QTYRct
           ,RctCostPerItem
           ,ITEM
           ,DATERECD
           ,rowIndex
       from RctTbl 
       Where ITEM = @ITEM
       Order By rowIndex desc

    open @inner_cursor
    fetch next from @inner_cursor into @RctNumb, @QTYRct, @RctCostPerItem, @RCTITEM, @DATERECD, @rowIndex

    set @fetch_inner_cursor = @@FETCH_STATUS
    while @OHBrem >0  and @fetch_inner_cursor = 0
    begin

       set @OHBrem         = @OHBrem - @QTYRct
       set @QtyToCost      =  case 
                               When @SumRctInvcd + @QTYRct <= @OHB Then @QTYRct
                               else @OHB - @SumRctInvcd
                           end
       set @OHBCOST        = case 
                               When @SumRctInvcd + @QTYRct <= @OHB Then @OHBCOST + (@QtyToCost * @RctCostPerItem)
                               else @OHBCOST + (@QtyToCost * @RctCostPerItem)
                           end
       set @SumRctInvcd    = @SumRctInvcd + @QtyToCost

       fetch next from @inner_cursor into @RctNumb, @QTYRct, @RctCostPerItem, @RCTITEM, @DATERECD, @rowIndex

       set @fetch_inner_cursor = @@FETCH_STATUS
    end
    close @inner_cursor
    deallocate @inner_cursor
       INSERT @FiFo(OHBFIFOCOST, OHB,  ITEM, ReportDate, QtyToCost, SumRctInvcd)
       SELECT @OHBCOST, @OHB, @ITEM, GETDATE(), @QtyToCost, @SumRctInvcd;

    fetch next from @outer_cursor into  @ITEM, @OHB  
    set @fetch_outer_cursor = @@FETCH_STATUS


 end

 close @outer_cursor  
 deallocate @outer_cursor 
 select * from @FiFo

引用自：https://dba.stackexchange.com/questions/147489