Gaps and Islands: Find Consecutive Activity in SQL Server

An activity table records individual observations. A business question often asks for something different: how many uninterrupted days was a customer active, or where did a monitoring sequence stop? Solving that question requires defining continuity before selecting a window function. Adjacent rows are not necessarily adjacent days.

Normalize the unit of continuity

The example treats one calendar date per customer as one observation. Multiple events on the same date count once. Without that normalization, duplicate dates can distort row-number arithmetic and produce incorrect streak lengths. The source deliberately includes a duplicate day, a missing day, and another customer.

CREATE TABLE #Activity(CustomerId int, ActivityDate date);
INSERT #Activity VALUES
(1,'20230101'),(1,'20230102'),(1,'20230102'),
(1,'20230104'),(1,'20230105'),(2,'20230102');
;WITH days AS
(SELECT DISTINCT CustomerId, ActivityDate FROM #Activity),
previous AS
(SELECT *, LAG(ActivityDate) OVER
 (PARTITION BY CustomerId ORDER BY ActivityDate) AS PrevDate FROM days),
flags AS
(SELECT *, CASE WHEN DATEDIFF(day, PrevDate, ActivityDate)=1
 THEN 0 ELSE 1 END AS NewIsland FROM previous),
groups AS
(SELECT *, SUM(NewIsland) OVER
 (PARTITION BY CustomerId ORDER BY ActivityDate
  ROWS UNBOUNDED PRECEDING) AS IslandId FROM flags)
SELECT CustomerId, MIN(ActivityDate) AS StartDate,
 MAX(ActivityDate) AS EndDate, COUNT(*) AS ActiveDays
FROM groups GROUP BY CustomerId, IslandId
ORDER BY CustomerId, StartDate;

The first CTE removes duplicate customer-date pairs. LAG then identifies the previous distinct date within each customer. A flag marks the first row or any date that is not exactly one day after its predecessor. The running sum converts those flags into a group number, and the final aggregation calculates each island's start, end, and number of active days.

For customer 1, the expected islands are January 1 through January 2 and January 4 through January 5. Customer 2 has a separate one-day island. COUNT(*) now means days because the first stage established that grain. It would mean events if duplicates remained. The explicit ROWS frame makes the running-sum intent clear.

Make the business calendar explicit

Consecutive calendar days, business days, and consecutive observations are different definitions. A Friday followed by Monday breaks a calendar-day streak but may continue a business-day streak. For business days, join to a maintained calendar table with a consecutive business-day sequence number and compare that sequence instead of adding one calendar day.

When source events are timestamps, decide the reporting time zone before deriving dates. Converting UTC events to the business zone can move an event into the previous or next local date. Daylight-saving changes affect elapsed hours, so a 24-hour duration test is not equivalent to consecutive local calendar dates. Preserve the original timestamp for auditability.

Range filtering creates another boundary issue. If the report starts on February 1, an island visible at that boundary may have started in January. Decide whether to show a clipped interval or the complete streak. Finding the complete start requires additional history or a maintained prior-state summary, not merely a wider display label.

Know when this pattern does not apply

Overlapping intervals need a different comparison. Suppose intervals run from 1 to 10, 2 to 3, and 9 to 12. Comparing the third start only with the immediately preceding end incorrectly finds a gap. Interval merging must compare against the running maximum of all preceding ends, using the chosen inclusive or exclusive endpoint rule.

For the daily-event pattern, an index beginning with customer and activity date can support deduplication and ordering, but the real plan determines whether a sort remains. On large histories, inspect rows processed and memory spills. Filter customers early when the report concerns a small subset, while preserving enough history for the requested boundary semantics.

Validate with a single day, duplicate days, multiple customers, a missing day, and an island crossing the reporting boundary. Also test late-arriving events: inserting a previously missing date can merge two existing islands. Persisted island identifiers therefore are not automatically permanent business keys. The reliable result comes from a clear calendar contract and tests of those boundaries, not from the apparent elegance of the SQL.

Technical references: Microsoft Learn: LAG · Microsoft Learn: OVER.