Got gaps in your data–oracle query to help you out

It’s a classic situation that generally requires variables and cursors to solve.  You want to check how contiguous your data is.  You want to know if you are missing next numbers in your sales order table, missing batch jobs from F986110 or missing any history records.  Well, has oracle got the solution for you. 

My issue is that I want to know that my OMW history is contiguous – as it’s important for me to know.  I want to make sure that there have been no gaps thanks to overzealous CNC purging.  NEVER PURGE F98210 or F98211 they are vital at upgrade time.

Here is a statement that is going to help me out:

WITH omwQuery as
(SELECT lhupmj as afterDate, LAG(lhupmj,1,0) OVER (ORDER BY lhupmj) BeforeDate FROM sy910.f98210 )
SELECT
  beforeDate, AfterDate
FROM
  omwQuery
WHERE
  beforeDate != 0
AND
  afterDate - BeforeDate > 20
ORDER BY
  AfterDate

In the Query above, beforeDate is the row before the current row date.  Ater date is the current row date (probably not the best name).  You can see them selected in the top select statement.  I’m using LAG for this (therefore my order BY is default, not DESC).  Then I want to only get those rows where beforeDate is not 0 (not the first) and there is a gap of at least 20 days.

BEFOREDATE  AFTERDATE
---------- ----------
    112206     112228
    112331     112354
    112356     113005

This gives me a nice tidy list.  We could also select min, max and other items to do further checking.