I have a table with rows of year and month

Ex.

YearMonth
202001
202002
202003
202006
202007
202010
202011
202012

I would like to find the last continuous period

For the example the last continous period is: 202010 - 202012

Other case:

YearMonth
202001
202003
202004
202005
202006
202007
202008

For this the period is: 202003 - 202008

CodePudding user response：

select   
         min(YearMonth) as start_date
        ,max(YearMonth) as end_date
from      (select YearMonth
                 ,    months_between(to_date(YearMonth, 'YYYYMM'), date '2000-01-01')
                    - row_number() over (order by YearMonth) as grp
           from t 
           ) t
group by grp 
having   count(*) > 1 
order by grp desc
fetch first row only 

Fiddle

CodePudding user response：

Well, i write a query

with pagos as
(
select 1 dni, 202001 pago from dual
union all
select 1, 202002 pago from dual
union all
select 1, 202003 pago from dual
union all
select 1, 202004 pago from dual
union all
select 1, 202007 pago from dual
union all
select 1, 202008 pago from dual
union all
select 1, 202009 pago from dual
)


select  distinct (min(pago) keep (DENSE_RANK FIRST order by pago ) OVER (partition by dni))-1 minimo,
        max(pago) keep (DENSE_RANK LAST order by pago ) OVER (partition by dni) maximo
from 
(
    select p.*, sum(start_new_group) OVER (PARTITION BY dni ORDER BY pago desc) group_id
from
(
SELECT  dni, pago, 
        lead(pago,1) over (order by pago desc ),
        CASE WHEN pago - 1 = lead(pago,1) over (order by pago desc ) THEN 0 ELSE 1 END start_new_group
        FROM pagos m 
        order by 1 desc nulls last
) p
) p
where group_id = 0

Result: 202007 - 202009

query result

obviusly i have to change pago - 1 por a function beacuse por example 201001 - 1 is 200912