oracle SQL determine consecutive periods in months

Question

Good morning,

Every 1st month I collect data for my data warehouse. Now I want to determine what the consecutive period over the months is with oracle sql.

If there is an interruption in the month, I want to start a new period.

The example I have is similar to the one below:

ID  LOAD_DATE  
100 20190101  
100 20190201  
100 20190401  
100 20190501  
100 20190601  
100 20190701  
100 20191001  
100 20191101  
100 20191201  
100 20200101  
200 20190701  
200 20190901  
200 20191101  
200 20191201  
200 20200101  
200 20200201  

The desired outcome:

ID  From     To  
100 20190101 20190201  
100 20190401 20190701  
100 20191001 20200101  
200 20190701 20190701  
200 20190901 20190901  
200 20191101 20200201  

I can manage the continuous period based on one year. Due to the changes in the year, I am unable to do this by month.

Please help. Im using oracle sql developer

Answer 1

One solution makes use of window functions, with the actual dates stored as numbers, like so:

select id, min(load_date) "To", max(load_date) "From"
from (
  select id, load_date,
    sum(period_start) over (partition by id order by load_date_converted)
      period
  from (
    select id, load_date,
      to_date(load_date,'YYYYMMDD') load_date_converted,
      case when add_months(to_date(load_date,'YYYYMMDD'),-1)<>
        lag (to_date(load_date,'YYYYMMDD'),1,sysdate)
          over (partition by id order by load_date)
            then 1 else 0
      end period_start
    from table_name
  )
)
group by id, period
order by id, period

Oracle Version: 12c