DB2 SQL forecast generation based giving as parameters end date

Question

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:

activityId	nextDate	frequency
1	03/01/2024	2

Giving parameter: 12/31/2024

Output needed:

activityId	date
1	03/01/2024
1	05/01/2024
1	07/01/2024
1	09/01/2024
1	11/01/2024

Answer 1

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

ACTIVITYID	NEXTDATE
1	2024-03-01
1	2024-05-01
1	2024-07-01
1	2024-09-01
1	2024-11-01

fiddle