时间戳

首页 > 解决方案 > TSQL：查找上一个“星期几”逻辑的日期。是否有任何边缘情况会失败并且可以简化吗？

sql - TSQL：查找上一个“星期几”逻辑的日期。是否有任何边缘情况会失败并且可以简化吗？

问题描述

我有各种 SSIS 作业，它们将在一周中的不同日子运行，并且需要参考之前的作业在运行之前是否已经运行（通过日志表时间戳）。由于每种作业类型都有不同的时间表（取决于第 3 方集成需求），我希望有一个通用脚本来设置一些变量，这些变量可以根据需要在每个脚本中选择性地引用。

例如，“每隔一个星期三，‘作业 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

这是声音吗？有没有更简单的方法来实现这一点？我确信必须有，但我很难过。

感谢您的帮助和反馈！

标签： sqlsql-serverdate

解决方案

免责声明 这本质上是一个很长的评论，但作为答案发布，因为它涉及大量的代码和评论。

来自@deeg的 How to get last run job details in 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）之间的日期差异在 7 天内。然后运行作业 B。

aka... where t2.JobName = 'yourjob'and t2.run_status = 1，然后运行 ​​Job B。

这种逻辑会有帮助吗？请注意，如有必要，您可以过滤掉返回的内容以摆脱各个步骤。

推荐阅读