# SQL Server - How to solve FIFO Cost of OHB... function, cursor,?

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.

• 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? Aug 23, 2016 at 14:19

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/

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

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

you can probably use a group by:

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