Hello All,
I have a query with finding the gap in between the sequences.
Below is the structure of my table
user_number | seq_number
1 | 101
1 | 102
1 | 109
1 | 110
1 | 111
2 | 201
2 | 202
2 | 203
2 | 207
2 | 208
. | .
. | .
. | .
. | .
Now I have to find the gap in between the sequences, what I mean by gap is for user_number '1' there is gap of 6 between seq_number '102' and '109'.
Below is the query which I am trying to write, but not giving the exact answer, it always gives the gap of 2:
select gap_start - gap_end gap
from(
select a.seq_number+1 as gap_start, b.seq_number-1 as gap_end
from my_table a, my_table b
where a.seq_number = b.seq_number
and a.seq_number - (select c.seq_number
from my_table c
where c.seq_number = a.seq_number-1) is null
and a.user_number = '1'
order by a.seq_number
);
Thanks.