Running Totals That Reset Every Month

The monthly report starts February with January's accumulated balance still attached. Running totals that reset need the period in the window partition. Give the rows a stable sequence too, especially when several transactions share one timestamp.

Define the Accumulation Grain and Reset Boundary

A running total adds the current value to preceding values within a defined group. The partition determines where that accumulation starts again. The window ordering determines which transactions precede the current one.

For a calendar-month reset, use both year and month. Partitioning on month number alone combines January from different years. That mistake can remain hidden until the dataset spans another reporting year.

I ask whether the report describes transaction sequence or daily closing totals. I also clarify whether each customer has an independent accumulation. Those choices determine the partition and ordering before the SUM expression becomes useful.

Choose the timestamp meaning explicitly. UTC calendar months differ from local business months around their boundaries. If the report uses a business-local month, derive or store that period consistently before applying the window.

Create Boundary and Tie Cases

Run the sample in one connection. It includes a repeated timestamp, a negative adjustment, multiple customers, and two different years. The transaction identifier makes the same-timestamp sequence deterministic.

DROP TABLE IF EXISTS #MonthlyLedger;
CREATE TABLE #MonthlyLedger
(
    TransactionId bigint NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    TransactionAt datetime2(0) NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #MonthlyLedger VALUES
(1,1,'20250101 09:00:00',10),
(2,1,'20250101 09:00:00',5),
(3,2,'20250102 10:00:00',4),
(4,1,'20250131 16:00:00',-2),
(5,1,'20250201 09:00:00',7),
(6,2,'20250202 09:00:00',6),
(7,1,'20260101 09:00:00',20);
SELECT * FROM #MonthlyLedger
ORDER BY TransactionAt, TransactionId;

These amounts are sample inputs, not an observed financial balance. The negative adjustment belongs in the total because this example measures net activity. Filtering adjustments away would change the measure rather than repair the accumulation.

TransactionId resolves equal timestamps, but it is not necessarily the business sequence in every real system. Use a source sequence when it defines the required ordering. Ensure that the complete ordering key remains unique within each partition.

Running Totals That Reset Across Calendar Months

The next query accumulates all customers together within each calendar month. That scope is intentional so you can compare it with the customer-specific version later. The outer ORDER BY controls presentation independently from the window's internal order.

SELECT TransactionId, CustomerId, TransactionAt, Amount,
       SUM(Amount) OVER
       (
           PARTITION BY YEAR(TransactionAt), MONTH(TransactionAt)
           ORDER BY TransactionAt, TransactionId
           ROWS UNBOUNDED PRECEDING
       ) AS MonthlyRunningTotal,
       COUNT_BIG(*) OVER
       (
           PARTITION BY YEAR(TransactionAt), MONTH(TransactionAt)
           ORDER BY TransactionAt, TransactionId
           ROWS UNBOUNDED PRECEDING
       ) AS MonthlyRunningCount
FROM #MonthlyLedger
ORDER BY TransactionAt, TransactionId;

Each new year-and-month partition starts from its own first transaction. The running count accumulates rows under the same boundary and sequence. COUNT_BIG counts transactions, not quantities, distinct customers, or calendar days.

ROWS UNBOUNDED PRECEDING is shorthand for the frame ending at the current row. It makes transaction-by-transaction accumulation explicit. Keeping the same frame in both expressions prevents count and value from describing different populations.

Do not add a display filter for late-month rows inside the same SELECT and expect earlier rows to contribute. WHERE runs before the window calculation. Calculate the full intended monthly population first, then filter its displayed rows in an outer query.

Running Totals That Reset for Each Customer

Add CustomerId to the partition when each customer needs a separate monthly total. The same timestamp can now belong to several independent sequences. Every other column required to identify a separate reporting series also belongs in that partition.

;WITH Accumulated AS
(
    SELECT TransactionId, CustomerId, TransactionAt, Amount,
           SUM(Amount) OVER
           (
               PARTITION BY CustomerId, YEAR(TransactionAt), MONTH(TransactionAt)
               ORDER BY TransactionAt, TransactionId
               ROWS UNBOUNDED PRECEDING
           ) AS CustomerMonthlyTotal
    FROM #MonthlyLedger
)
SELECT TransactionId, CustomerId, TransactionAt, Amount, CustomerMonthlyTotal
FROM Accumulated
WHERE TransactionAt >= '20250115' AND TransactionAt < '20250215'
ORDER BY CustomerId, TransactionAt, TransactionId;

The outer date filter displays a smaller interval without removing earlier contributing January transactions. That distinction matters for midmonth reports. A reporting interval and an accumulation interval are not automatically identical.

This version still has no opening balance from before the month. It calculates monthly activity, not a customer's lifetime account balance. An opening balance requires an explicitly defined measure. Do not silently carry the prior partition forward.

When amounts can be NULL, define whether that represents unknown activity or no activity. SUM ignores NULL values, while a running count can still count their rows. Report missing amounts separately when that difference matters.

Compare ROWS with the Default Peer Frame

For an ordered aggregate without an explicit frame, the usual default is RANGE through the current row. Rows sharing the window's ordering value become peers. With ordering on timestamp alone, both same-time transactions receive a total including that peer group.

SELECT TransactionId, TransactionAt, Amount,
       SUM(Amount) OVER
       (
           PARTITION BY YEAR(TransactionAt), MONTH(TransactionAt)
           ORDER BY TransactionAt
       ) AS TimestampPeerTotal,
       SUM(Amount) OVER
       (
           PARTITION BY YEAR(TransactionAt), MONTH(TransactionAt)
           ORDER BY TransactionAt, TransactionId
           ROWS UNBOUNDED PRECEDING
       ) AS TransactionSequenceTotal
FROM #MonthlyLedger
ORDER BY TransactionAt, TransactionId;

The first two sample rows make this difference visible. The peer calculation shows their combined amount on both rows. The explicit row calculation advances one uniquely ordered transaction at a time.

Neither behavior is inherently wrong. A daily closing report can deliberately aggregate peers or summarize daily values first. A transaction ledger usually needs individual steps, which is why its frame and tie breaker should be explicit.

Map Fiscal Periods as Data

A fiscal period does not have to match a calendar month. Store approved period boundaries and join each transaction to its period. Half-open intervals make a timestamp on the next period's start belong to that next period.

DROP TABLE IF EXISTS #FiscalPeriods;
CREATE TABLE #FiscalPeriods
(PeriodId int PRIMARY KEY, StartsAt datetime2(0) NOT NULL, EndsAt datetime2(0) NOT NULL);
INSERT #FiscalPeriods VALUES
(1,'20250101','20250129'),(2,'20250129','20250226');
SELECT l.TransactionId, l.CustomerId, p.PeriodId, l.Amount,
       SUM(l.Amount) OVER
       (
           PARTITION BY l.CustomerId, p.PeriodId
           ORDER BY l.TransactionAt, l.TransactionId
           ROWS UNBOUNDED PRECEDING
       ) AS FiscalRunningTotal
FROM #MonthlyLedger AS l
JOIN #FiscalPeriods AS p
  ON l.TransactionAt >= p.StartsAt AND l.TransactionAt < p.EndsAt
ORDER BY l.CustomerId, p.PeriodId, l.TransactionAt, l.TransactionId;

Validate that fiscal intervals neither overlap nor leave unexpected gaps. Overlap duplicates transactions in the join, while a gap omits them. The 2026 sample deliberately lies outside these two defined fiscal periods and therefore needs separate coverage if reported.

I test running totals that reset at exact period boundaries and duplicate timestamps. I also verify that a display filter preserves earlier contributing rows. January should not follow you into February unless the balance definition specifically invites it.

What resets the total: month, customer, fiscal period, or another business grouping? Put the complete answer into the partition. Running totals that reset become reliable when their boundaries, sequence, and contributing population are explicit.

Late-arriving transactions can change totals already displayed for earlier rows. Decide whether the report recalculates history or preserves a published snapshot. Keep the extraction cutoff with the result so a later recalculation can explain its different contributing population.

Validate the final accumulated value against the ordinary monthly SUM for each partition. Their agreement checks the population and reset boundary. It does not prove that every intermediate sequence is correct, so retain the duplicate-timestamp test too.

For large ledgers, inspect the actual plan for sorting and spill behavior. Consider an index matching the customer and transaction sequence when that fits the workload. Persisted period keys can support repeated period-based reporting, but their definition must follow the approved calendar.

Related reading on this blog: How to Find Running Total in SQL Server and What is T-SQL Window Function Framing? Notes from the Field #102.

A monthly running total is not a lifetime balance, it is ordered activity within a defined period.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.