Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 '11 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 '11 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 '11 at 7:42
1  
You can join your table valued function using the CROSS APPLY join type. –  Matt M Jul 8 '11 at 18:31

2 Answers 2

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

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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