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 columns like id,future_date columns.
future_date: Iterate the dates by adding 365 days to the dates in the table. It has to add Until it reaches the future_date>sysdate.
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>