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.

    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

Viewing post 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply