Customer Ageing Report - Ageing in Calender Months

Am looking for an MS SQL script that will generate a report showing customer ageing balance on a monthly level. The only parameter being the due date. Hence if the parameter due date is selected as 17/09/2023 then the first column should be for September indicating  the customer balances from 1st Sept - 30th Sept, the second column should be for August with balances from 1st Aug - 31st Aug...etc going 12 months back, Any amounts beyond 12 months will be indicated under the column 12 Months +

Try this query:

DECLARE @DueDate DATE = '2023-09-17';  -- Input Due Date Parameter


-- Generate dynamic aging buckets based on the DueDate
WITH MonthlyPeriods AS (
    SELECT
        DATEADD(MONTH, -1 * (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1), @DueDate) AS StartDate,
        DATEADD(MONTH, -1 * (ROW_NUMBER() OVER (ORDER BY (SELECT 1))), @DueDate) AS EndDate
    FROM
        (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS Months(MonthNumber)
),
AgingBuckets AS (
    SELECT
        StartDate,
        EndDate,
        DATEDIFF(DAY, StartDate, EndDate) AS DaysRange,
        CONCAT(
            DATEDIFF(DAY, DueDate, @DueDate),
            ' - ',
            DATEDIFF(DAY, DueDate, @DueDate) + DATEDIFF(DAY, StartDate, EndDate),
            ' days'
        ) AS AgingCategory
    FROM
        MonthlyPeriods
)


-- Calculate customer balances for each aging bucket and monthly period
SELECT
    FORMAT(MonthlyPeriods.StartDate, 'MMMM yyyy') AS Month,
    AgingBuckets.AgingCategory,
    SUM(CASE WHEN DueDate BETWEEN AgingBuckets.StartDate AND AgingBuckets.EndDate THEN Balance ELSE 0 END) AS Balance,
    SUM(Balance) OVER (PARTITION BY MonthlyPeriods.StartDate) AS TotalBalance,
    CAST(SUM(CASE WHEN DueDate BETWEEN AgingBuckets.StartDate AND AgingBuckets.EndDate THEN Balance ELSE 0 END) AS FLOAT) / NULLIF(SUM(Balance) OVER (PARTITION BY MonthlyPeriods.StartDate), 0) AS BalancePercentage
FROM
    MonthlyPeriods
JOIN
    AgingBuckets
ON
    DueDate <= AgingBuckets.EndDate
LEFT JOIN
    YourTableNameHere AS CustomerData  -- Replace YourTableNameHere with your actual table name
ON
    DueDate <= AgingBuckets.EndDate
GROUP BY
    MonthlyPeriods.StartDate,
    AgingBuckets.AgingCategory
ORDER BY
    MonthlyPeriods.StartDate,
    AgingBuckets.StartDate;

Select allOpen in new window

Adjust it based on your actual due date and working environment.

Hi David,

Could you please explain where the DueDate column is coming from ? Which table would this be ?

Thanks !

That's sample data. You may change it adapt to your actual environment. You may share some of your snippet code if you require further assistance here.

Hi David,

I executed this part of the query : -

DECLARE @DueDate DATE = '20230917';  -- Input Due Date Parameter

    SELECT
        DATEADD(MONTH, -1 * (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1), @DueDate) AS StartDate,
        DATEADD(MONTH, -1 * (ROW_NUMBER() OVER (ORDER BY (SELECT 1))), @DueDate) AS EndDate
    FROM
        (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS Months(MonthNumber)

Select allOpen in new window

and I got this result

StartDate    EndDate
2023-09-17    2023-08-17
2023-08-17    2023-07-17
2023-07-17    2023-06-17
2023-06-17    2023-05-17
2023-05-17    2023-04-17
2023-04-17    2023-03-17
2023-03-17    2023-02-17
2023-02-17    2023-01-17
2023-01-17    2022-12-17
2022-12-17    2022-11-17
2022-11-17    2022-10-17
2022-10-17    2022-09-17

Select allOpen in new window

The expected date range should be as follows :-

It's monthly based ageing hence should represent the data in actual calender months.

Thank you!

Try this revised:

DECLARE @DueDate DATE = '2023-09-17';  -- Input Due Date Parameter


-- Generate dynamic aging buckets based on the DueDate
WITH MonthlyPeriods AS (
    SELECT
        DATEADD(MONTH, -1 * (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1), @DueDate) AS StartDate,
        DATEADD(DAY, -1, DATEADD(MONTH, -1 * (ROW_NUMBER() OVER (ORDER BY (SELECT 1))), @DueDate)) AS EndDate
    FROM
        (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS Months(MonthNumber)
)


-- Calculate customer balances for each aging bucket and monthly period
SELECT
    FORMAT(MonthlyPeriods.StartDate, 'dd/MM/yyyy') AS StartDate,
    FORMAT(EndDate, 'dd/MM/yyyy') AS EndDate,
    SUM(CASE WHEN DueDate BETWEEN MonthlyPeriods.StartDate AND EndDate THEN Balance ELSE 0 END) AS Balance
FROM
    MonthlyPeriods
LEFT JOIN
    YourTableNameHere AS CustomerData  -- Replace YourTableNameHere with your actual table name
ON
    DueDate <= EndDate
GROUP BY
    MonthlyPeriods.StartDate,
    EndDate
ORDER BY
    MonthlyPeriods.StartDate;

Select allOpen in new window

Hi,

The start date and end date range are not correct. If we can have the two dates correct then the amounts will also be correct. See my example in the last message and compare with the below from your latest script.

Try this:

DECLARE @DueDate DATE = '2023-09-17'; -- Input Due Date Parameter


-- Generate dynamic aging buckets based on the DueDate
WITH MonthlyPeriods AS (
    SELECT
        DATEADD(MONTH, -1 * (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1), DATEADD(DAY, 1, EOMONTH(@DueDate, -1))) AS StartDate,
        DATEADD(MONTH, -1 * (ROW_NUMBER() OVER (ORDER BY (SELECT 1))), EOMONTH(@DueDate, -1)) AS EndDate
    FROM
        (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)) AS Months(MonthNumber)
),
AgingBuckets AS (
    SELECT
        StartDate,
        EndDate,
        CASE 
            WHEN StartDate = MIN(StartDate) OVER () THEN '12+'
            ELSE CONVERT(VARCHAR(10), StartDate, 103)
        END AS AgingCategory
    FROM
        MonthlyPeriods
)


-- Calculate customer balances for each aging bucket and monthly period
SELECT
    AgingBuckets.AgingCategory,
    SUM(CASE WHEN DueDate BETWEEN AgingBuckets.StartDate AND AgingBuckets.EndDate THEN Balance ELSE 0 END) AS Balance
FROM
    AgingBuckets
LEFT JOIN
    YourTableNameHere AS CustomerData  -- Replace YourTableNameHere with your actual table name
ON
    DueDate <= AgingBuckets.EndDate
GROUP BY
    AgingBuckets.AgingCategory
ORDER BY
    AgingBuckets.StartDate;

Select allOpen in new window

Hi David,

 Let me share sample data which you could use on your end to better understand the scenario. 

Customer_Details.xlsx

• The problem persists in that the total of the column Balance does not add up to the correct summation total.
• The problem seems to be coming on the 12+ range.
• Sample Data is on the excel file - Customer_Detail - Run the query and the total amount on the balance column should add up to - 5,749,816.39. 
• There was an error on the AgeingBuckets.EndDate - I corrected it to be EOMONTH(AgeingBuckets.EndDate) - to get the proper end date of the month.

This part here is the problem

 SUM(CASE WHEN CustomerData.DueDate BETWEEN AgingBuckets.StartDate AND EOMONTH (AgingBuckets.EndDate) THEN CustomerData.Balance ELSE 0 END) AS Balance,

Select allOpen in new window