MSSQL实现工作时间计算指南

1. MSSQL实现工作时间计算

在MSSQL中实现工作时间计算，主要需要考虑到两个方面，一是如何定义工作日，二是如何计算工作时间。下面详细介绍如何实现。

1.1 如何定义工作日

通常情况下，我们把周一至周五的上班时间定义为工作日，其他时间为休息日。为了实现工作日的定义，我们可以创建一个日历表（calendar table），在该表中标记每一天是否属于工作日。

下面是日历表的创建代码：

CREATE TABLE calendar (
    day DATE PRIMARY KEY,
    is_workday BIT NOT NULL
)
GO

在日历表中，day表示日期，is_workday表示该日期是否是工作日，取值为0或1。

可以使用以下代码向日历表中插入数据：

INSERT INTO calendar (day, is_workday)
VALUES
    ('2022-01-01', 0), -- 休息日
    ('2022-01-02', 0), -- 休息日
    ('2022-01-03', 1), -- 工作日
    ('2022-01-04', 1), -- 工作日
    ('2022-01-05', 1), -- 工作日
    ('2022-01-06', 1), -- 工作日
    ('2022-01-07', 1), -- 工作日
    ('2022-01-08', 0)  -- 休息日
GO

以上代码向日历表中插入了2022年1月1日至8日的数据。

1.2 如何计算工作时间

在定义了工作日之后，就可以计算工作时间。一种简单有效的方法是计算两个时间点之间的工作时间。计算方法如下：

1. 找到起始时间（start_datetime）所在的工作日，并计算该工作日的上班时间开始于该时间之后或之前。如果上班时间开始于该时间之后，则起始时间为该时间，否则起始时间为当天的上班时间开始时间。

2. 找到结束时间（end_datetime）所在的工作日，并计算该工作日的下班时间结束于该时间之前或之后。如果下班时间结束于该时间之前，则结束时间为该时间，否则结束时间为当天的下班时间结束时间。

3. 计算起始时间至结束时间的时间差，即为工作时间。

下面是计算工作时间的SQL代码：

DECLARE @start_datetime DATETIME = '2022-01-03 09:00:00',
        @end_datetime DATETIME = '2022-01-06 18:00:00'
SELECT SUM(DATEDIFF(MINUTE, start_time, end_time)) AS work_minutes
FROM (
    -- 找到起始工作日上班时间开始时间
    SELECT start_datetime AS start_time,
           MIN(DATEADD(MINUTE, work_minutes, start_datetime)) AS end_time
    FROM (
        SELECT @start_datetime AS start_datetime,
               CAST(@start_datetime AS DATE) AS workday,
               (SELECT is_workday FROM calendar WHERE day = CAST(@start_datetime AS DATE)) AS is_workday,
               CAST('09:00:00' AS TIME) AS work_start_time,
               CAST('17:00:00' AS TIME) AS work_end_time,
               DATEDIFF(MINUTE, CAST('09:00:00' AS TIME), CAST('17:00:00' AS TIME)) AS work_minutes
    ) AS t
    WHERE is_workday = 1 AND start_datetime >= DATEADD(HOUR, DATEPART(HOUR, work_start_time) - 1, CAST(workday AS DATETIME))
    GROUP BY start_datetime, work_start_time
    UNION ALL
    -- 找到结束工作日下班时间结束时间
    SELECT MAX(DATEADD(MINUTE, -work_minutes, end_datetime)) AS start_time,
           end_datetime AS end_time
    FROM (
        SELECT @end_datetime AS end_datetime,
               CAST(@end_datetime AS DATE) AS workday,
               (SELECT is_workday FROM calendar WHERE day = CAST(@end_datetime AS DATE)) AS is_workday,
               CAST('09:00:00' AS TIME) AS work_start_time,
               CAST('17:00:00' AS TIME) AS work_end_time,
               DATEDIFF(MINUTE, CAST('09:00:00' AS TIME), CAST('17:00:00' AS TIME)) AS work_minutes
    ) AS t
    WHERE is_workday = 1 AND end_datetime <= DATEADD(HOUR, DATEPART(HOUR, work_end_time), CAST(workday AS DATETIME))
    GROUP BY end_datetime, work_end_time
    UNION ALL
    -- 中间工作日时间差
    SELECT DATEADD(DAY, 1, workday) AS start_time,
           DATEADD(MINUTE, work_minutes, DATEADD(DAY, 1, workday)) AS end_time
    FROM (
        SELECT CAST(@start_datetime AS DATE) AS cur_workday,
               DATEADD(DAY, 1, CAST(@start_datetime AS DATE)) AS workday,
               (SELECT is_workday FROM calendar WHERE day = workday) AS is_workday,
               CAST('09:00:00' AS TIME) AS work_start_time,
               CAST('17:00:00' AS TIME) AS work_end_time,
               DATEDIFF(MINUTE, CAST('09:00:00' AS TIME), CAST('17:00:00' AS TIME)) AS work_minutes
    ) AS t
    WHERE is_workday = 1
          AND DATEDIFF(DAY, cur_workday, CAST(@end_datetime AS DATE)) > 1
) AS t2

以上代码计算了2022年1月3日上午9点至1月6日下午6点之间的工作时间，单位为分钟。

2. 总结

本文介绍了如何在MSSQL中实现工作时间计算，包括如何定义工作日和如何计算工作时间。日历表的创建可以使用自己的方式进行调整，比如增加节假日标记等。如果需要计算不同的工作时间，可以根据实际情况进行调整。