[已解決]MS SQL 新增任務資料by排程資料表

ms sql

JT 2021-08-21 00:14:49 ‧ 1547 瀏覽

分享至

各位先進們大家好，
目前我有兩個Table

Schedule table排程資料表
( SchId排程編號, FrequencyMinute建立任務頻率(分) )

--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任務資料表
( JobId任務編號, SchId排程編號, StartTime任務開始時間 )

--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

dbfiddle demo

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

石頭

iT邦高手 1 級 ‧ 2021-08-21 13:20:12

最佳解答

你可以嘗試使用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

sqlfiddle

回應 1
分享
檢舉

JT iT邦研究生 5 級 ‧ 2021-08-21 21:39:19 檢舉

感謝石頭的回覆
又學到了CTE遞迴的方法。

登入發表回應

allenlwh

iT邦高手 1 級 ‧ 2021-08-21 10:34:25

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