How do I calculate working business minutes?

I'm working on business inteligence reports and one of my task is to create reports for time tracking.

I have to report on business minutes (10 hours per day) within business week (monday to friday).

My initial solution was to use a scalar function. It did not cater for holidays, but it worked. It was not optimized by database engine, therefore, when I have tried using my function in the following way:

Select 
   [dbo].[fn_GetWorkMinutesInRange](CreatedOn,ResolvedOn,1, 6, 7)
From
   Incidents

This was not optimised database engine and it was taking over 5 minutes to execute before I canceled it. There are around 20,000 incidents in the Incidents table, so performance should not suffer imo.

I have given up on scalar function solution and I created a table function which created a calendar. This didn't help either as I don't see how I can join this function with my Incidents table.

If your reports are based on a data warehouse that is dimensionally modelled (generally the case for business intelligence solutions), it is highly probable there is a date dimension and time dimension.

If there are no such objects, it may be very useful to create them as they become highly re-usable as other reports are required.

In the date dimension, there should be an IsHolidayFlag column. In the time dimension, there should be an IsBusinessMinuteFlag column (depending on the grain of this dimension, it may be IsBusinessSecondFlag).

The query would then be relatively straight forward and quick, provided the indexes are correct. There is an overhead with the CROSS JOIN (which could be created as a view for re-usability), but for 200,000 rows, it should be negligible.

SELECT  i.IncidentID,
       'WorkMinutesInRange' = SUM(CASE WHEN d.IsHolidayFlag = 'N' AND t.IsBusinessMinuteFlag = 'Y' THEN 1 ELSE 0 END)
FROM    dbo.Incidents i
        INNER JOIN Dim_Date d
            ON d.CalendarDate BETWEEN i.CreatedOn AND i.ResolvedOn
        CROSS JOIN Dim_Time t
GROUP BY i.IncidentID

StackExchange Database Administrators Q#3608, answer score: 3