我需要计算两个日期之间的DateDiff(小时),但仅限于营业时间(8:30 – 16:00,没有周末).然后根据下面的示例将此结果放入Reaction_Time列.
ID Date Reaction_Time Overdue 1 29.04.2003 15:00:00 1 30.04.2003 11:00:00 3:30 2 30.04.2003 14:00:00 2 01.05.2003 14:00:00 7:30 YES
*注意：我没有检查示例中的日期是否是假期.
我正在使用sql Server 2005
这将与更大的查询相结合,但现在我需要的是这个开始,我将试图弄清楚如何将它们全部放在一起.谢谢您的帮助！
编辑：嘿,谢谢大家的回复.但是由于sql方面的解决方案明显很复杂,我们决定在Excel中执行此操作,而不管报告将在何处移动.很抱歉这个问题,但我确实认为这比这更简单.事实上,我们没有时间.
解决方法
DECLARE @BusHourStart DATETIME,@BusHourEnd DATETIME
SELECT @BusHourStart = '08:30:00',@BusHourEnd = '16:00:00'
DECLARE @BusMinutesStart INT,@BusMinutesEnd INT
SELECT @BusMinutesStart = DATEPART(minute,@BusHourStart)+DATEPART(hour,@BusHourStart)*60,@BusMinutesEnd = DATEPART(minute,@BusHourEnd)+DATEPART(hour,@BusHourEnd)*60
DECLARE @Dates2 TABLE (ID INT,DateStart DATETIME,DateEnd DATETIME)
INSERT INTO @Dates2
SELECT 1,'15:00:00 04/29/2003','11:00:00 04/30/2003' UNION
SELECT 2,'14:00:00 04/30/2003','14:00:00 05/01/2003' UNION
SELECT 3,'14:00:00 05/02/2003','14:00:00 05/06/2003' UNION
SELECT 4,'14:00:00 05/04/2003' UNION
SELECT 5,'07:00:00 05/02/2003','14:00:00 05/02/2003' UNION
SELECT 6,'23:00:00 05/02/2003' UNION
SELECT 7,'08:00:00 05/02/2003' UNION
SELECT 8,'22:00:00 05/02/2003','23:00:00 05/03/2003' UNION
SELECT 9,'08:00:00 05/03/2003','23:00:00 05/04/2003' UNION
SELECT 10,'23:00:00 05/02/2003'
-- SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7
SELECT ID,DateStart,DateEnd,CONVERT(VARCHAR,Minutes/60) +':'+ CONVERT(VARCHAR,Minutes % 60) AS ReactionTime
FROM (
SELECT ID,Overtime,CASE
WHEN DayDiff = 0 THEN
CASE
WHEN (MinutesEnd - MinutesStart - Overtime) > 0 THEN (MinutesEnd - MinutesStart - Overtime)
ELSE 0
END
WHEN DayDiff > 0 THEN
CASE
WHEN (StartPart + EndPart - Overtime) > 0 THEN (StartPart + EndPart - Overtime)
ELSE 0
END + DayPart
ELSE 0
END AS Minutes
FROM(
SELECT ID,DayDiff,MinutesStart,MinutesEnd,CASE WHEN(@BusMinutesStart - MinutesStart) > 0 THEN (@BusMinutesStart - MinutesStart) ELSE 0 END +
CASE WHEN(MinutesEnd - @BusMinutesEnd) > 0 THEN (MinutesEnd - @BusMinutesEnd) ELSE 0 END AS Overtime,CASE WHEN(@BusMinutesEnd - MinutesStart) > 0 THEN (@BusMinutesEnd - MinutesStart) ELSE 0 END AS StartPart,CASE WHEN(MinutesEnd - @BusMinutesStart) > 0 THEN (MinutesEnd - @BusMinutesStart) ELSE 0 END AS EndPart,CASE WHEN DayDiff > 1 THEN (@BusMinutesEnd - @BusMinutesStart)*(DayDiff - 1) ELSE 0 END AS DayPart
FROM (
SELECT DATEDIFF(d,DateEnd) AS DayDiff,ID,DATEPART(minute,DateStart)+DATEPART(hour,DateStart)*60 AS MinutesStart,DateEnd)+DATEPART(hour,DateEnd)*60 AS MinutesEnd
FROM (
SELECT ID,CASE
WHEN DATEPART(dw,DateStart) = 7
THEN DATEADD(SECOND,1,DATEADD(DAY,DATEDIFF(DAY,DateStart),2))
WHEN DATEPART(dw,DateStart) = 1
THEN DATEADD(SECOND,1))
ELSE DateStart END AS DateStart,DateEnd) = 7
THEN DATEADD(SECOND,-1,DateEnd),0))
WHEN DATEPART(dw,DateEnd) = 1
THEN DATEADD(SECOND,-1))
ELSE DateEnd END AS DateEnd FROM @Dates2
)Weekends
)InMinutes
)Overtime
)Calculation
