iT邦幫忙

0

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

JT 2021-08-21 00:14:49959 瀏覽

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

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

https://ithelp.ithome.com.tw/upload/images/20210821/20129970GawYxsGYAj.jpg

  1. Jobs table任務資料表
    ( JobId任務編號, SchId排程編號, StartTime任務開始時間 )
--Jobs table任務資料表
Drop Table If Exists Jobs
Create Table Jobs(JobId nvarchar(20) Unique, SchId nvarchar(2),StartTime datetime)
  1. 時間範圍
--時間區間
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小時)

預期得到結果
https://ithelp.ithome.com.tw/upload/images/20210821/201299700wBHBz136d.jpg

目前思考方向只能建立固定間隔的案件

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

https://ithelp.ithome.com.tw/upload/images/20210821/20129970f4oWPMLVav.jpg

再請各位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

2 個回答

1
石頭
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

JT iT邦新手 5 級 ‧ 2021-08-21 21:39:19 檢舉

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

0
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

https://ithelp.ithome.com.tw/upload/images/20210821/20033493abYKEt8HLY.jpg

JT iT邦新手 5 級 ‧ 2021-08-21 21:36:32 檢舉

感謝allenlwh的回覆
Cursor也是一個好方法。

我要發表回答

立即登入回答