Gaps and Islands: Finding Missing Ranges in a Sequence

A sequence report needs to show both the values that are absent and the runs that remain consecutive. The gaps and islands pattern answers those two questions with explicit boundaries and ordered rows.

Define the Sequence Before Hunting Gaps and Islands

A gap exists relative to a rule: integer increments of one, calendar days, business days, or another accepted sequence. Define that rule and its outer boundaries first. Missing weekends are correct in a business-day schedule and incorrect in a daily one. The same stored rows can therefore produce different valid reports.

I ask for the expected sequence before writing the window function. That prevents a clever query from finding gaps nobody considers missing. Identity values also permit gaps after failures and other normal activity, so absence in an identity sequence does not automatically indicate deleted business data.

Use the following small temporary tables as independent examples. Their values are synthetic inputs for explaining the patterns. They are not observations about an application's actual attendance or data quality. Keep source duplicates and NULL values under an explicit policy before treating rows as distinct members of a sequence.

Find Internal Integer Gaps With LEAD

LEAD exposes the next stored ID in the chosen order. If that next value is more than one step away, the missing range begins after the current ID and ends before the next. Cast to bigint for the calculation so incrementing an int boundary does not overflow an otherwise valid stored value.

CREATE TABLE #SequenceValues (SequenceID int NOT NULL PRIMARY KEY);
INSERT #SequenceValues VALUES (1),(2),(5),(6),(9);
WITH Neighbors AS
(
    SELECT CONVERT(bigint,SequenceID) AS CurrentID,
           LEAD(CONVERT(bigint,SequenceID)) OVER (ORDER BY SequenceID) AS NextID
    FROM #SequenceValues
)
SELECT CurrentID+1 AS MissingStart,NextID-1 AS MissingEnd
FROM Neighbors
WHERE NextID>CurrentID+1;

This returns ranges rather than generating one row for every absent number. That is useful when a large gap would otherwise produce an enormous output. The final row has no next neighbor, so it cannot establish a trailing gap by itself. Likewise, the first stored value does not establish how far the expected sequence extends backward.

Handle Leading and Trailing Boundaries

Supply an expected minimum and maximum when the report needs complete bounded coverage. Validate that the lower bound does not exceed the upper bound and decide how out-of-range source values should be treated. A sequence with no stored values inside the requested range is one entirely missing range, rather than an empty gap report.

For modest integer domains, a generated expected series followed by an anti-join is a simple bounded alternative. For a large domain, keep the range-based approach and add boundary comparisons instead of expanding every absent value. Choose the output shape from the actual reporting need, not from whichever demonstration query is easiest to paste.

The gaps and islands label describes a family of ordered comparisons. It does not choose the correct domain, permitted gaps, or reporting grain for you. Keep those decisions beside the SQL so a reviewer understands what absence means in this specific report.

Create Distinct Daily Login Inputs

A login streak normally counts days with activity rather than individual login events. Reduce timestamps to the agreed calendar-day convention and deduplicate each user's day before grouping. If the application spans time zones, establish which zone defines that date. Converting every UTC timestamp directly to date can shift the intended local attendance boundary.

CREATE TABLE #LoginDays
(
    UserID int NOT NULL,
    LoginDay date NOT NULL,
    PRIMARY KEY (UserID,LoginDay)
);
INSERT #LoginDays VALUES
(1,'2026-09-20'),(1,'2026-09-21'),(1,'2026-09-23'),
(1,'2026-09-24'),(1,'2026-09-25'),
(2,'2026-09-21'),(2,'2026-09-22'),(2,'2026-09-24');

The primary key makes the daily grain explicit. If the real source has repeated logins, use a DISTINCT user-and-day preparation step before applying the row numbering. Duplicate days would otherwise change the sequence number without advancing the calendar, incorrectly splitting or merging the intended runs.

Build Date Islands Between the Gaps

The day ordinal advances by one on successive dates, and ROW_NUMBER advances by one on successive rows. Their difference remains constant within a consecutive run. Partition row numbering by user so one person's attendance cannot connect with another person's dates. Then group by the user and that calculated island identifier.

WITH Numbered AS
(
    SELECT UserID,LoginDay,
           DATEDIFF(DAY,DATEFROMPARTS(2000,1,1),LoginDay)
           - ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY LoginDay) AS IslandID
    FROM #LoginDays
)
SELECT UserID,MIN(LoginDay) AS StreakStart,
       MAX(LoginDay) AS StreakEnd,COUNT_BIG(*) AS LoginDays
FROM Numbered
GROUP BY UserID,IslandID
ORDER BY UserID,StreakStart;

The reference date supplies an ordinal base; it is not a claim that the attendance system began then. Any fixed suitable base works for this limited date domain. The grouping relies on daily increments and deduplicated rows, so explain those assumptions before adapting the expression to a different sequence.

Generate Missing Calendar Dates

SQL Server 2022 introduces GENERATE_SERIES, which requires compatibility level 160 or higher. Generate numeric day offsets, convert them into dates, and retain expected dates without a matching user-day row. Use explicit inclusive date boundaries for this calendar report rather than importing a timestamp-range convention accidentally.

DECLARE @StartDate date='2026-09-20',@EndDate date='2026-09-26';
DECLARE @UserID int=1;
IF @EndDate<@StartDate THROW 50000,'Check the requested date boundaries.',1;
SELECT DATEADD(DAY,g.value,@StartDate) AS MissingLoginDay
FROM GENERATE_SERIES(0,DATEDIFF(DAY,@StartDate,@EndDate)) AS g
WHERE NOT EXISTS
(
    SELECT 1 FROM #LoginDays AS d
    WHERE d.UserID=@UserID AND d.LoginDay=DATEADD(DAY,g.value,@StartDate)
)
ORDER BY MissingLoginDay;

A business calendar should supply its expected rows from an approved calendar table instead. The generated daily series includes weekends and holidays. Also bound the requested interval before allowing arbitrary user input to produce a very large result. A report of absence can still create plenty of work.

Rank Login Streaks With a Clear Tie Rule

Use the same island preparation to rank completed runs by their daily count. Decide whether equal-length streaks should all appear or one should win using an explicit secondary rule. A deterministic ROW_NUMBER choice can select the latest starting run among equal-length candidates without pretending it is uniquely longest.

WITH Numbered AS
(
    SELECT UserID,LoginDay,
           DATEDIFF(DAY,DATEFROMPARTS(2000,1,1),LoginDay)
           - ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY LoginDay) AS IslandID
    FROM #LoginDays
), Streaks AS
(
    SELECT UserID,MIN(LoginDay) AS StreakStart,
           MAX(LoginDay) AS StreakEnd,COUNT_BIG(*) AS LoginDays
    FROM Numbered GROUP BY UserID,IslandID
), Ranked AS
(
    SELECT *,ROW_NUMBER() OVER
        (PARTITION BY UserID ORDER BY LoginDays DESC,StreakStart DESC) AS StreakRank
    FROM Streaks
)
SELECT UserID,StreakStart,StreakEnd,LoginDays
FROM Ranked WHERE StreakRank=1;

I keep current-streak and longest-streak questions separate. A longest historical run can have ended months ago. A current streak needs an additional accepted rule about the reporting date and whether today's activity is required before the day ends.

Test Gaps and Islands Queries at the Edges

Which boundaries would change the interpretation of the result? Test empty inputs, one row, duplicates, multiple users, gaps at both edges, and a run crossing a month or year boundary. Use a user-and-date index for the attendance grain and inspect plans when scaling the report.

Gaps and islands become reliable when the sequence rule, deduplication, and boundaries are explicit. Keep those properties with the query so the same window-function trick does not quietly answer a different business question after it is reused.

Related reading on this blog: Find Missing Identity Values and The Four Window Functions You Will Actually Use.

A missing value is not automatically missing business data, it is an absence relative to an explicitly defined sequence.

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