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

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 -------------------

Select allOpen in new window

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

Select allOpen in new window