I have a table with activities. The columns are: activityId, nextDate, frequency.
The frequency is in months.
I need a query that will generate all the dates at which the activity will take place based on frequency (in months).
I am using DB2.

Activities table:

Giving parameter: 12/31/2024

Output needed:

>Solution :

You can do it with a recursive CTE

with activities (activityId, nextDate, frequency) as (
  values (1, date '2024-03-01', 2)
),
activities_this_year (activityId, firstDate, number, nextDate, frequency) as (
  select activityId, nextDate as firstDate, 1, nextDate, frequency from activities
  union all
  select
    activityId,
    firstDate,
    number + 1,
    nextDate + frequency month,
    frequency
  from activities_this_year
  where nextDate + frequency month < this_year(current date) + 1 year
)
select activityId, nextDate from activities_this_year order by activityId, nextDate

fiddle