Pentium10 - 2 months ago 12

SQL Question

We have a table with

`ID`

`created`

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`

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'
;
```