CODEWITHSUNDEEP
sql-servert-sqldatetimesql-server-2012elapsedtime
FBeveridge
FBeveridge

Reputation: 121

SQL Server 2012 - Calculating time elapsed during working hours between two datetimes excluding weekends

I have a table with date assigned (datetime) and date resolved (datetime).

I am trying to calculate the time (in seconds) elapsed during work hours and excluding weekends.

Working hours being 09:00:00.000000 to 17:30:00.000000, Monday through Friday.

So if a project was assigned on Friday at 16:00:00.000000 on Friday and was resolved on Monday at 10:30:00.000000 the total elapsed time would be 3 hours or 180 minutes or 10,800 seconds.

I've seen some posts that excluded weekend time but not work hours --- any help is greatly appreciated, thanks!

Upvotes: 0

Views: 318

Answers (2)

Ann L.
Ann L.

Reputation: 13975

While it might be possible to do all of this in a single expression, I wouldn't try that. Too many potential human errors (including transcription errors), extremely lengthy, and tricky to debug or modify.

Instead, I'd do it as a user-defined function in T-SQL, if I had to return a data set with this already calculated. If I was going to display it or produce a report with this information, I'd consider doing it in code. But a user-defined T-SQL function will be the most flexible solution, I'll grant that.

I don't already have something like this to hand, so I'm going to give you an algorithm.

Assume we have two date-time values, @Date1 and @Date2. We'll assume that @Date1 < @Date2. We'll also assume that @Date1 and @Date2 both are on working days and within working hours.

If @Date1 and @Date2 are on the same date, return the difference in seconds between @Date1 and @Date2.

Let @firstMorning be the first 9 am after @Date1. If @Date1 is on 9 am, then @firstMorning = @Date1.

Let @lastMorning be the last 9 am after @firstMorning and before @Date2. (Start time of @Date2, basically.)

Let @workWeeks = number of Monday-Friday weeks between @firstMorning and @lastMorning.

Let @firstWeekDays = Number of days between @firstMorning + Friday 9:00 am of the same week. If @firstMorning = Friday 9:00 am, set @firstWeekDays to 0.

Let @lastWeekDays = Number of days between Monday 9:00 am of the last week and @lastMorning. If @lastMorning = Monday 9:00 am, set @lastWeekDays to 0.

Let @SecondsPerDay = 8.5 * 3600.

Return (@workWeeks * 5 * @SecondsPerDay) + (@firstWeekDays * @SecondsPerDay) + (seconds between @Date1 and 5:30 pm on @Date1) + (@lastWeekDays * @SecondsPerDay) + (seconds between @lastMorning and @Date2)

Note that this logic hasn't been tested with code: if it had, I'd give you the code. :)

Upvotes: 0

Polux2
Polux2

Reputation: 602

You can try this :

select datediff(hh, @d1, @d2) - (datediff(wk, @d1, @d2) * 2 * 8.5) - datediff(dd, @d1, @d2) * (24-8.5)

Upvotes: 0

Related Questions