Solving FIFO Queues Using Windowed Functions

Question

Solving FIFO Queues Using Windowed Functions

Viewing 2 posts - 16 through 16 (of 16 total)

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

Steve Collins SSCrazy Points: 2200 More actions · Answer 1

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

Steve Collins SSCrazy Points: 2200 More actions · Answer 2

????

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

Solving FIFO Queues Using Windowed Functions

Cookies on SQLServerCentral