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/02/2003’, ‘14:00:00 05/04/2003’ UNIOn
SELECT 5, ‘07:00:00 05/02/2003’, ‘14:00:00 05/02/2003’ UNIOn
SELECT 6, ‘14:00:00 05/02/2003’, ‘23:00:00 05/02/2003’ UNIOn
SELECT 7, ‘07:00:00 05/02/2003’, ‘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, ‘07:00:00 05/02/2003’, ‘23:00:00 05/02/2003’
-- SET DATEFIRST to U.S. English default value of 7.SET DATEFIRST 7SELECT ID, DateStart, DateEnd, ConVERT(VARCHAR, Minutes/60) +':'+ ConVERT(VARCHAR, Minutes % 60) AS ReactionTimeFROM ( SELECt ID, DateStart, DateEnd, 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, DateStart, DateEnd, 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,DateStart, DateEnd) AS DayDiff, ID, DateStart, DateEnd, DATEPART(minute,DateStart)+DATEPART(hour,DateStart)*60 AS MinutesStart, DATEPART(minute,DateEnd)+DATEPART(hour,DateEnd)*60 AS MinutesEnd FROM ( SELECt ID, CASE WHEN DATEPART(dw, DateStart) = 7 THEN DATEADD(SECOND, 1, DATEADD(DAY, DATEDIFF(DAY, 0, DateStart), 2)) WHEN DATEPART(dw, DateStart) = 1 THEN DATEADD(SECOND, 1, DATEADD(DAY, DATEDIFF(DAY, 0, DateStart), 1)) ELSE DateStart END AS DateStart, CASE WHEN DATEPART(dw, DateEnd) = 7 THEN DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, DateEnd), 0)) WHEN DATEPART(dw, DateEnd) = 1 THEN DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, DateEnd), -1)) ELSE DateEnd END AS DateEnd FROM @Dates2 )Weekends )InMinutes )Overtime)Calculation
