4

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.

Improve this question
4
  • Check the indexes of the underlying tables, and also if there are any blocking issues. If feel that you should not have to take that long for only 20,000 rows.
    – StanleyJohns
    Jul 1, 2011 at 18:07
  • 2
    Have you thought about a static calendar table, instead of creating one every time you call the function?
    – Aaron Bertrand
    Jul 1, 2011 at 23:39
  • Hi, yes - it takes under a second to create the table. I just don't see how to make a use of that table with joins.
    – vikp
    Jul 4, 2011 at 7:42
  • 1
    You can join your table valued function using the CROSS APPLY join type.
    – Matt M
    Jul 8, 2011 at 18:31

2 Answers 2

Reset to default
3

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
Improve this answer
2
  • Hi, I have never came across the date dimension object. Will look it up and reply later. Thank you!
    – vikp
    Jul 4, 2011 at 7:43
  • Could you please post an example of dimension date or time object?
    – vikp
    Jul 4, 2011 at 10:45
0

This seems pretty simple on the pseudo scale:

Determine number of business days in the date range: D 

Determine number of Holidays in range H

return (D-H)*10*60
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.