Preparing Time Series Features in T-SQL: Lags and Rolling Windows

Three preceding rows are not always three preceding days. Reliable time series features need a defined calendar and explicit handling of missing observations.

Define the Time Grain Before Building Time Series Features

This example uses one observation per series per calendar day. The primary key enforces that daily grain. Multiple measurements within a day require an agreed aggregation before applying these feature calculations.

Calendar days and trading days describe different sequences. A weekend is a gap under one definition and expected absence under another. Choose the relevant calendar before defining continuity or rolling-window length.

I establish the target prediction time before writing window expressions. I also document which values were available at that time. A feature that uses later information can make an otherwise convincing model invalid.

The fixture includes a missing date and a NULL measurement. Both interrupt usable daily history in this demonstration. Those choices are explicit policies rather than universal rules for every forecasting application.

CREATE TABLE #DailySeries
(
    SeriesId int NOT NULL,
    ObservationDate date NOT NULL,
    ObservedValue decimal(12,2) NULL,
    PRIMARY KEY (SeriesId, ObservationDate)
);
INSERT #DailySeries VALUES
    (1,'20250101',10),(1,'20250102',12),
    (1,'20250104',18),(1,'20250105',NULL),
    (1,'20250106',20),(1,'20250107',22),
    (1,'20250108',21),(1,'20250109',25),
    (2,'20250101',100),(2,'20250102',105);

Break History Into Contiguous Islands

Remove unusable NULL measurements from the feature input under the chosen policy. Compare each retained date with its previous retained date. A difference other than one day starts a new continuity island.

That comparison detects both absent rows and dates removed for missing measurements. The island identifier then becomes part of every historical window partition. Windows cannot silently reach across a broken daily sequence.

Use separate query stages for these calculations. A window function cannot directly nest inside another window function in the same expression. CTEs make the dependency between previous date and running island count visible.

WITH Valid AS
(
    SELECT SeriesId, ObservationDate, ObservedValue
    FROM #DailySeries WHERE ObservedValue IS NOT NULL
), PreviousDate AS
(
    SELECT *, LAG(ObservationDate) OVER
        (PARTITION BY SeriesId ORDER BY ObservationDate) AS PreviousDate
    FROM Valid
), Breaks AS
(
    SELECT *, CASE WHEN PreviousDate IS NULL
                        OR DATEDIFF(day, PreviousDate, ObservationDate) <> 1
                   THEN 1 ELSE 0 END AS StartsIsland
    FROM PreviousDate
), Islands AS
(
    SELECT SeriesId, ObservationDate, ObservedValue,
           SUM(StartsIsland) OVER
           (PARTITION BY SeriesId ORDER BY ObservationDate
            ROWS UNBOUNDED PRECEDING) AS IslandId
    FROM Breaks
)
SELECT SeriesId, ObservationDate, ObservedValue, IslandId
INTO #SeriesIsland FROM Islands;
SELECT * FROM #SeriesIsland ORDER BY SeriesId, ObservationDate;

The unique daily key supplies an unambiguous ordering within each series. If duplicate timestamps are valid in another design, add a stable tie-breaker. Arbitrary peer order cannot establish a dependable previous observation.

An island identifier is local to this generated input. It is not a permanent business key for the series. Rebuilding an earlier portion of history can renumber later islands without changing their actual dates.

Calculate Lag and Prior-Only Window Time Series Features

LAG retrieves the previous value within the same series and island. The first row of each island therefore has no previous value. Leaving that result NULL preserves the boundary rather than pretending the prior value was zero.

The rolling frame uses three preceding rows and excludes the current row. On this contiguous daily input, those rows represent prior days. This frame is appropriate when predicting today's value before observing it.

WITH Features AS
(
    SELECT SeriesId, ObservationDate, ObservedValue, IslandId,
           LAG(ObservedValue) OVER
           (PARTITION BY SeriesId, IslandId ORDER BY ObservationDate) AS LagValue,
           AVG(ObservedValue) OVER
           (PARTITION BY SeriesId, IslandId ORDER BY ObservationDate
            ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS PriorAverage,
           MIN(ObservedValue) OVER
           (PARTITION BY SeriesId, IslandId ORDER BY ObservationDate
            ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS PriorMinimum,
           MAX(ObservedValue) OVER
           (PARTITION BY SeriesId, IslandId ORDER BY ObservationDate
            ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS PriorMaximum,
           COUNT(ObservedValue) OVER
           (PARTITION BY SeriesId, IslandId ORDER BY ObservationDate
            ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS PriorCount
    FROM #SeriesIsland
)
SELECT *, ObservedValue - LagValue AS DifferenceFromPrevious
INTO #SeriesFeature FROM Features;
SELECT * FROM #SeriesFeature ORDER BY SeriesId, ObservationDate;

The rolling count shows how much history actually contributes to each statistic. Early island rows can have fewer than three prior observations. Require PriorCount equal three when the model contract needs a complete three-day history.

ROWS frames count ordered rows rather than measuring elapsed time directly. The island logic makes that row interpretation valid here. Removing that logic changes the meaning even if the rolling expression remains identical.

Keep the Prediction Boundary Visible

A current-inclusive rolling average can be appropriate after today's measurement arrives. It is not available when predicting that same unobserved measurement. The permitted frame depends on when the application makes its decision.

DifferenceFromPrevious includes the current observed value. It therefore describes an observed change rather than a prior-only forecast input for that value. Shift or exclude this field when preparing training inputs for an earlier prediction time.

Keep labels and features in distinct roles during model preparation. The sample returns the observed value for inspection. That does not authorize using it as an input when predicting the same day's outcome.

Train and evaluate using chronological splits. Randomly mixing future rows into earlier training periods can distort validation. Match the evaluation process to the way new observations will arrive in deployment.

Add Calendar Fields Without Session Surprises

MONTH gives the calendar month for each observation date. Day-of-week calculations need an explicit convention. DATEPART weekday depends on DATEFIRST, so relying on an unknown session setting can change feature values.

The following arithmetic defines Monday as one using a known Monday anchor. Its remainder correction also handles dates before the anchor. A separate flag identifies the first day of a month.

SELECT SeriesId, ObservationDate,
       ((DATEDIFF(day, CONVERT(date, '20000103', 112), ObservationDate) % 7 + 7) % 7) + 1
           AS MondayBasedWeekday,
       MONTH(ObservationDate) AS MonthNumber,
       CASE WHEN DAY(ObservationDate) = 1 THEN 1 ELSE 0 END AS IsMonthStart,
       LagValue, PriorAverage, PriorMinimum, PriorMaximum,
       PriorCount, DifferenceFromPrevious
FROM #SeriesFeature
ORDER BY SeriesId, ObservationDate;

A date-only fixture avoids time-zone ambiguity in these examples. A real timestamp source needs conversion into the chosen business calendar first. Events near midnight can belong to different days under different reporting zones.

A model also needs a deliberate encoding for categorical calendar values. Treating weekday seven as numerically far from weekday one can misrepresent their cycle. The SQL field is a preparation step rather than a complete modeling decision.

Choose a Missing-Data Policy Deliberately

Breaking at missing dates is one defensible policy for time series features. Another builds a complete calendar and marks missing measurements explicitly. Forward filling or interpolation needs its own documented assumptions and availability rules.

Never use a future observation to fill an earlier feature without accounting for leakage. Interpolation can make an offline dataset look complete while using unavailable future information. Preserve missingness indicators when they help explain uncertainty.

Zero is a real measured value when the business definition permits it. NULL represents unavailable or unusable measurement under this contract. Replacing every missing value with zero changes both rolling statistics and model interpretation.

Test the first day, a missing day, a NULL day, and a series boundary. Each should have an explicit expected lag and history count. On my run, the gap on January 3 and the NULL on January 5 split series 1 into three islands. Only January 9 reached a full three-row history.

Validate Time Series Features as a Contract

Can today's prediction reproduce the features calculated during training without reading tomorrow's data? Compare the online and historical preparation logic at the same availability boundary. That check matters more than a convenient complete table.

I keep time series features tied to their calendar and prediction point. I review every frame whenever the source grain changes. A missing Tuesday should not borrow Wednesday's explanation without telling anyone.

Index persistent source tables according to their series and date access pattern. Review actual plans and memory needs on representative history volumes. Correct window semantics remain essential even when the query runs quickly.

Record the gap policy, minimum history, units, and feature version beside the query. Recheck them when ingestion delay or calendar rules change. A reproducible feature calculation is useful only when its meaning remains consistent.

Related reading on this blog: A Walkthrough: DATETRUNC Function in SQL Server and T-SQL Window Function Framing and Performance: Notes from the Field #103.

A rolling frame is not automatically a calendar window, it is ordered history governed by explicit continuity rules.

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