TimeSpan between DateTime in business hours only 8:30am - 5:00pm M-F

Question

I am trying to get the total amount of time in hours between when a job is logged in a ticketing system to when then job is due. For example if the job is logged at 4:00pm on a Friday and is due at 10:30am on Monday it will have a 3 hour time span. (excluding weekends and our of business hours)

I can get the TimeSpan for total time but I have no idea how to get the TimeSpan in business hours only?

I am using LINQpad as a code scratchpad and I am querying a database.

Below is an example of the my query which gets the total TimeSpan rather than the TimeSpan in Business Hours.

(from so in TblServiceOrders 
 where so.DateReceived != null
    && so.TimeReceived != null
    && so.DateRequested != null
    && so.TimeRequested != null
 orderby so.SONumber descending
 select new
 {
   so.SONumber,
   received = so.DateReceived.Value.AddTicks(so.TimeReceived.Value.Ticks),
   requested = so.DateRequested.Value.AddTicks(so.TimeRequested.Value.Ticks),
   TimeSpan = (so.DateReceived.Value.AddTicks(so.TimeReceived.Value.Ticks))
              .Subtract(so.DateRequested.Value.AddTicks(so.TimeRequested.Value.Ticks))
 }).Take(20)

Answer 1

I hope there are better ways to do this however since my TimeRange is never longer than a few days I have been able to achieve it all within the Linq Query with the below code:

This only works with LINQ-to-SQL which is unfortunate as it cannot be used in Entity Framework.

from so in TblServiceOrders

where so.DateReceived != null &&
      so.TimeReceived != null &&
      so.DateRequested != null &&
      so.TimeRequested != null

//Get the named day of the week for start of TimeRange
let DayReceived = so.DateReceived.Value.DayOfWeek
let DayRequested = so.DateRequested.Value.DayOfWeek

//Find out how many days pass within the TimeRange
let SLADaysLapesed = ((so.DateRequested.Value.Day) - (so.DateReceived.Value.Day))

//Find out if any of those days past fall onto a Sat and therefor over a weekend.
//This will only work for the first weekend within the TimeRange.
let SLAContainsWE = (DayReceived == DayOfWeek.Monday && SLADaysLapesed <= 4 ||
                     DayReceived == DayOfWeek.Tuesday && SLADaysLapesed <= 3 ||
                     DayReceived == DayOfWeek.Wednesday && SLADaysLapesed <= 2 ||
                     DayReceived == DayOfWeek.Thursday && SLADaysLapesed <= 1 ||
                     DayReceived == DayOfWeek.Friday && SLADaysLapesed <= 0 ? false : true )

//Work out how many hours pass outside of business hours. (930 minutes)                  
let SLATotalDailyAH = (new TimeSpan(0, (930 * SLADaysLapesed), 0))

//Work out if the TimeRange falls over a weekend. Time declared in mins is equal to two standard working days 8.5hr x 2 in my case.                
let TotalWEHours = (SLAContainsWE == true ? new TimeSpan(0,1020,0) : new TimeSpan(0,0,0))

//Work out how many Business hours have passed by removing the total after hours.
let SLAHours =  (
                ((so.DateRequested.Value.AddHours(so.TimeRequested.Value.Hour)).AddMinutes(so.TimeRequested.Value.Minute)).Subtract
                ((so.DateReceived.Value.AddHours(so.TimeReceived.Value.Hour)).AddMinutes(so.TimeReceived.Value.Minute)).Subtract
                (SLATotalDailyAH).Subtract(TotalWEHours)
                )   

select SLAHours