Calculate time difference (only working hours) in minutes between two dates

Question

I need to calculate the number of "active minutes" for an event within a database. The start-time is well known.

The complication is that these active minutes should only be counted during a working day - Monday-Friday 9am-6.30pm, excluding weekends and (known) list of holiday days

The start or "current" time may be outside working hours, but still only the working hours are counted.

This is SQL Server 2005, so T-SQL or a managed assembly could be used.

Answer 1

If you want to do it pure SQL here's one approach

CREATE TABLE working_hours (start DATETIME, end DATETIME);

Now populate the working hours table with countable periods, ~250 rows per year.

If you have an event(@event_start, @event_end) that will start off hours and end off hours then simple query

SELECT SUM(end-start) as duration
FROM working_hours
WHERE start >= @event_start AND end <= @event_end

will suffice.

If on the other hand the event starts and/or ends during working hours the query is more complicated

SELECT SUM(duration) 
FROM 
(
   SELECT SUM(end-start) as duration
   FROM working_hours
   WHERE start >= @event_start AND end <= @event_end
UNION ALL
   SELECT end-@event_start
   FROM working_hours
   WHERE @event_start between start AND end
UNION ALL
   SELECT @event_end - start
   FROM working_hours
   WHERE @event_end between start AND end
) AS u

Notes:

• the above is untested query, depending on your RDBMS you might need date/time functions for aggregating and subtracting datetime (and depending on the functions used the above query can work with any time precision).
• the query can be rewritten to not use the UNION ALL.
• the working_hours table can be used for other things in the system and allows maximum flexibility

EDIT: In MSSQL you can use DATEDIFF(mi, start, end) to get the number of minutes for each subtraction above.

Answer 2

Using unreason's excellent starting point, here is a TSQL implementation for SQL Server 2012.

This first SQL populates a table with our work days and times excluding weekends and holidays:

declare @dteStart date
declare @dteEnd date
declare @dtStart smalldatetime
declare @dtEnd smalldatetime
Select @dteStart = '2016-01-01'
Select @dteEnd = '2016-12-31'

CREATE TABLE working_hours (starttime SMALLDATETIME, endtime SMALLDATETIME);

while @dteStart <= @dteEnd
BEGIN
   IF    datename(WEEKDAY, @dteStart) <> 'Saturday' 
     AND DATENAME(WEEKDAY, @dteStart) <> 'Sunday'
     AND @dteStart not in ('2016-01-01' --New Years
                          ,'2016-01-18' --MLK Jr
                          ,'2016-02-15' --President's Day
                          ,'2016-05-30' --Memorial Day
                          ,'2016-07-04' --Fourth of July
                          ,'2016-09-05' --Labor Day
                          ,'2016-11-11' --Veteran's Day
                          ,'2016-11-24' --Thanksgiving
                          ,'2016-11-25' --Day after Thanksgiving
                          ,'2016-12-26' --Christmas
                          )
      BEGIN
        select @dtStart = SMALLDATETIMEFROMPARTS(year(@dteStart),month(@dteStart),day(@dteStart),8,0) --8:00am
        select @dtEnd   = SMALLDATETIMEFROMPARTS(year(@dteStart),month(@dteStart),day(@dteStart),17,0) --5:00pm
        insert into working_hours values (@dtStart,@dtEnd)
      END
   Select @dteStart = DATEADD(day,1,@dteStart)
END

Now here is the logic that worked to return the minutes as an INT:

declare @event_start datetime2
declare @event_end datetime2
select @event_start = '2016-01-04 8:00'
select @event_end = '2016-01-06 16:59'

SELECT SUM(duration) as minutes
FROM 
(
   SELECT DATEDIFF(mi,@event_start,@event_end) as duration
   FROM working_hours
   WHERE @event_start >= starttime 
     AND @event_start <= endtime
     AND @event_end <= endtime
UNION ALL
   SELECT DATEDIFF(mi,@event_start,endtime)
   FROM working_hours
   WHERE @event_start >= starttime 
     AND @event_start <= endtime
     AND @event_end > endtime
UNION ALL
   SELECT DATEDIFF(mi,starttime,@event_end)
   FROM working_hours
   WHERE @event_end >= starttime 
     AND @event_end <= endtime
     AND @event_start < starttime
UNION ALL
   SELECT SUM(DATEDIFF(mi,starttime,endtime))
   FROM working_hours
   WHERE starttime > @event_start
     AND endtime < @event_end
) AS u

This correctly returns 1 minute shy of three 9 hour work days