Intelligent debt-ageing code, Sql Server

Language: Layout:

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;

View schema
Execution time: 0,03 sec, rows selected: 3, rows affected: 9, absolute service time: 0,19 sec