I have to write SQL query to iterate the dates to until date becomes greater than sysdate

Question

I have a Employee_info table and it contains employee_id and date.

date ex:20-03-2016,
        01-01-2016,
        01-01-2017,
        09-04-2021,
        25-07-2021 etc.

I want write a SQL select query which contain id,future_date and past_date columns.

future_date: iterate these dates by adding 365 days until it reaches date>sysdate in one column .

past_date: subtract the date by -365days.

Answer 1

It is about some kind of a row generator. For example, with two sample rows

SQL> with employee_info (employee_id, datum) as
  2    (select 1, date '2016-03-20' from dual union all
  3     select 2, date '2021-07-25' from dual
  4    )

Query begins here:

 5  select employee_id,
  6    datum,
  7    add_months(datum,  (column_value - 1) * 12) future_datum,
  8    add_months(datum, -(column_value - 1) * 12) past_date
  9  from employee_info cross join
 10    table(cast(multiset(select level from dual
 11                        connect by level <= ceil(months_between (sysdate, datum) / 12)
 12                       ) as sys.odcinumberlist))
 13  order by employee_id, future_datum;

EMPLOYEE_ID DATUM      FUTURE_DAT PAST_DATE
----------- ---------- ---------- ----------
          1 2016-03-20 2016-03-20 2016-03-20
          1 2016-03-20 2017-03-20 2015-03-20
          1 2016-03-20 2018-03-20 2014-03-20
          1 2016-03-20 2019-03-20 2013-03-20
          1 2016-03-20 2020-03-20 2012-03-20
          1 2016-03-20 2021-03-20 2011-03-20
          1 2016-03-20 2022-03-20 2010-03-20
          1 2016-03-20 2023-03-20 2009-03-20
          2 2021-07-25 2021-07-25 2021-07-25
          2 2021-07-25 2022-07-25 2020-07-25

10 rows selected.

SQL>