Dynamic ageing in a query

Dynamic aging in T-SQL is a technique commonly used in financial, accounting, or inventory systems to track and report the age of certain records based on varying time frames. It is especially useful for analyzing aging of accounts receivable, inventory items, or any records that need to be categorized into different time buckets.

DECLARE @AgingDate AS DATE = '11-JAN-2022';

WITH
test_data
AS
(
   SELECT tbl.* FROM (VALUES
	( '08-Jan-2022', 458.58)
   , ( '07-Jan-2022', 756.47)
   , ( '01-Jan-2022', 853.45)
   , ( '22-Dec-2021', 756.94)
   , ( '12-Dec-2021', 8754.53)
   , ( '22-Nov-2021', 456.55)
   , ( '03-Oct-2021', 887.14)
   , ( '03-May-2021', 295.67)
   ) tbl ([TRAN_DATE], [AMOUNT]) 
)
,
aging_metric
AS
(
   SELECT 
  tbl.*
  , [date_min] = DATEADD(DAY, -[days_min], @AgingDate)
  , [date_max] = DATEADD(DAY, -[days_max], @AgingDate)
  , [metric_label] =
 CASE
WHEN [days_max] IS NULL THEN '> ' + CONVERT([VARCHAR], [days_min])
ELSE (CONVERT([VARCHAR], [days_min]) + '-') + CONVERT([VARCHAR], [days_max])
 END + ' Days'
   FROM (VALUES
	( 0, 4) --might be a good idea to store this in a table if you're going to use it more than once
   , ( 4, 10)
   , ( 10, 20)
   , ( 20, 30)
   , ( 30, 50)
   , ( 50, 100)
   , ( 100, NULL)
   ) tbl ([days_min], [days_max]) 
)
SELECT 
	am.[metric_label]
   , am.[days_min]
   , am.[date_min]
   , am.[date_max] 
   , [d].[Amount]
FROM
   test_data AS [d] 
   LEFT JOIN aging_metric AS am ON [d].[TRAN_DATE] < am.[date_min] AND [d].[TRAN_DATE] >= ISNULL(am.[date_max], [d].[TRAN_DATE]);