MS SQL计算两行时间差：排除周末及工作外时段的实现

数据表格 #

需求 #

在MS SQL中计算连续两行数据间的时间差，需满足两个条件：

• 仅统计每日9:00-18:00的时长，排除其余时段；
• 排除周末（周六、周日）。

例如，第一行与第二行的时间差应为1小时30分钟（90分钟）：

• 2022-10-07（周五）17:30到18:00：30分钟
• 2022-10-08、10-09为周末，全部排除
• 2022-10-10（周一）9:00到10:00：60分钟
• 总计：30+60=90分钟

当前使用的查询语句仅计算原始时间差：

DATEDIFF(MINUTE, LAG(date) OVER (ORDER BY date), date)

需要为该语句添加上述过滤条件。

核心思路是：先通过LAG()获取上一行时间，再自定义逻辑计算两个时间点之间的有效工作时长（仅工作日9-18点）。以下提供两种实现方式：

方法1：自定义标量函数（适合小数据量） #

先创建一个计算有效分钟数的函数，再在查询中调用：

CREATE FUNCTION dbo.CalculateValidWorkMinutes(@StartDate DATETIME, @EndDate DATETIME)
RETURNS INT
AS
BEGIN
    DECLARE @TotalMinutes INT = 0;
    DECLARE @CurrentDate DATETIME = @StartDate;

    IF @StartDate >= @EndDate
        RETURN 0;

    WHILE @CurrentDate < @EndDate
    BEGIN
        DECLARE @DayStart DATETIME = CAST(CAST(@CurrentDate AS DATE) AS DATETIME);
        DECLARE @WorkStart DATETIME = DATEADD(HOUR, 9, @DayStart);
        DECLARE @WorkEnd DATETIME = DATEADD(HOUR, 18, @DayStart);

        -- 判断是否为工作日（默认周日为1，周一到周五对应2-6）
        IF DATEPART(WEEKDAY, @CurrentDate) BETWEEN 2 AND 6
        BEGIN
            DECLARE @EffectiveStart DATETIME = CASE WHEN @CurrentDate < @WorkStart THEN @WorkStart ELSE @CurrentDate END;
            DECLARE @EffectiveEnd DATETIME = CASE WHEN @EndDate > @WorkEnd THEN @WorkEnd ELSE @EndDate END;

            IF @EffectiveStart < @EffectiveEnd
                SET @TotalMinutes += DATEDIFF(MINUTE, @EffectiveStart, @EffectiveEnd);
        END

        SET @CurrentDate = DATEADD(DAY, 1, @DayStart);
    END

    RETURN @TotalMinutes;
END
GO

调用函数的查询语句：

SELECT
    Date,
    ID,
    Value,
    LAG(Date) OVER (ORDER BY Date) AS PreviousDate,
    dbo.CalculateValidWorkMinutes(LAG(Date) OVER (ORDER BY Date), Date) AS ValidWorkMinutesDiff
FROM
    YourTableName;

方法2：CTE集合运算（适合大数据量） #

无需创建函数，用递归CTE生成日期范围并计算有效时长：

WITH DateRange AS (
    -- 生成数据中覆盖的所有日期
    SELECT CAST(MIN(Date) AS DATE) AS WorkDate
    FROM YourTableName
    UNION ALL
    SELECT DATEADD(DAY, 1, WorkDate)
    FROM DateRange
    WHERE WorkDate < (SELECT CAST(MAX(Date) AS DATE) FROM YourTableName)
),
WorkDays AS (
    -- 筛选工作日
    SELECT WorkDate
    FROM DateRange
    WHERE DATEPART(WEEKDAY, WorkDate) BETWEEN 2 AND 6
),
RowWithPrevious AS (
    -- 获取每行的上一行时间
    SELECT
        Date,
        LAG(Date) OVER (ORDER BY Date) AS PreviousDate,
        ID,
        Value
    FROM YourTableName
)
SELECT
    r.Date,
    r.ID,
    r.Value,
    r.PreviousDate,
    SUM(
        DATEDIFF(MINUTE,
            CASE WHEN r.PreviousDate > DATEADD(HOUR, 18, w.WorkDate) THEN NULL
                 WHEN r.PreviousDate < DATEADD(HOUR, 9, w.WorkDate) THEN DATEADD(HOUR, 9, w.WorkDate)
                 ELSE r.PreviousDate END,
            CASE WHEN r.Date < DATEADD(HOUR, 9, w.WorkDate) THEN NULL
                 WHEN r.Date > DATEADD(HOUR, 18, w.WorkDate) THEN DATEADD(HOUR, 18, w.WorkDate)
                 ELSE r.Date END
        )
    ) AS ValidWorkMinutesDiff
FROM RowWithPrevious r
LEFT JOIN WorkDays w ON w.WorkDate BETWEEN CAST(r.PreviousDate AS DATE) AND CAST(r.Date AS DATE)
WHERE r.PreviousDate IS NOT NULL
GROUP BY r.Date, r.ID, r.Value, r.PreviousDate
OPTION (MAXRECURSION 0); -- 日期范围超过100天时需设置此选项

注意事项 #

• DATEPART(WEEKDAY)的取值依赖SET DATEFIRST设置，默认周日为1，若你的服务器设置周一为1，需将判断条件改为BETWEEN 1 AND 5。

内容的提问来源于stack exchange，提问作者Pankash Mann