# Solving FIFO Queues Using Windowed Functions

• 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.

`withcredits_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) LastRedeemedDatefrom credits_cte c     full join debits_cte d on c.CustID=d.CustID                               and c.trans_rn=d.trans_rngroup by c.TransID, c.CustIDorder by c.TransID, c.CustID;`
`CustIDCreditDateRemainingBalanceLastRedeemedDate12016-01-010.002016-02-0122016-01-010.002016-03-0132016-01-010.002016-04-0132016-03-010.002016-06-0132016-05-010.002016-06-0142016-01-010.002016-04-0142016-03-010.002016-07-0142016-06-010.002016-07-0152016-01-0110.002016-02-0162016-01-0120.00NULL72016-01-010.002016-03-0172016-02-010.002016-03-0182016-01-010.002016-04-0182016-03-0130.002016-04-0192016-01-010.002016-05-0192016-03-010.002016-05-0192016-04-0110.002016-05-01102016-01-010.002016-02-01102016-03-010.002016-05-01102016-06-0110.002016-09-01102016-08-0150.00NULL`

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

Viewing post 16 (of 15 total)