SQL Server’s window functions open up some creative possibilities when you need to work with dates. One interesting pattern involves pairing DATEADD() with ROW_NUMBER() to automatically generate sequential dates based on your query results.
This technique gives you a flexible way to calculate dates dynamically without hardcoding values or maintaining separate date tables. This can be useful for doing things like building a scheduling system, creating test data with realistic timestamps, or just spacing events across a timeline.
Example
Let’s work through an example to demonstrate the concept. We’ll be using a dataset of wildlife rescue operations. We’ll track rescued animals and assign them sequential rehabilitation appointment dates based on their rescue priority.
Setting Up the Sample Data
First, here’s a script to create and populate our test tables. This creates a simple database structure for tracking wildlife rescues across different facilities:
-- Create the tables
CREATE TABLE RescueFacility (
FacilityID INT PRIMARY KEY,
FacilityName VARCHAR(100),
Region VARCHAR(50)
);
CREATE TABLE AnimalRescue (
RescueID INT PRIMARY KEY,
FacilityID INT,
AnimalSpecies VARCHAR(50),
RescueDate DATE,
SeverityLevel INT,
RecoveryDays INT,
FOREIGN KEY (FacilityID) REFERENCES RescueFacility(FacilityID)
);
-- Populate with sample data
INSERT INTO RescueFacility (FacilityID, FacilityName, Region)
VALUES
(1, 'Coastal Wildlife Center', 'Pacific'),
(2, 'Mountain Animal Sanctuary', 'Rockies'),
(3, 'Desert Rescue Haven', 'Southwest'),
(4, 'Forest Creek Rehabilitation', 'Northeast');
INSERT INTO AnimalRescue (RescueID, FacilityID, AnimalSpecies, RescueDate, SeverityLevel, RecoveryDays)
VALUES
(101, 1, 'Sea Otter', '2024-03-15', 3, 45),
(102, 2, 'Red Fox', '2024-03-18', 2, 30),
(103, 1, 'Harbor Seal', '2024-03-20', 4, 60),
(104, 3, 'Desert Tortoise', '2024-03-22', 1, 90),
(105, 4, 'Barred Owl', '2024-03-25', 3, 40),
(106, 2, 'Black Bear Cub', '2024-03-28', 5, 120),
(107, 3, 'Roadrunner', '2024-04-01', 2, 25),
(108, 4, 'Red-tailed Hawk', '2024-04-05', 3, 35);The Query in Action
Now for the actual query. We want to schedule follow-up appointments for each rescued animal, starting from today and spacing them one week apart. The appointments should be ordered by severity level (most critical first) and then by how long the animal has been in recovery.
Here’s how we could do that:
SELECT
ar.AnimalSpecies,
rf.FacilityName,
ar.SeverityLevel,
DATEADD(week,
ROW_NUMBER() OVER (ORDER BY ar.SeverityLevel DESC, ar.RecoveryDays DESC) - 1,
CAST(GETDATE() AS DATE)) AS NextAppointment
FROM AnimalRescue ar
INNER JOIN RescueFacility rf ON ar.FacilityID = rf.FacilityID
WHERE ar.RecoveryDays > 0;Result:
AnimalSpecies FacilityName SeverityLevel NextAppointment
--------------- --------------------------- ------------- ---------------
Black Bear Cub Mountain Animal Sanctuary 5 2025-11-20
Harbor Seal Coastal Wildlife Center 4 2025-11-27
Sea Otter Coastal Wildlife Center 3 2025-12-04
Barred Owl Forest Creek Rehabilitation 3 2025-12-11
Red-tailed Hawk Forest Creek Rehabilitation 3 2025-12-18
Red Fox Mountain Animal Sanctuary 2 2025-12-25
Roadrunner Desert Rescue Haven 2 2026-01-01
Desert Tortoise Desert Rescue Haven 1 2026-01-08
8 row(s) returned
This query does several things worth noting. The ROW_NUMBER() window function assigns a sequential number to each row based on our ordering criteria. We’re sorting by severity level in descending order (most severe cases first) and then by recovery days (animals with longer recovery times get priority within the same severity level).
The DATEADD() function then takes that row number and adds weeks to today’s date. I subtracted 1 from the row number so the first appointment starts today rather than next week (I ran this on 2025-11-20). Each subsequent animal gets an appointment one week later than the previous one.
Why This Approach Works
The combination of DATEADD() and ROW_NUMBER() is ideal for when you need to spread events across time in a deterministic way. Unlike just adding a fixed interval to a date column in your table, this approach calculates the intervals based on the current result set and your specified ordering.
You might use this pattern for scheduling recurring maintenance tasks, distributing workload across team members over time, or creating staggered notification schedules. The main advantage is that the date calculation happens dynamically based on your query results rather than requiring stored date values.
One thing to watch for is, if you change the ORDER BY clause in your window function, you’ll get completely different date assignments. The row numbers recalculate based on that ordering, which then affects every date in your results. This makes sense logically but can trip you up if you’re not paying attention to your sorting criteria.
The query also uses CAST(GETDATE() AS DATE) to strip the time component from the current datetime, giving us a clean starting point for date calculations. This prevents our appointment times from having random hours and minutes attached to them.