我有各种SSIS作业，它们将在一周中的不同日子运行，并且需要参考前一个作业在运行之前是否已运行(通过日志表时间戳(。由于每种作业类型都有不同的时间表(取决于第三方集成需求(，我希望有一个通用的脚本来设置一些变量，这些变量可以根据需要在每个脚本中选择性地引用。
例如，"每隔一个星期三，‘作业A’运行一次，每隔一个周三，‘作业B’交错运行一次。如果‘作业A"在上周三没有运行(留下其运行的日志时间戳(，我就不需要运行‘作业B"。">
因此，我搜索了许多不同的方法，下面是我想到的：
-- Sets the first day of the week to Monday (used in finding 'previous day' below)
SET DATEFIRST 1
DECLARE
@DATE_TIME SMALLDATETIME
, @LastWeekMon SMALLDATETIME
, @LastWeekTue SMALLDATETIME
, @LastWeekWed SMALLDATETIME
, @LastWeekThu SMALLDATETIME
, @LastWeekFri SMALLDATETIME
, @LastWeekSat SMALLDATETIME
, @LastWeekSun SMALLDATETIME
, @PrevMon SMALLDATETIME
, @PrevTue SMALLDATETIME
, @PrevWed SMALLDATETIME
, @PrevThu SMALLDATETIME
, @PrevFri SMALLDATETIME
, @PrevSat SMALLDATETIME
, @PrevSun SMALLDATETIME
, @DontAllowPrevToBeToday BIT
, @DAY_SPECIFIC_ADJUST INT
--SET CONSTANTS
--SET @DATE_TIME = GETDATE();
SET @DATE_TIME = '2018-07-21 00:00:00';
SET @DontAllowPrevToBeToday = 1; --true
select @DATE_TIME as DATE_TIME
-----------------------------------------
--GET LAST WEEK'S DAY-OF-THE-WEEK DATE (REGARDLESS OF SYSTEM ZERO DATE AND DATEFIRST SETTINGS)
SELECT @LastWeekMon = DATEADD(week,
DATEDIFF(week,'19000101',@DATE_TIME),'1899-12-25T00:00:00') --12/25/1899 is a Monday
SELECT @LastWeekTue = DATEADD(week,
DATEDIFF(week,'19000101',@DATE_TIME),'1899-12-26T00:00:00') --12/26/1899 is a Tuesday
SELECT @LastWeekWed = DATEADD(week,
DATEDIFF(week,'19000101',@DATE_TIME),'1899-12-27T00:00:00') --12/27/1899 is a Wednesday
SELECT @LastWeekThu = DATEADD(week,
DATEDIFF(week,'19000101',@DATE_TIME),'1899-12-28T00:00:00') --12/28/1899 is a Thursday
SELECT @LastWeekFri = DATEADD(week,
DATEDIFF(week,'19000101',@DATE_TIME),'1899-12-29T00:00:00') --12/29/1899 is a Friday
SELECT @LastWeekSat = DATEADD(week,
DATEDIFF(week,'19000101',@DATE_TIME),'1899-12-30T00:00:00') --12/30/1899 is a Saturday
SELECT @LastWeekSun = DATEADD(week,
DATEDIFF(week,'19000101',@DATE_TIME),'1899-12-31T00:00:00') --12/31/1899 is a Sunday
SELECT @LastWeekMon as LastWeekMon, @LastWeekTue as LastWeekTue, @LastWeekWed as LastWeekWed, @LastWeekThu as LastWeekThu, @LastWeekFri as LastWeekFri, @LastWeekSat as LastWeekSat, @LastWeekSun as LastWeekSun
-----------------------------------------
SET @DAY_SPECIFIC_ADJUST = DATEDIFF(day, 0, @DATE_TIME) + -(0); --Mon + 0 = 1 = Monday
SELECT @PrevMon =
CASE
WHEN @DontAllowPrevToBeToday = 1 And DATEDIFF(day, 0, @DATE_TIME) = DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0) THEN
--PREV DAY IS TODAY, USE DAY FROM LAST WEEK
@LastWeekMon
ELSE
--PREV DAY SELECT
DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0)
END
SET @DAY_SPECIFIC_ADJUST = DATEDIFF(day, 0, @DATE_TIME) + -(1); --Mon + 1 = 2 = Tuesday
SELECT @PrevTue =
CASE
WHEN @DontAllowPrevToBeToday = 1 And DATEDIFF(day, 0, @DATE_TIME) = DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0) THEN
--PREV DAY IS TODAY, USE DAY FROM LAST WEEK
@LastWeekTue
ELSE
--PREV DAY SELECT
DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0)
END
SET @DAY_SPECIFIC_ADJUST = DATEDIFF(day, 0, @DATE_TIME) + -(2); --Mon + 2 = 3 = Wednesday
SELECT @PrevWed =
CASE
WHEN @DontAllowPrevToBeToday = 1 And DATEDIFF(day, 0, @DATE_TIME) = DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0) THEN
--PREV DAY IS TODAY, USE DAY FROM LAST WEEK
@LastWeekWed
ELSE
--PREV DAY SELECT
DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0)
END
SET @DAY_SPECIFIC_ADJUST = DATEDIFF(day, 0, @DATE_TIME) + -(3); --Mon + 3 = 4 = Thursday
SELECT @PrevThu =
CASE
WHEN @DontAllowPrevToBeToday = 1 And DATEDIFF(day, 0, @DATE_TIME) = DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0) THEN
--PREV DAY IS TODAY, USE DAY FROM LAST WEEK
@LastWeekThu
ELSE
--PREV DAY SELECT
DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0)
END
SET @DAY_SPECIFIC_ADJUST = DATEDIFF(day, 0, @DATE_TIME) + -(4); --Mon + 4 = 5 = Friday
SELECT @PrevFri =
CASE
WHEN @DontAllowPrevToBeToday = 1 And DATEDIFF(day, 0, @DATE_TIME) = DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0) THEN
--PREV DAY IS TODAY, USE DAY FROM LAST WEEK
@LastWeekFri
ELSE
--PREV DAY SELECT
DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0)
END
SET @DAY_SPECIFIC_ADJUST = DATEDIFF(day, 0, @DATE_TIME) + -(5); --Mon + 5 = 6 = Saturday
SELECT @PrevSat =
CASE
WHEN @DontAllowPrevToBeToday = 1 And DATEDIFF(day, 0, @DATE_TIME) = DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0) THEN
--PREV DAY IS TODAY, USE DAY FROM LAST WEEK
@LastWeekSat
ELSE
--PREV DAY SELECT
DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0)
END
SET @DAY_SPECIFIC_ADJUST = DATEDIFF(day, 0, @DATE_TIME) + -(6); --Mon + 6 = 7 = Sunday
SELECT @PrevSun =
CASE
WHEN @DontAllowPrevToBeToday = 1 And DATEDIFF(day, 0, @DATE_TIME) = DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0) THEN
--PREV DAY IS TODAY, USE DAY FROM LAST WEEK
@LastWeekSun
ELSE
--PREV DAY SELECT
DATEADD(day, DATEDIFF(day, 0, @DATE_TIME - (@DAY_SPECIFIC_ADJUST) %7), 0)
END
SELECT @PrevMon as PrevMon, @PrevTue as PrevTue, @PrevWed as PrevWed, @PrevThu as PrevThu, @PrevFri as PrevFri, @PrevSat as PrevSat, @PrevSun as PrevSun
这听起来合理吗？有没有更简单的方法来实现这一点？我肯定会有的，但我被难住了。
感谢您的帮助和反馈！
免责声明这本质上是一条长评论，但由于涉及大量代码和评论，因此作为回答发布。
来自@deeg的《如何在SQL中获取上次运行的作业详细信息》。
SELECT t2.instance_id
,t1.name as JobName
,t2.step_id as StepID
,t2.step_name as StepName
,CONVERT(CHAR(10), CAST(STR(t2.run_date,8, 0) AS DATETIME), 111) as RunDate
,STUFF(STUFF(RIGHT('000000' + CAST ( t2.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') as RunTime
,t2.run_duration
,CASE t2.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END as ExecutionStatus
,t2.message as MessageGenerated
FROM msdb.dbo.sysjobs t1
JOIN msdb.dbo.sysjobhistory t2
ON t1.job_id = t2.job_id
--Join to pull most recent job activity per job, not job step
JOIN (
SELECT TOP 1
t1.job_id
,t1.start_execution_date
,t1.stop_execution_date
FROM msdb.dbo.sysjobactivity t1
--If no job_id detected, return last run job
ORDER
BY last_executed_step_date DESC
) t3
--Filter on the most recent job_id
ON t1.job_id = t3.job_Id
--Filter out job steps that do not fall between start_execution_date and stop_execution_date
AND CONVERT(DATETIME, CONVERT(CHAR(8), t2.run_date, 112) + ' '
+ STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), t2.run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
BETWEEN t3.start_execution_date AND t3.stop_execution_date
将为您提供在开始执行日期和停止执行日期之间运行的所有作业(根据需要进行更改(。如果作业A在RunDate(和/或RunTime(之间的日期差异在七天内成功。然后运行作业B。
也就是。。。其中t2.JobName = 'yourjob'和t2.run_status = 1，然后运行作业B。
这种逻辑有帮助吗？请注意，如果需要，您可以过滤掉返回的内容，以摆脱单独的步骤。