Calculating time difference excluding out of hours

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculating time difference excluding out of hours

Author

Topic

paul27uk
Starting Member

9 Posts

Posted - 2007-09-12 : 10:16:49

I am new to SQL and I am looking for a solution on a query that would enable me to work out the time something has taken but excluding the elapsed time out of office hours.

For example, if office hours were 9-5 M-F and a job was started at 16:00hrs on the Friday and finished at 10:00 on the monday, the expected result in minutes would be 120.

Can any shed any light on how this would be possible?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-12 : 10:19:06

see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74645

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 11:02:02

[code]DECLARE @From DATETIME,
@To DATETIME

SELECT @From = '20070907 16:00',
@To = '20070910 10:00'

;WITH Yak (theDate, theWeekday, theSeconds)
AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @From), 0),
DATENAME(DW, @From),
CASE
WHEN CONVERT(CHAR(8), @From, 108) < '09:00:00' THEN 28800
WHEN CONVERT(CHAR(8), @From, 108) > '17:00:00' THEN 0
ELSE 61200 - DATEDIFF(SECOND, DATEADD(DAY, DATEDIFF(DAY, 0, @From), 0), @From)
END

UNION ALL

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @To), 0),
DATENAME(DW, @To),
CASE
WHEN CONVERT(CHAR(8), @To, 108) < '09:00:00' THEN 0
WHEN CONVERT(CHAR(8), @To, 108) > '17:00:00' THEN 28800
ELSE DATEDIFF(SECOND, DATEADD(DAY, DATEDIFF(DAY, 0, @To), 0), @To) - 32400
END

UNION ALL

SELECT 1 + theDate,
DATENAME(DW, 1 + theDate),
CASE
WHEN DATENAME(DW, 1 + theDate) IN ('Saturday', 'Sunday') THEN 0
ELSE 28800
END
FROM Yak
WHERE theDate < @To - 2
)

SELECT SUM(theSeconds) / 60.0 AS theMinutes
FROM Yak
OPTION (MAXRECURSION 0)

--SELECT theWeekday,
-- COUNT(*) AS theOccurence,
-- SUM(theSeconds) / 3600.0 AS theHours
--FROM Yak
--GROUP BY theWeekday
--OPTION (MAXRECURSION 0)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources