Moving Averages in T-SQL

A seven row average looks sensible until one week has missing dates. Moving averages in T-SQL require you to decide whether the window means rows or calendar days.

Define Moving Averages in Business Terms

Moving averages smooth a sequence by averaging recent observations. The key word is recent. Do you mean the latest seven rows, seven calendar days, or seven business days? Those definitions produce different results when days are missing. Write the requirement before writing the window clause.

I ask what should happen at the start of the series. A seven day average can use fewer available days, return NULL until a full window exists, or use a fixed denominator. Each choice answers a different question. The report should label the choice.

A window calculation also needs a stable grain. If a table has several orders per day, average daily totals after grouping. Averaging individual order amounts over seven rows is not the same calculation. SQL Server will execute both without asking what the analyst intended.

Use ROWS BETWEEN for a Row Window

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes the current row and six prior rows in the ordered partition. AVG ignores NULL values in the expression. Make the ORDER BY unique when several rows share a date, or aggregate to one row per date first.

The following query assumes dbo.DailySales has one row per SalesDate for each StoreId. A unique constraint on StoreId and SalesDate should enforce that assumption. The expression returns an average of up to seven available rows, including shorter windows at the beginning.

I inspect the first few rows by hand. Off-by-one mistakes are easy when the requirement says seven days and the code says six preceding. The current row is the seventh row. That small detail earns a check.

SELECT StoreId, SalesDate, SalesAmount,
       AVG(CONVERT(decimal(19,4), SalesAmount)) OVER
       (
           PARTITION BY StoreId
           ORDER BY SalesDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS AverageOfLastSevenRows
FROM dbo.DailySales
ORDER BY StoreId, SalesDate;

Fill Date Gaps When Days Matter

A row window skips missing dates. If a store has no row on Sunday, Monday’s seven row average reaches back eight calendar days. That can be correct for seven observed days, but wrong for seven calendar days. Join the series to a date dimension or generated calendar before applying the window.

Decide how a missing day contributes. If it means zero sales, use zero in the calendar row. If it means missing source data, leave NULL and alert rather than treating it as zero. The average denominator changes because AVG ignores NULL. That distinction can change a business conclusion.

I check one known gap in the output before publishing. A sequence of dates in the ORDER BY is not proof that the underlying table has every date. The calendar join makes the expected dates explicit.

SELECT d.DateValue, s.StoreId,
       COALESCE(s.SalesAmount, CONVERT(decimal(18,2), 0)) AS SalesAmount
FROM dbo.DateDimension AS d
LEFT JOIN dbo.DailySales AS s
  ON s.SalesDate = d.DateValue
 AND s.StoreId = 10
WHERE d.DateValue >= '2025-01-01'
  AND d.DateValue < '2025-02-01'
ORDER BY d.DateValue;

Handle a Full Window for Moving Averages

Some reports should show NULL until seven calendar days are available. Add a COUNT window over the same frame and use a CASE expression. If source values can be NULL, count the expression rather than COUNT(*). Then the rule says seven valid observations, not merely seven calendar rows.

Do not use a fixed denominator of seven without deciding what missing values mean. Dividing a five day sum by seven implicitly treats the missing two days as zero. That can be the correct business rule, but it should be named, not discovered after a review meeting.

What does a holiday mean in your series? Retail sales, call volume, and factory output can require different treatment. A date dimension can carry business day and holiday flags so the window is defined consistently across reports.

Partition by the Right Entity

A moving average for each store needs PARTITION BY StoreId. Without it, the first row of one store can include the last rows of another. That error can look plausible if the amounts are similar. Add all keys that define an independent series, such as product and region, after confirming the grain.

Avoid partitioning by a display name that can change. Use stable keys. If a store is renamed, its trend should usually remain one series. Join the name for presentation after calculating the window.

I compare a single entity’s result with a simple manual sample. Then I test a boundary where one entity ends and another begins. That check catches a missing partition clause quickly. A chart can hide the boundary, while a query result shows it.

Index for the Window Order

Window functions sort or scan data in partition and order sequence. An index on the partition keys and SalesDate can reduce sorting work for large tables. Include the value being averaged when appropriate, but measure write cost and index size. An index chosen for one report still has to be maintained by the load.

Inspect the actual execution plan and reads for representative date ranges. A filter that wraps SalesDate in a function can force broader scanning. Filter with a date range first when the report needs a subset, while ensuring the range includes earlier dates required to compute the opening window.

I check the requested reporting period and the required lookback separately. If a report starts January 1, a seven day average on that date needs late December input. Filtering January rows before calculating the window would give a short and misleading first value.

SELECT StoreId, SalesDate, SalesAmount
FROM dbo.DailySales
WHERE SalesDate >= DATEADD(day, -6, CONVERT(date, '2025-01-01'))
  AND SalesDate < CONVERT(date, '2025-02-01')
ORDER BY StoreId, SalesDate;

Validate What Moving Averages Say

Show the underlying daily value beside the moving average during development. A smooth line alone can hide missing data and a bad denominator. Check the beginning of the series, a gap, a duplicate date, and a store boundary. Those are the places where window logic changes meaning.

If the table is large and many reports request the same moving average, consider precomputing daily totals. The rolling average can still be calculated at query time over that smaller grain. Materialize the final average only when freshness and refresh logic justify it.

A moving average is useful because it removes noise. It can also remove clues if its frame is poorly defined. Put the frame in the measure name or report note, then verify it with a few rows you can reason through.

Related reading on this blog: The Four Window Functions You Will Actually Use and How to Find Running Total in SQL Server.

A moving average is not just an AVG clause, it is a definition of which days belong together.

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