Generate years of date ranges from a set of dates

Question

Using Oracle 12c

I need to generate years of date ranges from a set of dates per ID. I tried working around with LEVEL and CONNECT BY clause but I do not understand it fully and I wasn't able to come up with a solution. Please see the example below and the expected result. Any help is appreciated!

WITH dataset AS
   (SELECT 1 id,'01-Jan-2015' dt_fr,'01-Jan-2018' dt_to FROM DUAL
      UNION SELECT 2 id,'01-Dec-2016' dt_fr,'01-Dec-2020' dt_to FROM DUAL
      UNION SELECT 3 id,'01-May-2015' dt_fr,'01-May-2015' dt_to FROM DUAL
      )
   SELECT id
      ,--dt_fr
      ,--dt_to
      FROM dataset

ID  yr_from    yr_to
1   01-Jan-15   31-Dec-15
1   01-Jan-16   31-Dec-16
1   01-Jan-17   31-Dec-17
1   01-Jan-18   31-Dec-18
2   01-Dec-16   30-Nov-17
2   01-Dec-17   30-Nov-18
2   01-Dec-18   30-Nov-19
2   01-Dec-19   30-Nov-20
2   01-Dec-20   30-Nov-21
3   01-May-15   30-Apr-16

Answer 1

In Oracle 12, in addition to connect by, you can use the cross apply clause to generate the "levels" separately for each input row. This makes the code cleaner, since you don't have to handle cycles in the data, as well as faster.

In your code you show strings where you probably mean dates. I wrote the query as if the inputs are actual dates (and the outputs are dates also); I changed my nls_date_format so that the output matches your format, and I don't need to give the format explicitly in to_date in the with clause.

alter session set nls_date_format = 'dd-Mon-yyyy';

with
  dataset (id, dt_fr, dt_to) as (
    select 1, to_date('01-Jan-2015'), to_date('01-Jan-2018') from dual union all
    select 2, to_date('01-Dec-2016'), to_date('01-Dec-2020') from dual union all
    select 3, to_date('01-May-2015'), to_date('01-May-2015') from dual
  )
select id
     , add_months(dt_fr, 12 * (lvl - 1)) as yr_from
     , add_months(dt_fr, 12 * lvl) - 1   as yr_to
from   dataset
       cross apply
       ( select  level as lvl
         from    dual
         connect by level <= months_between(dt_to, dt_fr) / 12 + 1
       )
order by id, dt_fr   --   if needed
;

 ID YR_FROM     YR_TO      
--- ----------- -----------
  1 01-Jan-2015 31-Dec-2015
  1 01-Jan-2016 31-Dec-2016
  1 01-Jan-2017 31-Dec-2017
  1 01-Jan-2018 31-Dec-2018
  2 01-Dec-2016 30-Nov-2017
  2 01-Dec-2017 30-Nov-2018
  2 01-Dec-2018 30-Nov-2019
  2 01-Dec-2019 30-Nov-2020
  2 01-Dec-2020 30-Nov-2021
  3 01-May-2015 30-Apr-2016