SQL计算两个日期之间的工作小时数，不包括周末和非工作时间

Question

我希望计算一件物品使用了多少小时，使用开始和结束日期进行计算。开始时间和结束时间始终为工作日，但当然可能跨越多个星期。我已经成功地排除了周末，但不确定如何排除非工作时间。

示例：

declare @d1 datetime, @d2 datetime
select @d1 = '04/25/2014 02:42',  @d2 = '04/28/2014 17:14'

select (datediff(mi, @d1, @d2) / 60) - (datediff(wk, @d1, @d2) * 48)

工作时间为周一06:00至周五21:00。

因此，在所述示例中，它返回38，但我认为应该是29。

非常感谢您的帮助！谢谢。

[编辑]

@Fnightangel

我的朋友，你真是个天才！非常感谢。这是一个很好的解决方案——清晰简明，起到了很好的作用：）。

我改进了你的代码（正如你自己所说，它并不完全正确），通过在逻辑的开头添加IF语句：

IF (DATEDIFF(DD, @D1, @D2) <= 0) OR (DATEDIFF(WK, @D1, @D2) * 2 <= 0)
    RETURN
      (DATEDIFF(MI, @D1, @D2) / 60)
ELSE
    ....

- user2439970

---

平日晚上算工作时间吗？ - Daniel E.

是的，正确的 - 工作时间为每周24小时，除了星期一早上（直到早上6点）和星期五晚上（21:00至午夜）。 - user2439970

查找日历表的概念。 - Aaron Bertrand

Answer 1

我很不确定这是否是最佳解决方案，但这是我能想到的唯一一个。

看看这是否有帮助：

定义你的变量。

DECLARE @D1 DATETIME, @D2 DATETIME, @CURRENTDATE DATETIME
SELECT @D1 = '04/25/2014 02:42',  @D2 = '04/28/2014 17:14'

然后，我创建了两个表格来存储所有的星期一和星期五，以便之后计算我们需要减去多少小时。

CREATE TABLE #MONDAYS(MONDAYS VARCHAR(8) NULL)
CREATE TABLE #FRIDAYS(FRIDAYS VARCHAR(8) NULL)

SET @CURRENTDATE = CONVERT(VARCHAR(8), @D1, 112)
WHILE @CURRENTDATE <= CONVERT(VARCHAR(8), @D2, 112)
BEGIN
    IF DATEPART(DW, @CURRENTDATE) = 2 --MONDAY
        INSERT INTO #MONDAYS(MONDAYS) VALUES (CONVERT(VARCHAR(8), @CURRENTDATE, 112))
    ELSE
        IF DATEPART(DW, @CURRENTDATE) = 6 --FRIDAY
            INSERT INTO #FRIDAYS(FRIDAYS) VALUES (CONVERT(VARCHAR(8), @CURRENTDATE, 112))

    SET @CURRENTDATE = DATEADD(DAY, 1, CONVERT(VARCHAR(8), @CURRENTDATE, 112))
END

根据周一和周五计算非工作时间

DECLARE @NONWORKING AS INT

SET @NONWORKING = ((SELECT COUNT(*) FROM #FRIDAYS) * 3) -- 3 NON WORKING HOURS ON FRIDAYS
                + ((SELECT COUNT(*) FROM #MONDAYS) * 6) -- 6 NON WORKING HOURS ON MONDAYS

最后，计算。

SELECT
(DATEDIFF(MI, @D1, @D2) / 60) 
- (DATEDIFF(WK, @D1, @D2) * 48)
- @NONWORKING

希望这能有所帮助。请随意改进我的想法。

- fnightangel

---

我猜当开始和结束日期是周一/周五，但它不在非工作时间间隔之内时，必须重新思考。 - fnightangel

非常感谢。请查看我的第一篇帖子中的编辑，以了解感谢和进一步改进。 - user2439970