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.
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
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
EDIT: In MSSQL you can use DATEDIFF(mi, start, end) to get the number of minutes for each subtraction above.