Calculating the elapsed working hours between 2 datetime

Question

Given two datetimes. What is the best way to calculate the number of working hours between them. Considering the working hours are Mon 8 - 5.30, and Tue-Fri 8.30 - 5.30, and that potentially any day could be a public holiday.

This is my effort, seem hideously inefficient but in terms of the number of iterations and that the IsWorkingDay method hits the DB to see if that datetime is a public holiday.

Can anyone suggest any optimizations or alternatives.

 public decimal ElapsedWorkingHours(DateTime start, DateTime finish)
        {

            decimal counter = 0;

            while (start.CompareTo(finish) <= 0)
            {   
                if (IsWorkingDay(start) && IsOfficeHours(start))
                {
                    start = start.AddMinutes(1);
                    counter++;
                }
                else
                {
                    start = start.AddMinutes(1);
                }
            }

            decimal hours;

            if (counter != 0)
            {
                hours = counter/60;
            }

            return hours;
        }

Answer 1

Before you start optimizing it, ask yourself two questions.

a) Does it work?

b) Is it too slow?

Only if the answer to both question is "yes" are you ready to start optimizing.

Apart from that

• you only need to worry about minutes and hours on the start day and end day. Intervening days will obviously be a full 9/9.5 hours, unless they are holidays or weekends
• No need to check a weekend day to see if it's a holiday

Here's how I'd do it

// Normalise start and end    
while start.day is weekend or holiday, start.day++, start.time = 0.00am
    if start.day is monday,
        start.time = max(start.time, 8am)
    else
        start.time = max(start.time, 8.30am)
while end.day is weekend or holiday, end.day--, end.time = 11.59pm
end.time = min(end.time, 5.30pm)

// Now we've normalised, is there any time left?    
if start > end
   return 0

// Calculate time in first day    
timediff = 5.30pm - start.time
day = start.day + 1
// Add time on all intervening days
while(day < end.day)
   // returns 9 or 9.30hrs or 0 as appropriate, could be optimised to grab all records
   // from the database in 1 or 2 hits, by counting all intervening mondays, and all
   // intervening tue-fris (non-holidays)
   timediff += duration(day) 

// Add time on last day
timediff += end.time - 08.30am
if end.day is Monday then
    timediff += end.time - 08.00am
else
    timediff += end.time - 08.30am

return timediff

You could do something like SELECT COUNT(DAY) FROM HOLIDAY WHERE HOLIDAY BETWEEN @Start AND @End GROUP BY DAY

to count the number of holidays falling on Monday, Tuesday, Wednesday, and so forth. Probably a way of getting SQL to count just Mondays and non-Mondays, though can't think of anything at the moment.

Answer 2

especially considering the IsWorkingDay method hits the DB to see if that day is a public holiday

If the problem is the number of queries rather than the amount of data, query the working day data from the data base for the entire day range you need at the beginning instead of querying in each loop iteration.