aged debit, Sql Server

Language: Layout:

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
    FROM
	   @Table
    --WHERE
	   --Client IN ('FGH Co') --,'CDE Co')
)

, 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

View schema
Execution time: 0,03 sec, rows selected: 4, rows affected: 11, absolute service time: 0,22 sec