How to find the next value in this case

Question

So it's a bit complex.

We have a "report" in ERP system. It's like a big query from few tables... but, it's not so important, i guess. We have thousand of rows.

It's only an example:

How can I search for the "next flyer number" ? So if item is in flyer 2015C and 2016C I want to get "Extended flyer".

The logic here is: if the item is in actual flyer +1 = that's an "Extended flyer".

2015C: 20 actual year, 15 is number of the flyer. C is the type of the flyer.

The date is from year - 2008, to today. So I can't count only values (there are lot of items with lot of flyers).

Maybe something with LAG/LEAD ?

Now I'm using this query, but it's not accurate (self join):

decode(null,
  (select count(*) 
   from --my_actual_table g
   where g.id = id 
   and g.place_of_delivery = place_of_delivery 
   and g.partner = partner
   and g.date <= date and g.date >= date-22 
   group by g.id, g.place_of_delivery, g.partner 
   having count(*) > 1 ),
null,'Extended flyer')

Is there any better way to do this ?

Answer 1

I've created some table, which consist of only one column: flyer. And the query looks like:

Select flyer,
case when to_number(substr(flyer,3,2))-to_number(substr(previous_flyer,3,2))=1 then 'Extended' else ' ' end
from
(
 Select flyer, 
 LAG(flyer,1) over (order by flyer) as previous_flyer
 from stackoverflow_table
)

Checked that and its working, i get an output:

2015C    
2017C    
2018C   Extended
2019C   Extended
2021C    
2023C    
2024C   Extended
2025C   Extended
2027C    
2029C    
2030C   Extended