When you combine SQL Server’s aggregate functions like MIN() and MAX() with the OVER clause, you can use them as window functions that calculate across partitions while still maintaining individual row data. When combined with DATEDIFF(), it lets you calculate how much time has elapsed from a baseline date within each partition. This can be useful for doing stuff like measuring durations from the start of a process, tracking time since the first event in a group, or calculating age from an initial reference point.
The main advantage of using MIN() or MAX() as a window function is that you can compare every row in a partition against the earliest or latest date in that same partition without needing a self-join or subquery. Each row gets access to the aggregate value while still maintaining its individual row data.
Example
Let’s say you’re analyzing order fulfillment and want to know how many days have passed since the first item in each order was processed. Even though an order might have multiple line items processed on different dates, you want to measure each item’s processing time relative to when processing began for that order.
Sample Data
Here’s a script to set up sample data for the example:
-- Create the orders table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerName VARCHAR(100),
OrderDate DATETIME,
ShippingMethod VARCHAR(50)
);
-- Create the order items table
CREATE TABLE OrderItems (
OrderItemID INT PRIMARY KEY IDENTITY(1,1),
OrderID INT,
ProductName VARCHAR(100),
Quantity INT,
UnitPrice DECIMAL(10,2)
);
-- Insert sample orders
INSERT INTO Orders (OrderID, CustomerName, OrderDate, ShippingMethod)
VALUES
(5001, 'Stellar Manufacturing', '2025-11-10 09:15:00', 'Express'),
(5002, 'Pacific Distributors', '2025-11-12 14:30:00', 'Standard');
-- Insert order items for multiple products per order
INSERT INTO OrderItems (OrderID, ProductName, Quantity, UnitPrice)
VALUES
(5001, 'Industrial Sensor Array', 3, 425.00),
(5001, 'Mounting Bracket Kit', 6, 38.50),
(5001, 'Calibration Tool', 2, 189.00),
(5002, 'Hydraulic Pump', 1, 1250.00),
(5002, 'Pressure Gauge', 4, 67.50),
(5002, 'Connection Hose 10m', 2, 45.00),
(5002, 'Fitting Set', 8, 12.75);The Query
Now here’s where the window aggregate function comes in:
SELECT o.OrderID,
oi.ProductName,
oi.Quantity,
oi.ItemProcessedDate,
DATEDIFF(day,
MIN(oi.ItemProcessedDate) OVER (PARTITION BY o.OrderID),
oi.ItemProcessedDate) AS DaysSinceFirstItemProcessed
FROM OrderItems AS oi
INNER JOIN Orders AS o
ON oi.OrderID = o.OrderID
WHERE o.OrderID IN (5001, 5002);Result:
OrderID ProductName Quantity ItemProcessedDate DaysSinceFirstItemProcessed
------- ----------------------- -------- ----------------- ---------------------------
5001 Industrial Sensor Array 3 2025-11-10 0
5001 Mounting Bracket Kit 6 2025-11-11 1
5001 Calibration Tool 2 2025-11-13 3
5002 Hydraulic Pump 1 2025-11-12 0
5002 Pressure Gauge 4 2025-11-13 1
5002 Connection Hose 10m 2 2025-11-14 2
5002 Fitting Set 8 2025-11-15 3
This query shows each line item from the orders along with how many days have passed since the first item in that order was processed. The first item in order 5001 (Industrial Sensor Array) shows 0 days because it’s the baseline, while the Mounting Bracket Kit shows 1 day and the Calibration Tool shows 3 days – reflecting when each was processed relative to the first item.
Understanding the Window Aggregate
The MIN(oi.ItemProcessedDate) OVER (PARTITION BY o.OrderID) expression is our main focus. For each row, it calculates the minimum ItemProcessedDate within that order’s partition. This gives every line item access to when the first item in their order was processed, allowing DATEDIFF() to measure how many days elapsed between the first item and each subsequent item.
Using MAX() for Future Reference Points
You can flip the pattern using MAX() to compare against the latest date in a partition. This is useful for measuring how far each event is from the end of a process:
SELECT o.OrderID,
oi.ProductName,
oi.ItemProcessedDate,
DATEDIFF(day,
oi.ItemProcessedDate,
MAX(oi.ItemProcessedDate) OVER (PARTITION BY o.OrderID)) AS DaysBeforeFinalItem
FROM OrderItems AS oi
INNER JOIN Orders AS o
ON oi.OrderID = o.OrderID
WHERE o.OrderID IN (5001, 5002)
ORDER BY o.OrderID, oi.ItemProcessedDate;Result:
OrderID ProductName ItemProcessedDate DaysBeforeFinalItem
------- ----------------------- ----------------- -------------------
5001 Industrial Sensor Array 2025-11-10 3
5001 Mounting Bracket Kit 2025-11-11 2
5001 Calibration Tool 2025-11-13 0
5002 Hydraulic Pump 2025-11-12 3
5002 Pressure Gauge 2025-11-13 2
5002 Connection Hose 10m 2025-11-14 1
5002 Fitting Set 2025-11-15 0
This shows how many days before the last item each item was processed. The final item in each order shows 0 days, while earlier items show positive values indicating they were processed earlier.
Alternative Approach Using a Subquery
While the window function approach is concise, you can achieve the same result using a correlated subquery:
SELECT o.OrderID,
oi.ProductName,
oi.Quantity,
oi.ItemProcessedDate,
DATEDIFF(day,
(SELECT MIN(ItemProcessedDate)
FROM OrderItems
WHERE OrderID = oi.OrderID),
oi.ItemProcessedDate) AS DaysSinceFirstItemProcessed
FROM OrderItems AS oi
INNER JOIN Orders AS o
ON oi.OrderID = o.OrderID
WHERE o.OrderID IN (5001, 5002);This produces identical results but executes the subquery for each row, whereas the window function calculates the minimum once per partition. For simple queries like this, the performance difference is negligible. However, if you need multiple aggregate calculations (like both MIN() and MAX() dates, or calculations across different partitions), the window function approach becomes cleaner since you can add additional window expressions without nesting multiple subqueries. The choice often comes down to readability and whether you’re already using other window functions in the same query.