Search code examples
sqloracledatetimewindow-functionsgaps-and-islands

Find previous number in column that is not missing from a sequence

I have a table with a column that should contain numbers in a complete sequence, for simplicity we will say 101 to 110. However, this table relies on unreliable information to be input manually, so numbers in the sequence get missed. There is also a date column in the same table that I need to refer to, more on that in a moment. I have been challenged to find all of the missing sequence numbers along with the previous sequence number that was entered, and the date it was entered, and the next sequence number with the date it was entered. Finding the missing sequence numbers is straightforward, it is getting the relevant previous and next records I am struggling with. So if my data looked like this;

table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
<html>

<body>
  <table>
    <tr>
      <th>Seq No</th>
      <th>Date Input</th>
    </tr>
    <tr>
      <td>101</td>
      <td>01-JAN-20</td>
    </tr>
    <tr>
      <td>102</td>
      <td>05-JAN-20</td>
    </tr>
    <tr>
      <td>104</td>
      <td>07-JAN-20</td>
    </tr>
    <tr>
      <td>105</td>
      <td>08-JAN-20</td>
    </tr>
    <tr>
      <td>106</td>
      <td>09-JAN-20</td>
    </tr>
    <tr>
      <td>108</td>
      <td>10-JAN-20</td>
    </tr>
    <tr>
      <td>109</td>
      <td>11-JAN-20</td>
    </tr>
    <tr>
      <td>110</td>
      <td>12-JAN-20</td>
    </tr>
  </table>
</body>

</html>

My result set would look something like;

table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
<html>
<body>
  <table>
    <tr>
      <th>Missing Seq No</th>
      <th>Previous Date</th>
      <th>Next Date</th>
      <th>Notes</th>
    </tr>
    <tr>
      <td>103</td>
      <td>05-JAN-20</td>
      <td>07-JAN-20</td>
      <td>Dates from found seq nos 102 and 104</td>
    </tr>
    <tr>
      <td>107</td>
      <td>09-JAN-20</td>
      <td>10-JAN-20</td>
      <td>Dates from found seq nos 106 and 108</td>
    </tr>
  </table>
</body>
</html>

But without the notes column, that's just there for clarity.

I can get to an answer, but the sql is so huge and unwieldy it is as good as useless. Thanks.

Solution

  • If you want one row per gap, then you can use window functions:

    select 
    lag_seq_no last_sequence_number,
    lag_date_input last_date_input,
    seq_no next_sequence_number,
    date_input next_date_input
from (
    select 
        t.*,
        lag(seq_no) over(order by date_input) lag_seq_no,
        lag(date_input) over(order by date_input) lag_date_input
    from mytable t
) t
where seq_no > lag_seq_no + 1

    On the other hand if you have consecutive missing numbers and you want one row for each, then you need some kind of recursion:

    with 
    data(seq_no, date_input, lag_seq_no, lag_date_input) as (
        select 
            t.*,
            lag(seq_no) over(order by date_input) lag_seq_no,
           lag(date_input) over(order by date_input) lag_date_input
        from mytable t
    ),
    cte (seq_no, date_input, lag_seq_no, lag_date_input) as (
        select seq_no, date_input, lag_seq_no + 1, lag_date_input
        from data
        where seq_no > lag_seq_no + 1
        union all
        select seq_no, date_input, lag_seq_no + 1, lag_date_input
        from cte
        where seq_no > lag_seq_no + 1
) 
select 
    lag_seq_no missing_seq_no,
    lag_date_input last_date_input,
    date_input next_date_input
from cte