sql server-sql返回时间包含2个日期，不包括周末和非轮班时间

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

你，我的朋友，真是个天才！非常感谢。这是一个很好的解决方案-clear&简洁而有效：）。

我改进了你的代码（正如你自己提到的，它不是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'

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

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

希望这能有所帮助。我可以自由地改进我的想法。