Finding gaps in values with SQL Server's LAG function

SQL Server is a powerful database with a set powerful functions. One of them is called LAG, and it allows you to find gaps in value sequences. So, if you need to compare a row with the previous one in SQL Server, the LAG window function is often the simplest tool.

The LAG function lets you read a value from the previous row without a self-join. This is handy for detecting changes, breaks in sequences, or gaps in date values. It's precisely what I needed today.

Suppose you have a table with daily records:

CREATE TABLE dbo.SampleDates
(
    LogDate date NOT NULL
);

INSERT INTO dbo.SampleDates (LogDate)
VALUES
  ('2018-11-01'),
  ('2018-11-02'),
  ('2018-11-03'),
  ('2018-11-05'),
  ('2018-11-06');

You can now use the LAG function to see the previous date for each row:

SELECT
    LogDate,
    LAG(LogDate) OVER (ORDER BY LogDate) AS PreviousLogDate
FROM dbo.SampleDates
ORDER BY LogDate;

Once you have the previous value, finding gaps becomes straightforward: you can for example DATEDIFF the two values. For example, the following example returns rows where more than one day is missing:

SELECT
    LogDate,
    LAG(LogDate) OVER (ORDER BY LogDate) AS PreviousLogDate
FROM dbo.SampleDates
WHERE DATEDIFF
(
    day,
    LAG(LogDate) OVER (ORDER BY LogDate),
    LogDate
) > 1
ORDER BY LogDate;

In this case, the row for 2018-11-05 is returned, showing a gap after 2018-11-03.

Note that LAG works with any sortable value, not just dates. It's a good fit whenever you find yourself reaching for a self-join just to compare adjacent rows.

Hope this helps!