A Simple Linear Regression Trend Line in T-SQL

Rising monthly totals do not necessarily form a dependable straight trend. Simple linear regression describes that line and gives you a way to inspect its fit.

Define the Observation before the Formula

This example fits monthly sales totals against elapsed month number. Each retained month contributes one observation with equal weight. That definition differs from fitting individual transactions or weighting months by their transaction counts.

Choose a consistent sales definition before aggregating the source. Returns, cancellations, currency, and accounting cutoff rules affect the resulting totals. A mathematically correct line cannot repair inconsistent input definitions.

I check the monthly input before reviewing the fitted slope. I also keep missing periods separate from true zero sales. Those two conditions tell different business stories and should not be exchanged casually.

The fixture below uses selected monthly values in an isolated session. It demonstrates the calculation rather than reporting observed business results. A missing value is included to make the retained-pair rule explicit.

CREATE TABLE #MonthlySales
(
    MonthStart date NOT NULL PRIMARY KEY,
    SalesAmount decimal(14,2) NULL
);
INSERT #MonthlySales VALUES
    ('20250101',100),('20250201',120),('20250301',115),
    ('20250401',145),('20250501',150),('20250601',170),
    ('20250701',NULL);

DECLARE @Origin date = '20250101';
SELECT MonthStart,
       CONVERT(float, DATEDIFF(month, @Origin, MonthStart)) AS X,
       CONVERT(float, SalesAmount) AS Y
INTO #SalesPairs
FROM #MonthlySales
WHERE SalesAmount IS NOT NULL;

Use Elapsed Time Rather than Surviving Row Order

Elapsed month number preserves the distance between calendar periods. A missing March should not make April look adjacent to February. ROW_NUMBER over retained rows would erase that elapsed-time gap.

The chosen origin makes January's X equal zero. Moving the origin changes the intercept while leaving the same fitted relationship. Document the origin so readers can interpret the intercept correctly.

Require each MonthStart to represent the intended month boundary. The primary key prevents duplicate months in this fixture. Production aggregation must also establish one compatible observation per period before fitting.

NULL sales are excluded from both the X and Y calculations together. Applying different filters to separate sums would mix incompatible populations. Count only complete pairs for the regression formula.

Calculate the Linear Regression Slope and Intercept

For n paired observations, slope divides the adjusted X-Y sum by the adjusted squared-X sum. The intercept then subtracts slope times mean X from mean Y. Floating-point conversion prevents integer division from truncating the calculation.

At least two observations with distinct X values are required. An all-identical X population has no usable slope denominator. Treat that as an undefined model rather than manufacturing a zero slope.

DECLARE @N bigint, @SumX float, @SumY float;
DECLARE @SumXX float, @SumXY float, @Denominator float;
DECLARE @Slope float, @Intercept float, @MeanY float;
SELECT @N = COUNT_BIG(*), @SumX = SUM(X), @SumY = SUM(Y),
       @SumXX = SUM(X * X), @SumXY = SUM(X * Y)
FROM #SalesPairs;

IF @N < 2 THROW 50001, 'At least two complete observations are required.', 1;
SET @Denominator = CONVERT(float, @N) * @SumXX - @SumX * @SumX;
IF @Denominator = 0
    THROW 50002, 'The time values have no variation.', 1;

SET @Slope = (CONVERT(float, @N) * @SumXY - @SumX * @SumY)
             / @Denominator;
SET @MeanY = @SumY / CONVERT(float, @N);
SET @Intercept = @MeanY - @Slope * (@SumX / CONVERT(float, @N));

SELECT @N AS ObservationCount, @Slope AS SlopePerMonth,
       @Intercept AS InterceptAtOrigin;
SELECT MonthStart, Y AS ActualAmount,
       @Intercept + @Slope * X AS PredictedAmount,
       Y - (@Intercept + @Slope * X) AS Residual
FROM #SalesPairs ORDER BY MonthStart;

SELECT @Slope AS Slope, @Intercept AS Intercept, @MeanY AS MeanY
INTO #TrendModel;

The slope's unit is amount per elapsed month. The intercept is the fitted amount at the chosen origin. Neither number identifies a causal reason for changes in sales.

Float is approximate arithmetic and needs careful interpretation. Large uncentered values can suffer cancellation when subtracting similar large sums. Keeping X near the origin helps, but it is not a complete numerical stability guarantee.

For more demanding calculations, center X and Y around their means before summing cross-products. Slope then divides the centered cross-product sum by centered squared-X variation. Retain precision and stability tests appropriate to the input scale.

Compute Linear Regression R-Squared from the Residuals

R-squared compares unexplained squared error with total variation around mean Y. For this ordinary least-squares line with an intercept, use one minus their ratio. It describes in-sample fit rather than future prediction accuracy.

A constant Y population has zero total variation. That makes this ratio undefined even when the fitted line reproduces every value. Return NULL for that fit statistic instead of dividing by zero.

WITH Errors AS
(
    SELECT SUM(POWER(P.Y - (M.Intercept + M.Slope * P.X), 2)) AS SSE,
           SUM(POWER(P.Y - M.MeanY, 2)) AS SST
    FROM #SalesPairs AS P
    CROSS JOIN #TrendModel AS M
)
SELECT SSE AS ResidualSquaredError,
       SST AS TotalSquaredVariation,
       CASE WHEN SST = 0 THEN NULL ELSE 1.0 - SSE / SST END AS RSquared
FROM Errors;

A higher value means the line explains more variation within the fitted sample. It does not establish business usefulness on its own. A tiny sample or changing process can still produce an attractive fit.

Small floating-point deviations near zero or one deserve numerical review. Do not silently conceal large invalid values with an automatic clamp. Check the formula, retained population, and arithmetic scale before presenting the statistic.

Inspect What the Line Leaves Behind

Residuals show each observed amount minus its fitted amount. Look for runs of positive or negative residuals across time. A systematic shape suggests the straight line misses structure in the data.

Seasonality is one common source of that structure. A yearly sales cycle is not captured by a single straight slope. Promotions, closures, and reporting-policy changes can also affect the apparent trend.

Outliers deserve business explanation before removal. One unusual month can pull the fitted slope substantially. Compare a reviewed alternative fit only after documenting why any observation is excluded.

With these sample months, the slope is about 13.43 per month and the intercept is about 99.76. R-squared comes out near 0.93, and the illustrative July value is about 180.33. Validate NULL, constant-sales, and insufficient-observation cases as separate tests.

Keep Linear Regression Extrapolation Modest

Linear regression can produce a value for any supplied X. That mathematical convenience does not establish that a distant future month follows the same process. Limit extrapolation to a justified horizon with explicit uncertainty.

The following query computes one next-month point from the fitted model. It is an illustration rather than an approved forecast. A real forecast also requires evaluation against periods excluded from fitting.

SELECT DATEADD(month, CONVERT(int, MAX(P.X)) + 1,
                      CONVERT(date, '20250101', 112)) AS NextMonth,
       M.Intercept + M.Slope * (MAX(P.X) + 1) AS IllustrativeAmount
FROM #SalesPairs AS P
CROSS JOIN #TrendModel AS M
GROUP BY M.Intercept, M.Slope;

A fitted sales line can eventually predict negative amounts under a declining slope. That result signals the limits of the model's application. Replacing it with zero without explanation changes the forecast rule.

Keep the origin consistent in any future-point query. Changing its date while reusing the old intercept produces the wrong prediction. Store model metadata together with the coefficients when a calculation leaves the session.

Decide What the Trend Should Support

Would the same line still be useful after a change in sales policy? Identify the assumptions before using its slope for a decision. An explicit review date helps prevent a convenient old model from becoming permanent authority.

I use linear regression as a compact description before making forecast claims. I review the input, residuals, and holdout behavior together. A straight line does not become a crystal ball because SQL calculated it.

Record the fitted period, observation count, units, and treatment of missing months. Keep the metric definition beside the query. Those details allow another reader to reproduce and question the interpretation responsibly.

The line gives a useful first view of direction and fit. More complex models need evidence that their added assumptions improve the intended decision. Start with a transparent calculation and keep its practical limits visible.

Related reading on this blog: Moving Averages in T-SQL and Approximate Percentiles With APPROX_PERCENTILE_CONT.

A trend line is not a guaranteed forecast, it is a fitted description of a defined set of observations.

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