SQL Server’s window functions allow you to perform calculations across sets of rows that are related to the current row, without collapsing those rows into a single result like traditional GROUP BY aggregates would. When combined with the DATEDIFF() function, they provide a powerful way to analyze temporal patterns in your data.
One potential use case is calculating running totals of time durations. Unlike simple aggregates that give you a single summary value, running totals show you the cumulative duration at each point in a sequence. This can be invaluable for tracking accumulated processing time, measuring cumulative delays, or understanding how total duration builds up over a series of events.
The mechanism behind running totals with window functions is the frame clause in the OVER specification. By using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, you tell SQL Server to sum all values from the beginning of the partition up to and including the current row, creating that cumulative effect.
Example
Let’s say you’re analyzing a manufacturing process where each product goes through multiple sequential steps, and each step takes a different amount of time. You want to see not just how long each step took, but also the cumulative time invested in the product at each stage. This helps identify at what point in the process you’ve consumed most of your time budget.
Sample Data
Here’s a script to set up some sample data for our example:
-- Create the production steps table
CREATE TABLE ProductionSteps (
StepID INT PRIMARY KEY IDENTITY(1,1),
ProductBatch VARCHAR(20),
StepName VARCHAR(100),
StepStartTime DATETIME,
StepEndTime DATETIME
);
-- Insert sample data for two product batches going through manufacturing
INSERT INTO ProductionSteps (ProductBatch, StepName, StepStartTime, StepEndTime)
VALUES
('BATCH-A401', 'Material Preparation', '2025-11-20 08:00:00', '2025-11-20 09:30:00'),
('BATCH-A401', 'Initial Assembly', '2025-11-20 09:45:00', '2025-11-20 11:20:00'),
('BATCH-A401', 'Quality Check 1', '2025-11-20 11:30:00', '2025-11-20 12:00:00'),
('BATCH-A401', 'Secondary Assembly', '2025-11-20 13:00:00', '2025-11-20 15:45:00'),
('BATCH-A401', 'Final Inspection', '2025-11-20 16:00:00', '2025-11-20 16:40:00'),
('BATCH-B208', 'Material Preparation', '2025-11-20 08:30:00', '2025-11-20 10:15:00'),
('BATCH-B208', 'Initial Assembly', '2025-11-20 10:30:00', '2025-11-20 12:45:00'),
('BATCH-B208', 'Quality Check 1', '2025-11-20 13:00:00', '2025-11-20 13:25:00'),
('BATCH-B208', 'Secondary Assembly', '2025-11-20 13:40:00', '2025-11-20 16:10:00'),
('BATCH-B208', 'Final Inspection', '2025-11-20 16:25:00', '2025-11-20 17:10:00');Calculating the Running Total
Now here’s a query that calculates the running total:
SELECT ProductBatch,
StepName,
StepStartTime,
StepEndTime,
DATEDIFF(minute, StepStartTime, StepEndTime) AS StepDurationMinutes,
SUM(DATEDIFF(minute, StepStartTime, StepEndTime))
OVER (PARTITION BY ProductBatch
ORDER BY StepStartTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeMinutes
FROM ProductionSteps
ORDER BY ProductBatch, StepStartTime;Result:
ProductBatch StepName StepStartTime StepEndTime StepDurationMinutes CumulativeMinutes
------------ -------------------- ------------------------ ------------------------ ------------------- -----------------
BATCH-A401 Material Preparation 2025-11-20T08:00:00.000Z 2025-11-20T09:30:00.000Z 90 90
BATCH-A401 Initial Assembly 2025-11-20T09:45:00.000Z 2025-11-20T11:20:00.000Z 95 185
BATCH-A401 Quality Check 1 2025-11-20T11:30:00.000Z 2025-11-20T12:00:00.000Z 30 215
BATCH-A401 Secondary Assembly 2025-11-20T13:00:00.000Z 2025-11-20T15:45:00.000Z 165 380
BATCH-A401 Final Inspection 2025-11-20T16:00:00.000Z 2025-11-20T16:40:00.000Z 40 420
BATCH-B208 Material Preparation 2025-11-20T08:30:00.000Z 2025-11-20T10:15:00.000Z 105 105
BATCH-B208 Initial Assembly 2025-11-20T10:30:00.000Z 2025-11-20T12:45:00.000Z 135 240
BATCH-B208 Quality Check 1 2025-11-20T13:00:00.000Z 2025-11-20T13:25:00.000Z 25 265
BATCH-B208 Secondary Assembly 2025-11-20T13:40:00.000Z 2025-11-20T16:10:00.000Z 150 415
BATCH-B208 Final Inspection 2025-11-20T16:25:00.000Z 2025-11-20T17:10:00.000Z 45 460
This query shows each production step’s duration and the running total of time invested at each stage. We can see that on BATCH-A401 for example, the Material Preparation step shows 90 minutes for both the step duration and cumulative time (since it’s first). Initial Assembly shows 95 minutes duration, but 185 cumulative minutes because it includes the previous step’s time. And so on.
Understanding the Running Total Mechanism
The main functionality of the running total happens in the SUM() window function with its frame specification. The PARTITION BY ProductBatch ensures each batch has its own independent running total. The ORDER BY StepStartTime establishes the sequence for accumulation. The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW tells SQL Server to include all rows from the start of the partition up to the current row in the sum.
Without the frame clause, SQL Server would sum across the entire partition for every row, giving you the same total duration for each step. The frame clause is what creates the cumulative, step-by-step buildup you want for a running total.
Combining Multiple Time Metrics
You can extend this pattern to track both active processing time and total elapsed time (including gaps between steps):
SELECT ProductBatch,
StepName,
StepStartTime,
StepEndTime,
DATEDIFF(minute, StepStartTime, StepEndTime) AS StepDurationMinutes,
DATEDIFF(minute,
LAG(StepEndTime) OVER (PARTITION BY ProductBatch ORDER BY StepStartTime),
StepStartTime) AS GapBeforeStepMinutes,
SUM(DATEDIFF(minute, StepStartTime, StepEndTime))
OVER (PARTITION BY ProductBatch
ORDER BY StepStartTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeProcessingMinutes,
DATEDIFF(minute,
MIN(StepStartTime) OVER (PARTITION BY ProductBatch),
StepEndTime) AS TotalElapsedMinutes
FROM ProductionSteps
ORDER BY ProductBatch, StepStartTime;Result:
ProductBatch StepName StepStartTime StepEndTime StepDurationMinutes GapBeforeStepMinutes CumulativeProcessingMinutes TotalElapsedMinutes
------------ -------------------- ------------------------ ------------------------ ------------------- -------------------- --------------------------- -------------------
BATCH-A401 Material Preparation 2025-11-20T08:00:00.000Z 2025-11-20T09:30:00.000Z 90 null 90 90
BATCH-A401 Initial Assembly 2025-11-20T09:45:00.000Z 2025-11-20T11:20:00.000Z 95 15 185 200
BATCH-A401 Quality Check 1 2025-11-20T11:30:00.000Z 2025-11-20T12:00:00.000Z 30 10 215 240
BATCH-A401 Secondary Assembly 2025-11-20T13:00:00.000Z 2025-11-20T15:45:00.000Z 165 60 380 465
BATCH-A401 Final Inspection 2025-11-20T16:00:00.000Z 2025-11-20T16:40:00.000Z 40 15 420 520
BATCH-B208 Material Preparation 2025-11-20T08:30:00.000Z 2025-11-20T10:15:00.000Z 105 null 105 105
BATCH-B208 Initial Assembly 2025-11-20T10:30:00.000Z 2025-11-20T12:45:00.000Z 135 15 240 255
BATCH-B208 Quality Check 1 2025-11-20T13:00:00.000Z 2025-11-20T13:25:00.000Z 25 15 265 295
BATCH-B208 Secondary Assembly 2025-11-20T13:40:00.000Z 2025-11-20T16:10:00.000Z 150 15 415 460
BATCH-B208 Final Inspection 2025-11-20T16:25:00.000Z 2025-11-20T17:10:00.000Z 45 15 460 520
Now you can see both the cumulative active work time and the total elapsed time from when the batch started to when each step completed. The difference between these reveals how much time is spent in gaps or waiting between steps.
Finding Bottlenecks with Running Percentages
Running totals can be even more useful when you convert them to percentages. This can show you what proportion of total time has been consumed at each stage:
WITH StepDurations AS (
SELECT ProductBatch,
StepName,
StepStartTime,
StepEndTime,
DATEDIFF(minute, StepStartTime, StepEndTime) AS StepDurationMinutes,
SUM(DATEDIFF(minute, StepStartTime, StepEndTime))
OVER (PARTITION BY ProductBatch) AS TotalBatchMinutes
FROM ProductionSteps
)
SELECT ProductBatch,
StepName,
StepDurationMinutes,
SUM(StepDurationMinutes)
OVER (PARTITION BY ProductBatch
ORDER BY StepStartTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeMinutes,
TotalBatchMinutes,
CAST(SUM(StepDurationMinutes)
OVER (PARTITION BY ProductBatch
ORDER BY StepStartTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) * 100.0 / TotalBatchMinutes
AS DECIMAL(5,2)) AS PercentComplete
FROM StepDurations
ORDER BY ProductBatch, StepStartTime;Result:
ProductBatch StepName StepDurationMinutes CumulativeMinutes TotalBatchMinutes PercentComplete
------------ -------------------- ------------------- ----------------- ----------------- ---------------
BATCH-A401 Material Preparation 90 90 420 21.43
BATCH-A401 Initial Assembly 95 185 420 44.05
BATCH-A401 Quality Check 1 30 215 420 51.19
BATCH-A401 Secondary Assembly 165 380 420 90.48
BATCH-A401 Final Inspection 40 420 420 100
BATCH-B208 Material Preparation 105 105 460 22.83
BATCH-B208 Initial Assembly 135 240 460 52.17
BATCH-B208 Quality Check 1 25 265 460 57.61
BATCH-B208 Secondary Assembly 150 415 460 90.22
BATCH-B208 Final Inspection 45 460 460 100
Comparing Running Totals Across Different Partitions
You can use multiple running totals with different partition schemes to gain different perspectives on your data:
SELECT ProductBatch,
StepName,
StepStartTime,
DATEDIFF(minute, StepStartTime, StepEndTime) AS StepDurationMinutes,
SUM(DATEDIFF(minute, StepStartTime, StepEndTime))
OVER (PARTITION BY ProductBatch
ORDER BY StepStartTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeByBatch,
SUM(DATEDIFF(minute, StepStartTime, StepEndTime))
OVER (ORDER BY StepStartTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeAllBatches
FROM ProductionSteps
ORDER BY StepStartTime;Result:
ProductBatch StepName StepStartTime StepDurationMinutes CumulativeByBatch CumulativeAllBatches
------------ -------------------- ------------------------ ------------------- ----------------- --------------------
BATCH-A401 Material Preparation 2025-11-20T08:00:00.000Z 90 90 90
BATCH-B208 Material Preparation 2025-11-20T08:30:00.000Z 105 105 195
BATCH-A401 Initial Assembly 2025-11-20T09:45:00.000Z 95 185 290
BATCH-B208 Initial Assembly 2025-11-20T10:30:00.000Z 135 240 425
BATCH-A401 Quality Check 1 2025-11-20T11:30:00.000Z 30 215 455
BATCH-A401 Secondary Assembly 2025-11-20T13:00:00.000Z 165 380 620
BATCH-B208 Quality Check 1 2025-11-20T13:00:00.000Z 25 265 645
BATCH-B208 Secondary Assembly 2025-11-20T13:40:00.000Z 150 415 795
BATCH-A401 Final Inspection 2025-11-20T16:00:00.000Z 40 420 835
BATCH-B208 Final Inspection 2025-11-20T16:25:00.000Z 45 460 880
The first running total shows accumulation within each individual batch, while the second shows the cumulative processing time across all batches in chronological order. This helps you see both individual batch progress and overall facility utilization over time.
Quick Summary
Running totals with DATEDIFF() provide insights that simple aggregates cannot. They let you see how duration accumulates progressively through a process, identify at what point you’ve consumed most of your time budget, and compare the pace of accumulation across different groups or sequences.