各位先進們大家好,
目前我有兩個Table
--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
--Jobs table任務資料表
Drop Table If Exists Jobs
Create Table Jobs(JobId nvarchar(20) Unique, SchId nvarchar(2),StartTime datetime)
--時間區間
Declare @StartTime datetime = '2021-08-20 00:00'
Declare @EndTime datetime = '2021-08-21 00:00'
預計每日00:00建立SQL AGENT排程建立當天任務,依照Schedule table的FrequencyMinute(分) 建立任務
ex: 排程A,任務間隔480分(8小時)
排程B,任務間隔720分(12小時)
預期得到結果
目前思考方向只能建立固定間隔的案件
Declare @StartTime datetime = '2021-08-20 00:00:00'
Select @StartTime AS '@StartTime'
Declare @EndTime datetime = DATEADD(DAY,1,@StartTime)
Select @EndTime AS '@EndTime'
--固定間隔480 mins新增一個任務
While( @StartTime < @EndTime )
Begin
Set @StartTime = DATEADD(minute,480,@StartTime)
Select @StartTime
End
再請各位SQL高手幫幫忙,感謝~
最後附上參考石頭的做法完成的結果
--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
--Jobs table任務資料表
Drop Table If Exists Jobs
Create Table Jobs(JobId nvarchar(20) Unique, SchId nvarchar(2),StartTime datetime)
Select * From Jobs
--時間區間
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
你可以嘗試使用Cte遞迴
Declare @StartTime datetime = '2021-08-20 00:00'
Declare @EndTime datetime = '2021-08-21 00:00'
;WITH CTE AS (
SELECT SchId,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
)
SELECT FORMAT(@StartTime,'yyyyMMddhhmmss') + ROW_NUMBER() OVER(ORDER BY SchId,ST) JobId,
SchId,
ST
FROM CTE
declare @result TABLE(JobId nvarchar(20),SchId nvarchar(20), StartTime datetime)
declare @jobid varchar(20),@schid varchar(10),@Freq int
declare @StartTime datetime,@EndTime datetime
declare @cnt int
set @cnt=1
declare myCursor cursor for
Select schid,FrequencyMinute From Schedule
open myCursor
FETCH NEXT FROM myCursor INTO @schid,@Freq
WHILE @@FETCH_STATUS = 0
BEGIN
set @StartTime= '2021-08-20 00:00:00'
set @EndTime= DATEADD(DAY,1,@StartTime)
While( @StartTime < @EndTime )
Begin
Select @jobid=convert(varchar,@StartTime,112)+ RIGHT(REPLICATE('0', 5) + cast(@cnt as nvarchar), 5)
Set @StartTime = DATEADD(minute,@Freq,@StartTime)
insert into @result
select @jobid,@schid,@StartTime
set @cnt=@cnt+1
End
FETCH NEXT FROM myCursor INTO @schid,@Freq
END
CLOSE myCursor
DEALLOCATE myCursor
select * from @result