Aggregate financial transactions into monthly balances

ghz 8months ago ⋅ 53 views

I have these financial transactions (name of client, date of transactions and debit and credit amount). There can be multiple transactions for the same client on the same date.

 Name   Date    Debit   Credit
 A  2021-01-01 00:00:00.0000000 10  0
 A  2021-01-01 00:00:00.0000000 9   0
 A  2021-02-01 00:00:00.0000000 11  0
 A  2021-03-01 00:00:00.0000000 0   50
 A  2021-04-01 00:00:00.0000000 30  0
 B  2021-01-01 00:00:00.0000000 10  0
 B  2022-02-01 00:00:00.0000000 0   12
 B  2022-03-01 00:00:00.0000000 0   50
 B  2024-04-01 00:00:00.0000000 3   0

I would like the balances for each client for each month. (It's fine if the there are missing years / months for when there were no transactions but ideally the result should be gapless.)

Below results with gaps (e.g. gap between B 2021/1 and B 2022/2)

Name    Year    Month   balance
A   2021    1   -19
A   2021    2   -30
A   2021    3   20
A   2021    4   -10
B   2021    1   -10
B   2022    2   2
B   2022    3   52
B   2024    4   49

Here results without gaps (... elided year/month with no transactions)

 Name    Year    Month   balance
 A   2021    1   -19 -- start from the very first transaction
 A   2021    2   -30
 A   2021    3   20
 A   2021    4   -10
 A   2021    5   -10 -- no transaction for 2021/5 so repeat the previous balance
 ...                -- show all year/months to 2024-03
 A   2024    4   -10 -- all the way to the current month
 
 B   2021    1   -10
 B   2021    2   -10 -- no transaction
 B   2021    3   -10 -- no transaction
 ...                 -- show all year/months to 2021-12
 B   2022    1   -10 -- no transaction
 B   2022    2   2   
 B   2022    3   52  
 B   2022    4   52  -- no transaction
 B   2022    5   52  -- no transaction
 ...                 -- show all year/months to 2024-03
 B   2024    3   49  -- no transaction
 B   2024    4   49

I managed to do this in 2 steps.

  1. First group per client, year and month.
  2. Then for each group sum all the preceding transactions to get the balance.

Here the SQL

 DECLARE @MyTransactions TABLE (
    [Name] nvarchar(10),
    [Date] datetime2,
    [Debit] decimal,
    [Credit] decimal
 )
 
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('A','2021-01-01',10,0)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('A','2021-02-01',11,0)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('A','2021-03-01',0,50)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('A','2021-04-01',30,0)
 
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('B','2021-01-01',10,0)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('B','2022-02-01',0,12)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('B','2022-03-01',0,50)
 INSERT INTO @MyTransactions ([Name],[Date],[Debit],[Credit]) VALUES ('B','2024-04-01',3,0)
 
 
 DECLARE @MyGroups TABLE (
    [Name] nvarchar(10),
    [Year] int,
    [Month] int
 )
 
 
 -- 1. first group per client, year and month
 INSERT INTO @MyGroups ([Name],[Year],[Month])
 SELECT [Name], DATEPART(YEAR, [Date]), DATEPART(MONTH, [Date])
 FROM @MyTransactions
 GROUP BY [Name],DATEPART(YEAR, [Date]), DATEPART(MONTH, [Date])
 
 -- 2. now for each group sum all the preceding transactions to get the balance
 SELECT 
    G.[Name], G.[Year], G.[Month],  SUM(ISNULL(T.Credit,0) - ISNULL(T.Debit,0))             
 FROM @MyGroups G
 JOIN @MyTransactions T ON 
    T.[Name] = G.[Name] 
    AND 
    (
    (DATEPART(YEAR, T.[Date]) = G.[Year] AND DATEPART(MONTH, T.[Date]) <= G.[Month])
    OR
    (DATEPART(YEAR, T.[Date]) < G.[Year])
    )
 GROUP BY G.[Name], G.[Year], G.[Month]

Questions

  1. Is there a way to do this more elegantly, e.g. in one step without a temporary grouping table?
  2. How to ensure that there are no gaps between the dates?

Answers

You can achieve this without using temporary tables by using window functions and generating a series of dates for each client to ensure there are no gaps in the dates. Here's how you can do it:

WITH AllDates AS (
    SELECT DISTINCT
        [Name],
        DATEADD(MONTH, m.MonthOffset, DATEFROMPARTS(Y.YearValue, m.MonthValue, 1)) AS TransactionDate
    FROM
        (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS m(MonthOffset)
    CROSS JOIN
        (VALUES (2021), (2022), (2023), (2024)) AS Y(YearValue)
),
ClientTransactions AS (
    SELECT
        t.[Name],
        DATEADD(MONTH, DATEDIFF(MONTH, 0, t.[Date]), 0) AS TransactionMonth,
        SUM(ISNULL(t.Credit, 0) - ISNULL(t.Debit, 0)) AS Balance
    FROM
        @MyTransactions t
    GROUP BY
        t.[Name],
        DATEADD(MONTH, DATEDIFF(MONTH, 0, t.[Date]), 0)
),
FilledDates AS (
    SELECT
        ad.[Name],
        ad.TransactionDate AS TransactionMonth,
        COALESCE(ct.Balance, LAG(ct.Balance) OVER (PARTITION BY ad.[Name] ORDER BY ad.TransactionDate)) AS Balance
    FROM
        AllDates ad
    LEFT JOIN
        ClientTransactions ct ON ad.[Name] = ct.[Name] AND ad.TransactionDate = ct.TransactionMonth
)
SELECT
    [Name],
    YEAR(TransactionMonth) AS [Year],
    MONTH(TransactionMonth) AS [Month],
    Balance
FROM
    FilledDates
ORDER BY
    [Name],
    [Year],
    [Month];

In this query:

  1. AllDates CTE generates all possible combinations of client names and months spanning from 2021 to 2024.

  2. ClientTransactions CTE calculates the balance for each client for each month based on the transactions.

  3. FilledDates CTE left joins the AllDates with the ClientTransactions to ensure all combinations are included, and fills in the missing balances by using the last known balance (using the LAG function).

  4. Finally, the main query selects the client name, year, month, and balance from the FilledDates CTE, ensuring the results are ordered by client name, year, and month.

This approach ensures there are no gaps in the dates and provides the desired results in one step without the need for temporary grouping tables.

#sql