🔧 Calculate monthly account balance and fill in missing dates:From SQL to SPL

There is a transaction table for the asset accounts in the MS SQL database, with dates that are not consecutive.

Now we need to calculate the balance of each account for each month from January 2021 at the beginning of the period to April 2024 at the end of the period, and fill in the missing months.

SQL Solution：

WITH Accounts AS (
    SELECT DISTINCT Name FROM trans
),
Months AS (
    SELECT DATEADD(MONTH, n, '2021-01-01') AS MonthStart
    FROM (
        SELECT TOP (DATEDIFF(MONTH, '2021-01-01', '2024-04-01') + 1) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM master.dbo.spt_values
    ) AS Numbers
),
AccountMonths AS (
    SELECT a.Name, m.MonthStart
    FROM Accounts a
    CROSS JOIN Months m
),
Changes as (
    SELECT Name, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0) as ym,
    sum(Credit - Debit) as change 
    FROM trans group by Name, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0)
),
fullChanges as (
    SELECT A.Name,A.MonthStart,c.change 
    FROM AccountMonths a left join Changes c 
    on a.Name=c.Name and a.MonthStart=c.ym
)
SELECT 
   Name,YEAR(MonthStart) AS Y,MONTH(MonthStart) AS M,
   SUM(change) OVER (PARTITION BY Name ORDER BY MonthStart) AS balance
   FROM fullChanges

SQL does not have a convenient method to generate a months sequence, so nested queries and window functions are required, and the code is very complex.

SPL provides functions for generating dates sequence, including consecutive months.

A1： Query the database, group by account and date of the first day of each month, and calculate the monthly amount changes.

A2： Generate a continuous sequence consisting of the first day of each month. periods() generates a dates sequence, and @m represents the interval unit in month.

A3： Cross joint the account and date sequence.

A4： Left join the last cross-join result with A1.

A5： When the current account remains unchanged compared to the previous record, the current month's balance equals the change in the current month's amount plus the previous month's balance; When the account changes, the current month balance is reset to the current month amount change.

Open source address

Free download