Solving FIFO Queues Using Windowed Functions

• Steve Collins

  SSCrazy

  Points: 2069

  More actions

  April 5, 2021 at 8:02 pm

  #3865713

  Here's another approach which uses a tally function to row multiply the 'Amount' column value(s) for both credits and debits.  A similar approach to FIFO calculation recently won some points on Stack Overflow. In this case it's only necessary to SUM the SIGN(Amount)'s.  The SIGN function returns either 1 (if the parameter is positive) or -1 (if the parameter is negative).   For some reason here when the RemainingBalance is a credit it's expressed as a positive number, so the calculation multiples the sum of the signs by -1.

  with
  credits_cte as (
      select *, row_number() over (partition by CustID 
                                   order by TransDate) trans_rn 
      from #Transactions t
           cross apply dbo.fnTally(1, abs(t.Amount)) fn
      where TransType='C'),
  debits_cte as (
      select *, row_number() over (partition by CustID 
                                   order by TransDate) trans_rn 
      from #Transactions t
           cross apply dbo.fnTally(1, t.Amount) fn
      where TransType='D')
  select c.CustID, min(c.TransDate) CreditDate, 
         sum(isnull(sign(c.Amount), 0)+
             isnull(sign(d.Amount), 0))*-1 RemainingBalance,
         max(d.TransDate) LastRedeemedDate
  from credits_cte c
       full join debits_cte d on c.CustID=d.CustID
                                 and c.trans_rn=d.trans_rn
  group by c.TransID, c.CustID
  order by c.TransID, c.CustID;

  CustIDCreditDateRemainingBalanceLastRedeemedDate
  12016-01-010.002016-02-01
  22016-01-010.002016-03-01
  32016-01-010.002016-04-01
  32016-03-010.002016-06-01
  32016-05-010.002016-06-01
  42016-01-010.002016-04-01
  42016-03-010.002016-07-01
  42016-06-010.002016-07-01
  52016-01-0110.002016-02-01
  62016-01-0120.00NULL
  72016-01-010.002016-03-01
  72016-02-010.002016-03-01
  82016-01-010.002016-04-01
  82016-03-0130.002016-04-01
  92016-01-010.002016-05-01
  92016-03-010.002016-05-01
  92016-04-0110.002016-05-01
  102016-01-010.002016-02-01
  102016-03-010.002016-05-01
  102016-06-0110.002016-09-01
  102016-08-0150.00NULL

   

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können