Intelligent Debt Ageing Code

Question

I have some data in the format of;

Client Amt   Date
ABC Co £250  20/09/16
ABC Co £250  20/10/16
CDE Co £200  20/11/16
CDE Co £200  20/10/16
CDE Co £-200 20/09/16
FGH Co £600  01/01/16
FGH Co £-500 20/09/16
FGH Co £-50  20/10/16
FGH Co £100  20/11/16

I can pivot it like this easily;

Client Balance 0-29days 30-59days 60-89days 90days+
ABC Co £500    £0       £250      £250      £0
CDE Co £200    £200     £200      £-200     £0
FGH Co £100    £100     £-50      £-500     £600
IJK Co £-100   £100     £0        £0        £-200

But I need it to look like;

Client Balance 0-29days 30-59days 60-89days 90days+
ABC Co £500    £0       £250      £250      £0
CDE Co £200    £200     £0        £0        £0
FGH Co £100    £100     £0        £0        £50
IJK Co £-100   £0       £0        £0        £-100

The columns or "aging buckets" represent the age of a debit/credit. A single transaction will not occur in more than one bucket. If there are credits and debits they should be applied to eachother (starting with the oldest). So to elaborate on a few of the records...

CDE Co; The earliest transaction £-200 credit on 20/09 is balanced by the next transaction £200 debit on 20/10. This only leaves the £200 debit on 20/11 (hence the £200 debit in the 0-29days bucket).

FGH Co; The earliest transaction £600 debit on 01/01 is part paid by the 2 payments of £-500 (20/09) and £-50 (20/10) leaving £50 of debit in the 90days+ bucket and a more recent debit of £100 on 20/11 in the 0-29days bucket.

Is there a query/formula I can use to evaluate this? Or am I going to have to use a cursor?

Thanks

Answer 1

Link Showing it working : http://rextester.com/MLFE98410

I was curious which was was easier logically the recursive cte is a bit easier but sill has some of the same hurdles. Note I added yet 1 more test case here too.

DECLARE @Table AS TABLE (Client CHAR(6), AMT INT, Date DATE)
INSERT INTO @Table VALUES
('ABC Co',250 ,'2016/09/20')
,('ABC Co',250 ,'2016/10/20')
,('CDE Co',200 ,'2016/11/20')
,('CDE Co',200 ,'2016/10/20')
,('CDE Co',-200,'2016/09/20')
,('FGH Co',600 ,'2016/01/01')
,('FGH Co',-500,'2016/09/20')
,('FGH Co',-50 ,'2016/10/20')
,('FGH Co',100 ,'2016/11/20')
,('IJK Co',-100 ,'2016/01/01')
,('IJK Co',-100 ,'2016/09/20')
,('LMN Co',-200 ,'2016/01/01')
,('LMN Co', 50 ,'2016/06/10')
,('LMN Co',-100 ,'2016/09/20')

;WITH cteRowNumbers AS (
    SELECT *, RowNumber = ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Date DESC)
    FROM
       @Table
)

, cteRecursive AS (
    SELECT
       Client
       ,CurrentBalance = SUM(AMT)
       ,Date = CAST(GETDATE() AS DATE)
       ,Amt = CAST(0 AS INT)
       ,RemainingBalance = SUM(Amt)
       ,AttributedAmt = 0
       ,RowNumber = CAST(0 AS BIGINT)
    FROM
       @Table
    GROUP BY
       Client

    UNION ALL

    SELECT
       r.Client
       ,r.CurrentBalance
       ,c.Date
       ,c.AMT
       ,CASE WHEN SIGN(r.CurrentBalance) = SIGN(c.AMT) THEN r.CurrentBalance - c.AMT ELSE r.RemainingBalance END
       ,CASE
          WHEN SIGN(r.CurrentBalance) <> SIGN(c.AMT) THEN 0
          WHEN ABS(r.RemainingBalance) < ABS(c.AMT) THEN r.RemainingBalance
          ELSE c.AMT END
       ,c.RowNumber
    FROM
       cteRecursive r
       INNER JOIN cteRowNumbers c
       ON r.Client = c.Client
       AND r.RowNumber + 1 = c.RowNumber
    WHERE
       SIGN(r.RemainingBalance) = SIGN(r.CurrentBalance)
)

, ctePrepared AS (
    SELECT
       Client
       ,CurrentBalance
       ,DateGroup = CASE
          WHEN DATEDIFF(day,Date,GETDATE()) BETWEEN 0 AND 29 THEN '0-29days'
          WHEN DATEDIFF(day,Date,GETDATE()) BETWEEN 30 AND 59 THEN '30-59days'
          WHEN DATEDIFF(day,Date,GETDATE()) BETWEEN 60 AND 89 THEN '60-89days'
          WHEN DATEDIFF(day,Date,GETDATE()) >= 90 THEN '90days+'
              ELSE 'Unknown Error'
       END
       ,AttributedAmt
    FROM
       cteRecursive
    WHERE
       RowNumber > 0
       AND AttributedAmt <> 0
)

SELECT *
FROM
    ctePrepared c
    PIVOT (
       SUM(AttributedAmt)
       FOR DateGroup IN ([0-29days],[30-59days],[60-89days],[90days+])
    ) pvt
ORDER BY
    Client

Results

Client  CurrentBalance  0-29days    30-59days   60-89days   90days+
ABC Co  500            NULL         250         250         NULL
CDE Co  200            200          NULL        NULL         NULL
FGH Co  150            100          NULL        NULL         50
IJK Co  -200             NULL        NULL   -100         -100
LMN Co  -250             NULL        NULL   -100         -150

Answer 2

Link to Working Example: http://rextester.com/NAAUE88941

DECLARE @Table AS TABLE (Client CHAR(6), AMT INT, Date DATE)
INSERT INTO @Table VALUES
('ABC Co',250 ,'2016/09/20')
,('ABC Co',250 ,'2016/10/20')
,('CDE Co',200 ,'2016/11/20')
,('CDE Co',200 ,'2016/10/20')
,('CDE Co',-200,'2016/09/20')
,('FGH Co',600 ,'2016/01/01')
,('FGH Co',-500,'2016/09/20')
,('FGH Co',-50 ,'2016/10/20')
,('FGH Co',100 ,'2016/11/20')
,('IJK Co',-100 ,'2016/01/01')
,('IJK Co',-100 ,'2016/09/20')

;WITH cte AS (
    SELECT
       Client
       ,Date
       ,AMT
       ,CurrentBalance = SUM(AMT) OVER (PARTITION BY Client)
       ,BackwardsRunningTotal = SUM(AMT) OVER (PARTITION BY Client ORDER BY Date DESC)
       ,CurrentBalanceMinusBackwardsRunningTotal = SUM(AMT) OVER (PARTITION BY Client) - SUM(AMT) OVER (PARTITION BY Client ORDER BY Date DESC)
       ,DateGroup = CASE
             WHEN DATEDIFF(day,Date,GETDATE()) BETWEEN 0 AND 29 THEN '0-29days'
             WHEN DATEDIFF(day,Date,GETDATE()) BETWEEN 30 AND 59 THEN '30-59days'
             WHEN DATEDIFF(day,Date,GETDATE()) BETWEEN 60 AND 89 THEN '60-89days'
             WHEN DATEDIFF(day,Date,GETDATE()) >= 90 THEN '90days+'
             ELSE 'Unknown Error'
       END
       ,BalanceAtTime = SUM(AMT) OVER (PARTITION BY Client ORDER BY Date)
    FROM
       @Table
)

, cteWhenCurrentBalanceIsMet AS (
    SELECT
       Client
       ,MaxDate = MAX(DATE)
    FROM
       cte
    WHERE
       CurrentBalanceMinusBackwardsRunningTotal = 0
    GROUP BY
       Client
)

, cteAgedDebtPrepared AS (
    SELECT
       c.Client
       ,Balance = c.CurrentBalance
       ,c.DateGroup
       ,Amt = CASE
          WHEN CurrentBalanceMinusBackwardsRunningTotal = 0
          THEN ISNULL(LAG(CurrentBalanceMinusBackwardsRunningTotal) OVER (PARTITION BY c.Client ORDER BY Date DESC),AMT)
          ELSE AMT
       END
    FROM
       cteWhenCurrentBalanceIsMet m
       INNER JOIN cte c
       ON m.Client = c.Client
       AND m.MaxDate <= c.Date
       AND SIGN(c.AMT) = SIGN(c.CurrentBalance)
)

SELECT *
FROM
    cteAgedDebtPrepared
    PIVOT (
       SUM(Amt)
       FOR DateGroup IN ([0-29days],[30-59days],[60-89days],[90days+])
    ) pvt
ORDER BY
    Client

Definitely was a challenging question it was more so because even though you are saying you are looking at aged Debt you actually show both Aged Debt and Aged Credit in your Pivot Table. I think it would be easier to do in a Recursive CTE but I wanted more of a set based operation so above is what I have come up with and it works for all of your test cases. Note I did add one where the net was a Credit.

General Steps

• Determine Current Balance
• A Backwards Running Total (e.g. SUM(AMT) From most current date to the oldest date)
• Subtract the Backwards Running Total From Current Balance to determine at which point the balance was last 0 and then get the MAX(date) at which that occured
• Do a self join to grab all of the records >= that MAX(date) that are the same SIGN(), e.g. when balance is positive then amt must be positive or reverse negative and negative for credits. The reason it has to be the same SIGN() is that the inverse would actually affect the balance in the opposite direction we are looking for.
• Figure Out The remainder of the Debt or Credit that needs to be attributed to the first Row when the balance was last 0 by looking at the previous row or assigning the AMT
• Pivot As Desired

the results:

Client  Balance 0-29days    30-59days   60-89days   90days+
ABC Co  500     NULL        250         250         NULL
CDE Co  200     200         NULL        NULL        NULL
FGH Co  150     100         NULL        NULL        50
IJK Co  -200    NULL        NULL        -100        -100

Note for My IJK example I had two credits of -100 each.

Answer 3

Here is a solution which seems to match your expected output. Note, it's a bit messy and you might be able to simplify the logic a bit, but at least it seems to work.

Link to working example: http://rextester.com/OWH97326

Note that this answer is adapted from a solution to a slightly similar problem on dba.stackexchange.com. I was very impressed with the solution.

Create Table Debt (
    Client char(6),
    Amount money,
    [Date] date);

Insert Into Debt 
Values 
('ABC Co', 250,  Convert(date, '20/09/2016', 103)),
('ABC Co', 250,  Convert(date, '20/10/2016', 103)),
('CDE Co', 200,  Convert(date, '20/11/2016', 103)),
('CDE Co', 200,  Convert(date, '20/10/2016', 103)),
('CDE Co', -200, Convert(date, '20/09/2016', 103)),
('FGH Co', 600,  Convert(date, '01/01/2016', 103)),
('FGH Co', -500, Convert(date, '20/09/2016', 103)),
('FGH Co', -50,  Convert(date, '20/10/2016', 103)),
('FGH Co', 100,  Convert(date, '20/11/2016', 103));

With Grouping_cte As (
Select Client, Sum(ABS(Amount)) As Amount, 
    Case When DateDiff(Day, GetDate(), [Date]) > -30 Then '0-29 days'
         When DateDiff(Day, GetDate(), [Date]) > -60 Then '30-59 days'
         When DateDiff(Day, GetDate(), [Date]) > -90 Then '60-89 days'
         Else '90+ days' End As [Date],
    Case When Amount < 0 Then 'In' Else 'Out' End As [Type]
  From Debt
  Group By Client,
    Case When DateDiff(Day, GetDate(), [Date]) > -30 Then '0-29 days'
         When DateDiff(Day, GetDate(), [Date]) > -60 Then '30-59 days'
         When DateDiff(Day, GetDate(), [Date]) > -90 Then '60-89 days'
         Else '90+ days' End,
    Case When Amount < 0 Then 'In' Else 'Out' End),
RunningTotals_cte As (
Select Client, Amount, [Date], [Type],
    Sum(Amount) Over (Partition By Client, [Type] Order By [Date] Desc) - Amount As RunningTotalFrom,
    Sum(Amount) Over (Partition By Client, [Type] Order By [Date] Desc) As RunningTotalTo
  From Grouping_cte),
Allocated_cte As (
Select Outs.Client, Outs.Date, Outs.Amount + IsNull(Sum(x.borrowed_qty),0) As AdjustedAmount
  From (Select * From RunningTotals_cte Where [Type] = 'Out') As Outs
  Left Join (Select * From RunningTotals_cte Where [Type] = 'In') As Ins
    On Ins.RunningTotalFrom < Outs.RunningTotalTo
    And Outs.RunningTotalFrom < Ins.RunningTotalTo
    And Ins.Client = Outs.Client
  Cross Apply (
      Select Case When ins.RunningTotalTo < Outs.RunningTotalTo Then Case When ins.RunningTotalFrom > Outs.RunningTotalFrom  Then -1 * Ins.Amount
                                                                          Else -1 * (Ins.RunningTotalTo - Outs.RunningTotalFrom) End
                  Else Case When Outs.RunningTotalFrom > Ins.RunningTotalFrom Then Outs.Amount
                            Else -1 * (Outs.RunningTotalTo - Ins.RunningTotalFrom) End End) As x (borrowed_qty)
  Group By Outs.Client, Outs.Date, Outs.Amount)
--Select * From Allocated_cte;

Select Client,
    Sum(AdjustedAmount) As Balance,
    Sum(iif([Date] = '0-29 days', AdjustedAmount, Null)) As [0-29 days],
    Sum(iif([Date] = '30-59 days', AdjustedAmount, Null)) As [30-59 days],
    Sum(iif([Date] = '60-89 days', AdjustedAmount, Null)) As [60-89 days],
    Sum(iif([Date] = '90+ days', AdjustedAmount, Null)) As [90+ days]
  From Allocated_cte
  Group By Client;

Answer 4

I have already answered a similar question here and here and here

You need to explode debits and credits to single units and then couple them chronologically, and filter away the matching rows, then you can age them per periods.

Just pivot the sum up for each period.

DECLARE @Table AS TABLE (Client CHAR(6), AMT INT, Date DATE)
INSERT INTO @Table VALUES
('ABC Co',250 ,'2016/09/20')
,('ABC Co',250 ,'2016/10/20')
,('CDE Co',200 ,'2016/11/20')
,('CDE Co',200 ,'2016/10/20')
,('CDE Co',-200,'2016/09/20')
,('FGH Co',600 ,'2016/01/01')
,('FGH Co',-500,'2016/09/20')
,('FGH Co',-50 ,'2016/10/20')
,('FGH Co',100 ,'2016/11/20')
,('IJK Co',-200 ,'2016/01/01')
,('IJK Co',100 ,'2016/09/20')

For FN_NUMBERS(n), it is a tally table, look at other answers I have linked above to get an example or google it.

;with
m as (select * from @Table),
e as (select * from m where AMT>0),
r as (select * from m where AMT<0),
ex as (
    select *, ROW_NUMBER() over (partition by Client order by [date] ) rn, 1 q
    from e
    join FN_NUMBERS(1000) on N<= e.AMT
),
rx as (
    select *, ROW_NUMBER() over (partition by Client order by [date] ) rn, 1 q
    from r
    join FN_NUMBERS(1000) on N<= -r.AMT
),
j as (
select 
    isnull(ex.Client, rx.Client) Client, 
    (datediff(DAY, ISNULL(ex.[Date],rx.[Date]), GETDATE()) / 30) dd,
    (isnull(ex.q,0) - isnull(rx.q,0)) q
from ex
full join rx on ex.Client = rx.Client and ex.rn = rx.rn 
where ex.Client is null or  rx.Client is null
),
mm as (
    select j.Client, j.q, isnull(x.n,99) n
    from j
    left join (values (0),(1),(2)) x (n) on dd=n
),
b as (
    select Client, SUM(AMT) balance
    from m
    group by Client
),
p as (
    select b.*, p.[0] as [0-12days], p.[1] as [30-59days], p.[2] as [60-89days], p.[99] as [90days+]
    from mm
    pivot (sum(q) for n in ([0],[1],[2],[99])) p
    left join b on p.Client = b.Client
)
select *
from p
order by 1

perfect output

Client  balance 0-12days    30-59days   60-89days   90days+
ABC Co  500     NULL        250         250         NULL
CDE Co  200     200         NULL        NULL        NULL
FGH Co  150     100         NULL        NULL        50
IJK Co  -100    NULL        NULL        NULL        -100

bye

Answer 5

If you only need the data in the format you have provided and what you say in the comments about having an unpivoted base table with this data in, the query is very simple:

declare @t table(PaymentDate date
                ,Client nvarchar(50)
                ,Amount decimal(10,2)
                );
insert into @t values
 ('20160920','ABC Co',250),('20161020','ABC Co',250  ),('20161020','CDE Co',200  ),('20161020','CDE Co',200  ),('20160920','CDE Co',-200 ),('20160101','FGH Co',600  ),('20160920','FGH Co',-500 ),('20161020','FGH Co',-100 ),('20161120','FGH Co',100  );

declare @ReportDate date = getdate();

select Client

        -- Data aggregated by each period
        ,sum(Amount) as ClientBalance
        ,sum(case when PaymentDate between dateadd(d,-29,@ReportDate) and @ReportDate then Amount else 0 end) as [0-29 Days]
        ,sum(case when PaymentDate between dateadd(d,-59,@ReportDate) and dateadd(d,-30,@ReportDate) then Amount else 0 end) as [30-59 Days]
        ,sum(case when PaymentDate between dateadd(d,-89,@ReportDate) and dateadd(d,-60,@ReportDate) then Amount else 0 end) as [60-89 Days]
        ,sum(case when PaymentDate <= dateadd(d,-90,@ReportDate) then Amount else 0 end) as [90+ Days]

        ,'' as [ ]

        -- Data aggregated as a rolling periodic balance
        ,sum(Amount) as ClientBalance
        ,sum(case when PaymentDate <= @ReportDate then Amount else 0 end) as [0-29 Days]
        ,sum(case when PaymentDate <= dateadd(d,-30,@ReportDate) then Amount else 0 end) as [30-59 Days]
        ,sum(case when PaymentDate <= dateadd(d,-60,@ReportDate) then Amount else 0 end) as [60-89 Days]
        ,sum(case when PaymentDate <= dateadd(d,-90,@ReportDate) then Amount else 0 end) as [90+ Days]
from @t
group by Client
order by Client;