Finding Gaps in Sequential Data Using SQL Server

Question

Finding Gaps in Sequential Data Using SQL Server

Imran2629

SSC Enthusiast

Points: 153
More actions
September 21, 2026 at 12:00 am

#4970608

Comments posted to this topic are about the item Finding Gaps in Sequential Data Using SQL Server

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jeffrey.webb Valued Member Points: 60 More actions · Answer 1

I have a need to find gaps in dates. Can this be used for dates? For example, was there activity everyday from '01-01-2026' to '03-31-2026'.

Eirikur Eiriksson SSC Guru Points: 182940 More actions · Answer 2

jeffrey.webb wrote:

I have a need to find gaps in dates. Can this be used for dates? For example, was there activity everyday from '01-01-2026' to '03-31-2026'.

Just use dateadd to generate the date series

😎

pietlinden SSC Guru Points: 63706 More actions · Answer 3

(But you have to start the counter table at 0 instead of 1 in order to include the first date.)

use tempdb;
go

DECLARE @StartDate DATE = '2026-01-01';

SELECT value, DATEADD(day,value,@StartDate)
FROM generate_series(0,9)

drew.allen SSC Guru Points: 77013 More actions · Answer 4

You're missing a very important step. You've hard-coded the range values instead of reading them from the table. Using this approach, you would need to read the table twice: once to get the ranges, and once to get the missing values. There is an approach that will allow you to only read the table once.

WITH Prev AS (
    SELECT i.InvoiceNumber, LAG(i.InvoiceNumber) OVER(ORDER BY i.InvoiceNumber) AS PrevInvoiceNumber
    FROM #Invoices AS i
)
SELECT s.value AS MissingInvoiceNumber
FROM Prev AS p
CROSS APPLY GENERATE_SERIES(p.PrevInvoiceNumber + 1, p.InvoiceNumber - 1) s
WHERE p.InvoiceNumber > p.PrevInvoiceNumber + 1

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ildjarn.is.dead Ten Centuries Points: 1176 More actions · Answer 5

Here is a solution using windowing functions to find gaps in date sequences:

https://www.mssqltips.com/tutorial/sql-server-window-functions-gaps-and-islands-problem/

pietlinden SSC Guru Points: 63706 More actions · Answer 6

(writing this article now seems like a bit late...?? I mean, The solutions have been in Itzik Ben-Gan's book on T-SQL for like 15+ years now. Gaps & Islands etc. How to use APPLY... etc etc.)