Finding elapsed time in SQL Server is easy, so long as the clock is always running: just use DATEDIFF. But you often need to find elapsed time excluding certain periods, like weekends, nights, or holidays. A fellow SQL Server MVP recently posed a variation on this problem: to find the number of minutes between two times, where the clock is running only from 6:00am-6:00pm, Monday-Friday. He needed this to compute how long trouble tickets stayed at a help desk that was open for those hours.

I came up with a function DeskTimeDiff_minutes(@from,@to) for him. It requires a permanent table that spans the range of times you might care about, holding one row for every time the clock is turned on or off, weekdays at 6:00am and 6:00pm in this case.

The table also holds an “absolute business time” in minutes (ABT-m): the total number of “help desk open” minutes since a fixed but arbitrary “beginning of time.” Elapsed help desk time is then simply the difference between ABT-m values. While the table only records the ABT-m 10 times a week, you can find the ABT-m for an arbitrary datetime @d easily. Find the row of the table with time d closest to @d but not later. In that row you’ll find the ABT-m at time d, and you’ll also find out whether the clock was (or will be) running or not between d and @d. If not, the ABT-m at time @d is the same as at time d. Otherwise, add the number of minutes between d and @d.

Here’s the code. The reference table here is good from early 2000 until well past 2050, and you can easily extend it or adapt it to other business rules. A larger permanent table of times shouldn’t affect performance, because the function only performs (two) index seek lookups on the table.

If you cut and paste this for your own use, watch out for “smart quotes” or other WordPress/Live Writer formatting quirks.

create table Minute_Count(
  d datetime primary key,
  elapsed_minutes int not null,
  timer varchar(10) not null check (timer in (‘Running’,’Stopped’))
);

insert into Minute_Count values (‘2000-01-03T06:00:00′,0,’Running’);
insert into Minute_Count values (‘2000-01-03T18:00:00′,12*60,’Stopped’);

insert into Minute_Count values (‘2000-01-04T06:00:00′,12*60,’Running’);
insert into Minute_Count values (‘2000-01-04T18:00:00′,24*60,’Stopped’);

insert into Minute_Count values (‘2000-01-05T06:00:00′,24*60,’Running’);
insert into Minute_Count values (‘2000-01-05T18:00:00′,36*60,’Stopped’);

insert into Minute_Count values (‘2000-01-06T06:00:00′,36*60,’Running’);
insert into Minute_Count values (‘2000-01-06T18:00:00′,48*60,’Stopped’);

insert into Minute_Count values (‘2000-01-07T06:00:00′,48*60,’Running’);
insert into Minute_Count values (‘2000-01-07T18:00:00′,60*60,’Stopped’);
/* any Monday-Friday week */

declare @week int;
set @week = 1;
while @week < 2100 begin
  insert into Minute_Count
    select
      dateadd(week,@week,d),
      elapsed_minutes + 60*@week*60,
      timer
  from Minute_Count
  set @week = @week * 2
end;

go

create function DeskTimeDiff_minutes(
  @from datetime,
  @to datetime
) returns int as begin
  declare @fromSerial int;
  declare @toSerial int;
  with S(d,elapsed_minutes,timer) as (
    select top 1 d,elapsed_minutes, timer
    from Minute_Count
    where d <= @from
    order by d desc
  )
    select @fromSerial =
      elapsed_minutes +
      case when timer = ‘Running’
      then datediff(minute,d,@from)
      else 0 end
    from S;
  with S(d,elapsed_minutes,timer) as (
    select top 1 d,elapsed_minutes, timer
    from Minute_Count
    where d <= @to
    order by d desc
  )
    select @toSerial =
      elapsed_minutes +
      case when timer = ‘Running’
      then datediff(minute,d,@to)
      else 0 end
    from S;
  return @toSerial – @fromSerial;
end;
go
select MAX(d) from Minute_Count
select dbo.DeskTimeDiff_minutes(‘2007-12-19T18:00:00′,’2007-12-24T17:51:00’);
go

drop function DeskTimeDiff_minutes;
drop table Minute_Count;