SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2016 (SP3-OD) (KB5006943) - 13.0.6404.1 (X64) 
	Oct 18 2021 09:37:01 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE date_spans
(
  group_id    VARCHAR(8) NOT NULL,
  plan_id     VARCHAR(8) NOT NULL,
  beg_dt      DATE       NOT NULL,
  end_dt      DATE       NOT NULL,
  plan_length INTEGER    NOT NULL
);
INSERT INTO date_spans 
(group_id, plan_id, beg_dt, end_dt, plan_length)
VALUES

('32198498','AMD00001','2020-10-01','2021-09-30','12'),

('32198498','AMD00001','2021-10-01','2022-09-30','12'),

('32198498','AMD00002','2020-10-01','2022-09-30','12'),

('32198498','AMD00003','2020-10-01','2100-09-30','12'),


('75491773','AMT00004','2020-01-01','2021-06-30','18'),


('82551399','AMT00001','2020-01-01','2020-12-31','12'),

('82551399','AMT00002','2020-01-01','2100-12-31','12'),

('82551399','AMT00003','2020-01-01','2021-12-31','12');
8 rows affected

--TABLE DATA

SELECT * FROM date_spans


group_id
plan_id
beg_dt
end_dt
plan_length


32198498
AMD00001
2020-10-01
2021-09-30
12

32198498
AMD00001
2021-10-01
2022-09-30
12

32198498
AMD00002
2020-10-01
2022-09-30
12

32198498
AMD00003
2020-10-01
2100-09-30
12

75491773
AMT00004
2020-01-01
2021-06-30
18

82551399
AMT00001
2020-01-01
2020-12-31
12

82551399
AMT00002
2020-01-01
2100-12-31
12

82551399
AMT00003
2020-01-01
2021-12-31
12


WITH
  /*
    This is just a table of numbers, a very useful tool
    for solving various problems; consider creating and
    maintaining a real one in your database.
    Many people keep numbers tables starting with 1,
    I prefer mine to start with a 0.
  */
  Numbers (N) AS
  (
    SELECT
      hundreds.N * 100 + tens.N * 10 + ones.N * 1
    FROM
      (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS ones (N)
    , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS tens (N)
    , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS hundreds (N)
  )
SELECT
  ds.*
, x.Date
FROM
  dbo.date_spans AS ds
  
  -- this join multiplies each range as many times as there are
  -- PLAN_LENGTH-month intervals within the range
  INNER JOIN Numbers AS n
    ON n.N BETWEEN 0 AND DATEDIFF(MONTH, ds.BEG_DT, ds.END_DT) / ds.PLAN_LENGTH

  -- this generates an actual date
  CROSS APPLY
  (
    SELECT DATEADD(YEAR, n.N, ds.BEG_DT)
  ) AS x (Date)
WHERE
  -- this additionally caps the list of dates at a point that is today + PLAN_LENGTH months
  x.Date < DATEADD(MONTH, ds.PLAN_LENGTH, CAST(CURRENT_TIMESTAMP AS date))
ORDER BY
  ds.group_id ASC
, ds.plan_id ASC
, n.N ASC
;


group_id
plan_id
beg_dt
end_dt
plan_length
Date


32198498
AMD00001
2020-10-01
2021-09-30
12
2020-10-01

32198498
AMD00001
2021-10-01
2022-09-30
12
2021-10-01

32198498
AMD00002
2020-10-01
2022-09-30
12
2020-10-01

32198498
AMD00002
2020-10-01
2022-09-30
12
2021-10-01

32198498
AMD00003
2020-10-01
2100-09-30
12
2020-10-01

32198498
AMD00003
2020-10-01
2100-09-30
12
2021-10-01

32198498
AMD00003
2020-10-01
2100-09-30
12
2022-10-01

32198498
AMD00003
2020-10-01
2100-09-30
12
2023-10-01

75491773
AMT00004
2020-01-01
2021-06-30
18
2020-01-01

82551399
AMT00001
2020-01-01
2020-12-31
12
2020-01-01

82551399
AMT00002
2020-01-01
2100-12-31
12
2020-01-01

82551399
AMT00002
2020-01-01
2100-12-31
12
2021-01-01

82551399
AMT00002
2020-01-01
2100-12-31
12
2022-01-01

82551399
AMT00002
2020-01-01
2100-12-31
12
2023-01-01

82551399
AMT00002
2020-01-01
2100-12-31
12
2024-01-01

82551399
AMT00003
2020-01-01
2021-12-31
12
2020-01-01

82551399
AMT00003
2020-01-01
2021-12-31
12
2021-01-01

group_id	plan_id	beg_dt	end_dt	plan_length
32198498	AMD00001	2020-10-01	2021-09-30	12
32198498	AMD00001	2021-10-01	2022-09-30	12
32198498	AMD00002	2020-10-01	2022-09-30	12
32198498	AMD00003	2020-10-01	2100-09-30	12
75491773	AMT00004	2020-01-01	2021-06-30	18
82551399	AMT00001	2020-01-01	2020-12-31	12
82551399	AMT00002	2020-01-01	2100-12-31	12
82551399	AMT00003	2020-01-01	2021-12-31	12