TSQL to loop through to get per month rows in result

Question

Hi there,

I have below SQL Server 2016 table. 'Somehow' I would like to loop through data (either via view or store proc) to show new rows per month for a given PeriodType logic.

Current Table:
TargetName TargetType TargetClass TargetYear TargetPeriodType TargetValue
MGMT LeaderShip NomiAlloc 2021 Annual 85
MGMT Interaction NULL 2021 Annual 250
MGMT Facility NULL 2021 Qtr1 CY_CalendarYear OnTrack
MGMT Facility NULL 2021 Qtr2 CY_CalenderYear OnTrackPush
MGMT Facility NULL 2021 Qtr3 CY_CalenderYear OnTrackLead
MGMT Facility NULL 2021 Qtr4 CY_CalenderYear OnTrackLeg
LEAD Miti NULL 2021/22 FY Annual 75
LEAD Reasoning Cleared 2021/22 Qtr1 FY_FinancialYear 388
LEAD Reasoning Cleared 2021/22 Qtr2 FY_FinancialYear 943
LEAD Reasoning Cleared 2021/22 Qtr3 FY_FinancialYear 20
LEAD Reasoning Cleared 2021/22 Qtr4 FY_FinancialYear 764

Expected Output Result
TargetName TargetType TargetClass TargetYear TargetPeriodType TargetValue Target_TheDate
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-01-01
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-02-01
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-03-01
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-04-01
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-05-01
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-06-01
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-07-01
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-08-01
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-09-01
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-10-01
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-11-01
MGMT LeaderShip NomiAlloc 2021 Annual 85 2021-12-01

MGMT Interaction NULL 2021 Annual 250 2021-01-01
MGMT Interaction NULL 2021 Annual 250 2021-02-01
MGMT Interaction NULL 2021 Annual 250 2021-03-01
MGMT Interaction NULL 2021 Annual 250 2021-04-01
MGMT Interaction NULL 2021 Annual 250 2021-05-01
MGMT Interaction NULL 2021 Annual 250 2021-06-01
MGMT Interaction NULL 2021 Annual 250 2021-07-01
MGMT Interaction NULL 2021 Annual 250 2021-08-01
MGMT Interaction NULL 2021 Annual 250 2021-09-01
MGMT Interaction NULL 2021 Annual 250 2021-10-01
MGMT Interaction NULL 2021 Annual 250 2021-11-01
MGMT Interaction NULL 2021 Annual 250 2021-12-01

MGMT Facility NULL 2021 Qtr1 CY OnTrack 2021-01-01
MGMT Facility NULL 2021 Qtr1 CY OnTrack 2021-02-01
MGMT Facility NULL 2021 Qtr1 CY OnTrack 2021-03-01

MGMT Facility NULL 2021 Qtr2 CY OnTrackPush 2021-04-01
MGMT Facility NULL 2021 Qtr2 CY OnTrackPush 2021-05-01
MGMT Facility NULL 2021 Qtr2 CY OnTrackPush 2021-06-01

MGMT Facility NULL 2021 Qtr3 CY OnTrackLead 2021-07-01
MGMT Facility NULL 2021 Qtr3 CY OnTrackLead 2021-08-01
MGMT Facility NULL 2021 Qtr3 CY OnTrackLead 2021-09-01

MGMT Facility NULL 2021 Qtr4 CY OnTrackLeg 2021-10-01
MGMT Facility NULL 2021 Qtr4 CY OnTrackLeg 2021-11-01
MGMT Facility NULL 2021 Qtr4 CY OnTrackLeg 2021-12-01

LEAD Miti NULL 2021/22 FY Annual 75 2021-07-01
LEAD Miti NULL 2021/22 FY Annual 75 2021-08-01
LEAD Miti NULL 2021/22 FY Annual 75 2021-09-01
LEAD Miti NULL 2021/22 FY Annual 75 2021-10-01
LEAD Miti NULL 2021/22 FY Annual 75 2021-11-01
LEAD Miti NULL 2021/22 FY Annual 75 2021-12-01
LEAD Miti NULL 2021/22 FY Annual 75 2022-01-01
LEAD Miti NULL 2021/22 FY Annual 75 2022-02-01
LEAD Miti NULL 2021/22 FY Annual 75 2022-03-01
LEAD Miti NULL 2021/22 FY Annual 75 2022-04-01
LEAD Miti NULL 2021/22 FY Annual 75 2022-05-01
LEAD Miti NULL 2021/22 FY Annual 75 2022-06-01

LEAD Reasoning Cleared 2021/22 Qtr1 FY 388 2021-07-01
LEAD Reasoning Cleared 2021/22 Qtr1 FY 388 2021-08-01
LEAD Reasoning Cleared 2021/22 Qtr1 FY 388 2021-09-01

LEAD Reasoning Cleared 2021/22 Qtr2 FY 943 2021-10-01
LEAD Reasoning Cleared 2021/22 Qtr2 FY 943 2021-11-01
LEAD Reasoning Cleared 2021/22 Qtr2 FY 943 2021-12-01

LEAD Reasoning Cleared 2021/22 Qtr3 FY 20 2022-01-01
LEAD Reasoning Cleared 2021/22 Qtr3 FY 20 2022-02-01
LEAD Reasoning Cleared 2021/22 Qtr3 FY 20 2022-03-01

LEAD Reasoning Cleared 2021/22 Qtr4 FY 764 2022-04-01
LEAD Reasoning Cleared 2021/22 Qtr4 FY 764 2022-05-01
LEAD Reasoning Cleared 2021/22 Qtr4 FY 764 2022-06-01

So as you can see above:
(1) When TargetPeriodType = 'Annual' then it should has 12 rows per month (by calender year). Hence column name 'Target_TheDate' in expected output result has 12 rows by month.
(2) When TargetPeriodType = ' Qtr1 CY_CalendarYear' then it should has 3 rows per month for that calendar year quarter (Jan/Feb/Mar).
(3) When TargetPeriodType = 'Qtr2 CY_CalendarYear' then it should has 3 rows per month for that calendar year quarter(Apr/May/Jun).
(4) When TargetPeriodType = 'Qtr3 CY_CalendarYear' then it should has 3 rows per month for that year quarter(Jul/Aug/Sep).
(5) When TargetPeriodType = 'Qtr4 CY_CalendarYear' then it should has 3 rows per month for that year quarter(Oct/Nov/Dec).
(5) When TargetPeriodType = 'FY Annual' then it should has 12 rows per month for that financial year. In Australia Financial year start in 1st of July.
(6) When TargetPeriodType = ' Qtr1 FY' then it should has 3 rows per month for that financial year quarter (July/Aug/Sept).
(7) When TargetPeriodType = 'Qtr2 FY' then it should has 3 rows per month for that calendar year quarter(Oct/Nov/Dec).
(8) When TargetPeriodType = 'Qtr3 FY' then it should has 3 rows per month for that next year quarter(Jan/Feb/Mar).
(9) When TargetPeriodType = 'Qtr4 FY' then it should has 3 rows per month for that next year quarter(Mar/Apr/May).

Please help! Many thanks.