1

SQL Server 2008R2 - Need to solve FIFO cost of items on hand. To do this I need to add the cost for the most recent receipts quantity up to the current on hand balance(OHB).

So lets say I have Item : 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

Here I would need: (1*1.00)+(3*2.00)+(1*1.25) = CostofOHB = 8.25

I have no idea how to address this with SQL.

1
  • sum(x) Over ( Order By) does not seem to be supported with SQL Server 2008R2. " Incorrect syntax near 'order'. " This method works well in 2012 or later. What options available for 2008R2?
    – stinkyjak
    Aug 23, 2016 at 14:19

3 Answers 3

2

Kathi Kellenberger did a comparison of FIFO costing for Redgate in 2010. I used the winning entry method to calculate monthly inventory quantities and values for our Mexico operations. You can search for Set based speed PHreakery if this link does not work. https://www.red-gate.com/simple-talk/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

0

I came up with this thanks to hints from other sources.

running-totals

nested-cursors-w-errors

It is very slow compared to the over(order by) option in 2012 or later.

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
1
  • You could look into using a Case statement as opposed to the cursor. They usually perform better and are very flexible.
    – Muab Nhoj
    Dec 4, 2019 at 16:13
0

you can probably use a group by:

    select item, sum(qtyrct * rctcostperitem) as costOHB
    group by item

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.