sql-server - 两个日期之间的 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

你是我的 friend ，真是个天才!非常感谢你。这是一个很好的解决方案 - 清晰简洁，效果很好 :)。

我改进了你的代码(正如你自己提到的，它不是 100% 正确)，将这个 IF 语句添加到逻辑的开头:

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

Answer 1

我真的不知道这是否是最佳解决方案，但这是我能想到的唯一解决方案。

看看这是否有帮助:

定义变量

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

然后，我创建了 2 个表来存储所有星期一和所有星期五，以便稍后计算我们必须分多少小时

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

希望对您有所帮助。随意改进我的ideia。