sql - How to determine if my batches are all consecutive? -

company produces 3 batches per year each using following naming convention: yyyy11,yyyy22,yyyy33

in case batch_id =1 batches sequential. absence of batches 200933 , 201022 make batch_id=2 non sequential.

with batch_sequences as(   select  1 batch_sequence, '200911' batch_date  dual union   select  2 batch_sequence, '200922' batch_date dual  union   select  3 batch_sequence, '200933' batch_date  dual  union   select  4 batch_sequence, '201011' batch_date  dual union   select  5 batch_sequence, '201022' batch_date dual union   select  6 batch_sequence, '201033' batch_date  dual), batch_entries  (    select   1 batch_id, '200911'  batch_date dual union    select   1 batch_id, '200922'  batch_date dual union    select   1 batch_id, '200933'  batch_date dual union    select   1 batch_id, '201011'  batch_date dual union    select   1 batch_id, '201022'  batch_date dual union    select   1 batch_id, '201033'  batch_date dual union    select   2 batch_id, '200911'  batch_date dual union    select   2 batch_id, '200922'  batch_date dual union    select   2 batch_id, '201011'  batch_date dual union    select   2 batch_id, '201033'  batch_date dual  ) select batch_sequence,        e.batch_id,        s.batch_date,        lead(batch_sequence,1) on (order batch_sequence) next_batch   batch_entries e  inner join batch_sequences s on e.batch_date=s.batch_date  order e.batch_id,           e.batch_date; 

i'm thinking can perform math on lead values, i'm not getting batch_sequence values correctly calculate this.

question

how can write query show batch_id=1 had 'perfect run' , batch_id=2 missed batch_dates?

i'll content result set can highlight this.

assign sequential number each batch_id based on batch_date , compare batch_sequence:

with cte  (    select batch_id, batch_date,       row_number() -- sequential number       on (partition batch_id             order batch_date) rn    batch_entries  )  select e.batch_id  cte e join batch_sequences s     on e.batch_date=s.batch_date   group e.batch_id  -- if there's no missing batch difference same    having min(s.batch_sequence - e.rn) <> max(s.batch_sequence - e.rn) 

see fiddle

data 2nd batch:

batch_date  rn    batch_sequence  batch_date '200911'  -> 1           1         '200911' '200922'  -> 2           2         '200922'                          3         '200922' '201011'  -> 3           4         '201011'                           5         '201022' '201033'  -> 4           6         '201033'