Any ideas on how to implement FIFO for my ledger?

I have a ledger of transactions that need to be applied to one another. Inbound transactions (positive Quantity values) occur first & outbound transactions (negative Quantity values) must be applied to the positive ones in first-in, first-out order. This is the enclosed dataset CREATE TABLE #ledger ( id INT IDENTITY(1,1) PRIMARY KEY, Date Date, Produc -

Can anyone point me in the right direction?


could you explain how you want it done ?



In ideal scenario I'd like to know which ids from outbound quantity are connected to which ids from inbound quantity like this.

Though honestly, any output would be good at this point. Thanks

inbound_id outbound_id
5 8
5 10
5 12
5 15

Probably easiest way that I can think of is to use loop to iterate each inbound transaction and match it with the outbound. For each inbound transaction store the matches in temp table #matches

CREATE TABLE #matches -- Table to hold the matches
  Outbound_ID int,
  Inbound_ID int,
  Match_Qty int

declare @ID int

select @ID = min(ID) from #ledger where Quantity > 0;

-- Iterate through the Inbound (Quantity > 0) line
while @ID is not null

  with inbound as
    select ID, [Date], ProductID, Qty = Quantity
    from   #ledger
    where  ID = @ID
  outbound as
    select l.ID, l.Date, l.ProductID, 
           Qty    = -(l.Quantity + isnull(m.Match_Qty, 0)),
           CumQty = sum(-Quantity) over (partition by ProductID order by ID)
    from   #ledger l
           left join
               select Outbound_ID, Match_Qty = sum(Match_Qty)
               from   #matches
               group by Outbound_ID
           ) m  on l.ID = m.Outbound_ID
    where  l.Quantity + isnull(m.Match_Qty, 0) < 0  
  insert into #matches (Outbound_ID, Inbound_ID, Match_Qty)
  select Outbound_ID = o.ID,
         Inbound_ID  = i.ID,
         Match_Qty   = m.Match_Qty
  from   inbound i
         inner join outbound o on    i.ProductID = o.ProductID
                              and    i.[Date]   <= o.[Date]
         cross apply
             select Match_Qty   = case when i.Qty >= o.CumQty
                                       then o.Qty
                                       when i.Qty >  o.CumQty - o.Qty
                                       then i.Qty - (o.CumQty - o.Qty)
                                       else 0
         ) m
  where  m.Match_Qty > 0

  select @ID = min(ID) from #ledger where Quantity > 0 and ID > @ID

select *
from   #matches

db<>fiddle demo


I hadn't thought to use a loop in SQL, but that makes the most sense. I wasn't getting very far with nested CTEs. Thanks!