We help IT Professionals succeed at work.

User Defined Function to Calc Business Hrs Between 2 Date/Times (SQL)

560 Views
Last Modified: 2012-06-27
Hi All --
Please bear with me as I was given this task and have NEVER done this before.
I need to create a User Defined Function to calculate the business hours between 2 dates and times.

I will need to pass 5 parameters.

The parameters are:

1) Company (I have different companies with different business hours)

2) Business Hours Start (This is the time the company considers the start of it's business day. (i.e. 7:00 AM))

3) Business Hours End (This is the time the company considers the end of it's business day.(i.e. 10:00 PM, 22:00))

4) Incident Start (This would be the start of the problem being tracked. (i.e. 11/6/2009 9:00:00)

5) Incident End (This would be the end of the problem being tracked. (i.e. 11/10/2009 20:00:00)

Weekend and holidays need to be excluded. I do have a table set up with company holidays.

After much searching I did find this question that seem very close to what I am trying to accomplish, however it uses stored procedure plus a user defined function and I'm not sure how to turn it into what I actually need.

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21924182.html?sfQueryTermInfo=1+hour+sql+work

Thanks in advance for any help you can provide. Details would be appreciated.
Comment
Watch Question

could you provide some more information such as table structures for the tables that contain your company and hours information.

Author

Commented:
I hope this is what you mean:

COMPANY TABLE
Company  (varchar)    BH Start   (date/time)              BH End (date/time)
ABC Company             7:00:00 AM                            10:00:00 PM
 XYZ Company            6:00:00 AM                            05:00:00 PM

HOLIDAY TABLE
Holiday (varchar)      Date (date/time)
New Years Day        1/6/2009




So what if, by chance, a ticket is opened or closed on a holiday?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Tickets can be opened at any time.

So if a ticket is opened on a holiday or weekend, the start of the ticket would be the next business day.

For instance if a ticket is opened on Saturday the clock wouldn't start ticking on it until Monday at 8:00 AM if that is the businss start time.
-----------------------------------
My COMPANY table is joined to the INCIDENT table on Company Name. The company table is just a lookup table that holds the company business hours among other things.

The holiday table is not joined. I'm thinking from looking at other solutions that it's just referenced to find the dates to exclude.
------------------------------------------
I know this is complex. I'm trying to get the code attached to work. I copied it from the link below.

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21924182.html?sfQueryTermInfo=1+hour+sql+work
if exists (select * from sysobjects where name = N'CalcWorkingHours' and type = N'FN')
    drop function CalcWorkingHours
go
 
CREATE FUNCTION CalcWorkingHours (@dtStartDateTime AS DATETIME, 
                                  @dtEndDateTime AS DATETIME,
                                  @dtWorkFrom AS DATETIME,
                                  @dtWorkTo AS DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
 
    DECLARE @intWorkingMinutes        INT,
            @intFullWorkDayMinutes    INT,
            @intHours                 INT,
            @intMinutes               INT,
            @dtFirstDayEnd            DATETIME,
            @dtLastDayStart           DATETIME,
            @chrWorkingHours          VARCHAR(10),
            @chrHours                 VARCHAR(7),
            @chrMinutes               VARCHAR(2)
 
    SELECT  @intWorkingMinutes = 0
    SELECT  @intFullWorkDayMinutes = DATEDIFF(mi, @dtWorkFrom, @dtWorkTo)
 
    -- Is the start date & time < end date & time
    IF DATEDIFF(dd, @dtStartDateTime, @dtEndDateTime) >= 0
    BEGIN
 
        -- If start and end time are the same assume 24 hour workig day
        IF @intFullWorkDayMinutes = 0 
        BEGIN
            SELECT @intWorkingMinutes = DATEDIFF(mi, @dtStartDateTime, @dtEndDateTime)
        END
        ELSE
        BEGIN
 
            -- Is the start time < working hours start
            IF DATEPART(hh, @dtStartDateTime) < DATEPART(hh, @dtWorkFrom)
            BEGIN
                SELECT @dtStartDateTime = LEFT(@dtStartDateTime, 12) + RIGHT(@dtWorkFrom, 6)
            END
            ELSE
            BEGIN
 
                -- Is the start time > working hours end
                IF DATEPART(hh, @dtStartDateTime) >= DATEPART(hh, @dtWorkTo)
                BEGIN
                    SELECT @dtStartDateTime = LEFT(@dtStartDateTime, 12) + RIGHT(@dtWorkTo, 6)
                END
            END
 
            -- Is the end time < working hours start
            IF DATEPART(hh, @dtEndDateTime) < DATEPART(hh, @dtWorkFrom)
            BEGIN
                SELECT @dtEndDateTime = LEFT(@dtEndDateTime, 12) + RIGHT(@dtWorkFrom, 6)
            END
            ELSE
            BEGIN
 
                -- Is the end time > working hours end
                IF DATEPART(hh, @dtEndDateTime) >= DATEPART(hh, @dtWorkTo)
                BEGIN
                    SELECT @dtEndDateTime = LEFT(@dtEndDateTime, 12) + RIGHT(@dtWorkTo, 6)
                END
            END
 
            -- Are start date and end date the same
            IF DATEDIFF(dd, @dtStartDateTime, @dtEndDateTime) = 0
            BEGIN
 
                -- Is this a working day?
                IF dbo.WorkingDay(@dtStartDateTime) = 1
                BEGIN
                    SELECT @intWorkingMinutes = DATEDIFF(mi, @dtStartDateTime, @dtEndDateTime)
                END
                ELSE
                BEGIN
                    SELECT @intWorkingMinutes = 0
                END
            END
            ELSE
            BEGIN
 
                -- Calculate the number of minutes on the first day
                SELECT @dtFirstDayEnd = LEFT(@dtStartDateTime, 12) + RIGHT(@dtWorkTo, 6)
                -- Is this a working day?
                IF dbo.WorkingDay(@dtStartDateTime) = 1
                BEGIN
                    SELECT @intWorkingMinutes = DATEDIFF(mi, @dtStartDateTime, @dtFirstDayEnd)
                END
                SELECT @dtStartDateTime = DATEADD(dd, 1, @dtStartDateTime)
 
                -- Calculate the number of minutes on the last day
                SELECT @dtLastDayStart = LEFT(@dtEndDateTime, 12) + RIGHT(@dtWorkFrom, 6)
                -- Is this a working day?
                IF dbo.WorkingDay(@dtLastDayStart) = 1
                BEGIN
                    SELECT @intWorkingMinutes = @intWorkingMinutes + DATEDIFF(mi, @dtLastDayStart, @dtEndDateTime)
                END
 
                -- Calculate the number of working days between the two dates
                WHILE CONVERT(DATETIME, LEFT(@dtStartDateTime, 12)) < CONVERT(DATETIME, LEFT(@dtEndDateTime, 12))
                BEGIN
 
                    -- Is this a working day?
                    IF dbo.WorkingDay(@dtStartDateTime) = 1
                    BEGIN
                        SELECT @intWorkingMinutes = @intWorkingMinutes + @intFullWorkDayMinutes
                    END
                    SELECT @dtStartDateTime = DATEADD(dd, 1, @dtStartDateTime)
                END
            END
        END
    END
    ELSE
    BEGIN
        SELECT @intWorkingMinutes = 0
    END
 
    -- Calculate hours and minutes
    SELECT @intHours = @intWorkingMinutes / 60
    SELECT @chrHours = CAST(@intHours AS VARCHAR(7))
    IF LEN(@chrHours) < 2
    BEGIN
        SELECT @chrHours = REPLICATE('0', 2 - LEN(@chrHours)) + @chrHours
    END
    SELECT @intMinutes = @intWorkingMinutes - (@intHours * 60)
    SELECT @chrMinutes = CAST(@intMinutes AS VARCHAR(2))
    SELECT @chrMinutes = REPLICATE('0', 2 - LEN(@chrMinutes)) + @chrMinutes
    SELECT @chrWorkingHours = @chrHours + ':' + @chrMinutes
 
    return @chrWorkingHours
END
GO
 
GRANT EXECUTE on CalcWorkingHours TO PUBLIC
GO
 
--------------------- End of stored procedure --------------------
 
--------------------- Function start -------------------
 
if exists (select * from sysobjects where name = N'WorkingDay' and type = N'FN')
    drop function WorkingDay
go
 
CREATE FUNCTION WorkingDay (@dtDate AS DATETIME)
RETURNS BIT
AS
BEGIN
 
    DECLARE @bWorkingDay BIT
    SELECT  @bWorkingDay = 1
 
    -- Check for a weekend
    IF DATEPART(dw, @dtDate) IN (1, 7)
    BEGIN
        SELECT @bWorkingDay = 0
    END
    ELSE
    BEGIN
 
        -- Check for a public holiday
        IF EXISTS (SELECT    *
                    FROM      NonWorkingDays
                    WHERE     dtNonWorkingDay = LEFT(@dtDate, 11)    AND
                              chCountryCode = 'GB'                   AND
                              tiRecordStatus = 1)
        BEGIN
            SELECT @bWorkingDay = 0
        END
    END
 
    RETURN @bWorkingDay
END
GO
 
GRANT EXECUTE on WorkingDay TO PUBLIC
GO
 
 
----------------- End of function -------------------

Open in new window

Try removing the correlation between holiday and the company then and see how close my query is.
CERTIFIED EXPERT

Commented:
I was intrigued by your question, didn't like the long-windedness of the solution you were trying to adopt, and have spent some time looking for a more elegant approach.

The attached function is not complete - it has hard coded values for the company's working hours, but I think it's readable enough to show the methodology.

Essentially it creates an in-memory table populated in a single SQL statement with one row for each day in the range containing the date and the standard daily hours. It then adjusts the first and last days as appropriate.  

It uses some helper functions which I'll post separately

Let me know if you're interested and I'll complete the testing

mmm, I'm getting an error on this page when I try to attach the code - it may have to come in a follow-up post.
CERTIFIED EXPERT

Commented:
It attached this time.

ALTER FUNCTION [dbo].[WorkingHours] 
(
	-- Add the parameters for the function here
	@Company integer,
	@IncidentStart smalldatetime,
	@IncidentEnd smalldatetime
)
RETURNS Decimal(10,2)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result Decimal(10,2)
	
	-- Test Data Company Hours
		Declare @Start Decimal(4,2), @End Decimal(4,2)	-- The start/end or the working day in decimal hours
		Select @Start = 8.5, @end = 16.5		-- Eight-thirty to Four-thirty
			
	-- Align IncidentStart/End to Working Hours
	Declare @IncidentStartTime 	Decimal(4,2), @IncidentEndTime 	Decimal(4,2)	
	Select @IncidentStartTime = dbo.fnTimeInHours(@IncidentStart),
			@IncidentEndTime = dbo.fnTimeInHours(@IncidentEnd) 
	if 	not @IncidentStartTime Between @Start and @End
		begin
		if @IncidentStartTime >= @End
			-- Effectively starts the next day
			Select @IncidentStart = DATEADD(d,1,@IncidentStart)
		-- move to start of day 	
		select  @IncidentStartTime =  @Start 
		End
	if 	not @IncidentEndTime Between @Start and @End
		begin
		if @IncidentEndTime >= @Start
			-- Effectively ends the pervious day
			Select @IncidentEnd = DATEADD(d,-1,@IncidentEnd)
		-- move to end of day 	
		select  @IncidentEndTime =  @End 
		End
 
	If @IncidentEnd <= @IncidentStart
		Return 0;
 
	-- Remove the time from the start/end dates
	Select @IncidentStart = dbo.fnDateOnly(@IncidentStart),
			@IncidentEnd = dbo.fnDateOnly(@IncidentEnd)
	
	Declare @Days as Table(Day Smalldatetime, Hours Decimal(4,2)) 
	Insert @Days 
		Select DateAdd(d, Nums.N - 1, @incidentStart), @End-@Start
		From  dbo.fn_Nums(datediff(d,@IncidentStart,@IncidentEnd) + 1) as Nums
		Left Join Holidays H on DateAdd(d, Nums.N - 1, @incidentStart) = H.HDate
		Where HDate is null	and DatePart(dw,DateAdd(d, Nums.N - 1, @incidentStart)) between 2 and 6	
    --Select @IncidentStart, @IncidentEnd, @IncidentStartTime, @IncidentEndTime
	if @IncidentEnd = @IncidentStart
		update @Days set Hours = @IncidentEndTime -  @IncidentStartTime Where Day = @IncidentStart 
	else
		begin 
		update @Days set Hours = @End - @IncidentStartTime  Where Day = @IncidentStart
		update @Days set Hours = @IncidentEndTime -  @Start  Where Day = @IncidentEnd
		End
	Select @Result = sum(Hours) from @Days
	Return @Result	
	
	
END

Open in new window

CERTIFIED EXPERT

Commented:
Already noticed a bug:

            if @IncidentEndTime >= @Start
should be
            if @IncidentEndTime <= @Start
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
AngelIII,
As far as I'm aware the solution I provided is viable, though not complete.
Mike
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.