Pentium10 Pentium10 - 1 month ago 6
SQL Question

Find the gap in ID column + select the prev/next date column

We have a table with

ID
autoincrement column with gaps. Each row has also a
created
date.

We are trying to find out for each gap ID (the missing ones) the prev/next date that is available in our table.

We already built a SQL that identifies the gaps (solution from here), so I have them in a table cached, but from here how to find the prev/next
created
date that covers the gap from the original table.

Input:

+----+------------+
| 84 | 1443728132 |
| 91 | 1443728489 |
| 93 | 1443729058 |
| 94 | 1443729200 |
+----+------------+


Output

+--------+------------+------------+
| gap_id | prev_dt | next_dt |
+--------+------------+------------+
| 85 | 1443728132 | 1443728489 |
| 86 | 1443728132 | 1443728489 |
| 87 | 1443728132 | 1443728489 |
| 88 | 1443728132 | 1443728489 |
| 89 | 1443728132 | 1443728489 |
| 90 | 1443728132 | 1443728489 |
| 92 | 1443728489 | 1443729058 |
+--------+------------+------------+

Answer

Should work on most databases other than MySQL

select      *

from       (select      lag (id) over (order by id) + 1                                 as gap_start
                       ,id - 1                                                          as gap_end
                       ,lag (dt) over (order by id)                                     as dt_before_gap
                       ,dt                                                              as dt_after_gap
                       ,case when lag (id) over (order by id) + 1 <> id then 'Y' end    as is_gap

            from        t
            ) t

where       is_gap = 'Y'
;