SQL Server 2019 (Linux)

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

--Schedule table排程資料表
Drop Table If Exists Schedule
Create Table Schedule(SchId nvarchar(20) Unique, FrequencyMinute int)
Insert Into Schedule(SchId, FrequencyMinute)
Values 
    ('A',480),
    ('B',720)
Select * From Schedule


SchId
FrequencyMinute


A
480

B
720


--Jobs table任務資料表
Drop Table If Exists Jobs
Create Table Jobs(JobId nvarchar(20) Unique, SchId nvarchar(2),StartTime datetime)
Select * From Jobs


JobId
SchId
StartTime


--時間區間
Declare @StartTime datetime = '2021-08-20 00:00'
Declare @EndTime datetime = '2021-08-21 00:00'

--使用CTE
;WITH CTE AS (
   SELECT SchId,FrequencyMinute,DATEADD(MINUTE,FrequencyMinute,@StartTime) st,@EndTime en
   FROM Schedule
   UNION ALL
   SELECT SchId,FrequencyMinute,DATEADD(MINUTE,FrequencyMinute,st),en
   FROM CTE 
   WHERE DATEADD(MINUTE,FrequencyMinute,st)<=en
)
Insert Into Jobs(JobId, SchId, StartTime)
SELECT FORMAT(@StartTime,'yyyyMMddhhmmss') +  ROW_NUMBER() OVER(ORDER BY SchId,ST) JobId,
       SchId,
       ST
FROM CTE

Select * From Jobs


JobId
SchId
StartTime


20210820120001
A
2021-08-20 08:00:00.000

20210820120002
A
2021-08-20 16:00:00.000

20210820120003
A
2021-08-21 00:00:00.000

20210820120004
B
2021-08-20 12:00:00.000

20210820120005
B
2021-08-21 00:00:00.000

JobId	SchId	StartTime
20210820120001	A	2021-08-20 08:00:00.000
20210820120002	A	2021-08-20 16:00:00.000
20210820120003	A	2021-08-21 00:00:00.000
20210820120004	B	2021-08-20 12:00:00.000
20210820120005	B	2021-08-21 00:00:00.000