Home » SQL » Finding Sequence Gaps in a Table in Oracle

Finding Sequence Gaps in a Table in Oracle

Finding Sequence Gaps in a Table in Oracle

Problem
You want to find all gaps in the sequence of numbers or in dates and times in your data. The gaps could be in dates recorded for a given action, or in some other data with a logically consecutive nature.
Solution
Oracle’s LAG and LEAD OLAP functions let you compare the current row of results with a preceding row.
The general format of LAG looks like this  Lag (column or expression, preceding row offset, default for first row) The column or expression is the value to be compared with lagging (preceding) values. The preceding row offset indicates how many rows prior to the current row the LAG should act against. We’ve used ‘1’ in the following listing to mean the row one prior to the current row. The default for LAG indicates what value to use as a precedent for the first row, as there is no row zero in a table or result. We instruct Oracle to use 0 as the default anchor value, to handle the case where we look for the day prior to the first of the month.
The WITH query alias approach can be used in almost all situations where a subquery is used, to
relocate the subquery details ahead of the main query. This aids readability and refactoring of the code if required at a later date.
This recipe looks for gaps in the sequence of days on which orders were made for the month of
November 1999:
with salesdays as
(select extract(day from order_date) next_sale,
lag(extract(day from order_date),1,0)
over (order by extract(day from order_date)) prev_sale
from oe.orders
where order_date between '01-NOV-1999' and '30-NOV-1999')
select prev_sale, next_sale
from salesdays
where next_sale - prev_sale > 1
order by prev_sale;

PREV_SALE NEXT_SALE
---------- ----------
1 10
10 14
15 19
20 22
The results indicate that after an order was recorded on the first of the month, no subsequent order
was recorded until the 10th. Then a four-day gap followed to the 14th, and so on. An astute sales manager
might well use this data to ask what the sales team was doing on those gap days, and why no orders
came in!