Calculating the Number of Business Hours Passed Since a Point of Time

,

Calculating the number of hours passed since a point of time

This set of user defined functions is used to calculate the number of hours passed since a given date and time excluding the non-working hours . The set contains 8 functions. The function which is called to do the job is [CalcTimeSinceCreated]. This function takes a datetime type value as input. All the other 7 functions are used internally.

This code assumes the working schedule as follows:

  • 8 working hours per day from 7.30 AM to 4.00 PM
  • Lunch period from 11.30 AM to 12 Noon.
  • Saturday & Sunday as off days

The code can be modified to suit any other working hours and days pattern. I used this code to calculate the actual number of working hours passed since a job was started. It can be modified to calculate the number of working hours taken to complete tasks (The function CalcTimeSinceCreated will have to be modified to take two parameters in that case as 'task beginning date&time' and 'task ending date&time', instead of one at present).

Suppose the time right now is November 20, 2 PM and we want to calculate the number of working hours passed since November 16, 3:30 PM. The function will give the result 14.5 hours.

select dbo.CalcTimeSinceCreated('2007-11-16 15:30')





Result = 14.5

The calculation goes as follows:

Date Day Hours Explanation
November 16 Friday 0.5 hours The task started at 3.30 PM and the working hour is upto 4.00 PM
November 17 Saturday 0 hours Saturday is an off day
November 18 Sunday 0 hours Sunday is a off day
November 19 Monday 8 hours 8 full hours in the day
November 20 Tuesday 6 hours From 7.30 AM to 2 PM excuding half an hour for the lunch time
Total Hours: 14.5
-- Function1 [DateAtMidnight]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =================================================================================================================
-- Author:    <Mazharuddin Ehsan>
-- =================================================================================================================

CREATE function [DateAtMidnight](@DateTime DateTime)
-- Returns the DateTime minus the time portion of the input date; ie the DateTime at midnight
returns datetime
as
 begin 
 return dateadd(dd, datediff(dd,0,@DateTime), 0)
 end 
-- =================================================================================================================
-- Function 2 [DateAt730]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Mazharuddin Ehsan>
-- =============================================
CREATE function [DateAt730](@DateTime DateTime)
-- Returns DateTime at 07.30 A.M. for the input date

returns datetime
as
begin
 return dateadd(dd, datediff(dd,0,@DateTime),0)+dateadd(ss,(7*3600)+(30*60),0)
 end
-- =================================================================================================================
-- Function 3 [DateAt1130]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Mazharuddin Ehsan>
-- =============================================
CREATE function [DateAt1130](@DateTime DateTime)
-- Returns DateTime at 11.30 AM for the input date
returns datetime
as
begin
 return dateadd(dd,datediff(dd,0,@DateTime), 0)+dateadd(ss,(11*3600)+(30*60),0)
 end

-- =================================================================================================================
-- Function 4 [DateAt12]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: <Mazharuddin Ehsan>
-- =============================================
CREATE function [DateAt12](@DateTime DateTime)
-- Returns DateTime at 12.00 Noon for the input date
returns datetime
as
 begin
 return dateadd(dd, datediff(dd,0,@DateTime), 0)+dateadd(ss,(12*3600),0)
 end

-- =================================================================================================================
-- Function 5 [DateAt16]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: <Mazharuddin Ehsan>
-- =============================================
CREATE function [DateAt16](@DateTime DateTime)
-- Returns DateTime at 4.00 P.M. for the input date
returns datetime
as
 begin
 return dateadd(dd, datediff(dd,0,@DateTime),0)+dateadd(ss,(16*3600),0)
 end
-- =================================================================================================================
-- Function 6 [CalcCreateDate]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:<Mazharuddin Ehsan>
-- =============================================

CREATE function [CalcCreateDate](@DateTime DateTime)
-- Returns the beginning DateTime on a particular office day for calculation
returns datetime
as
begin
if DATEPART(dw, @DateTime) = 7 
 set @DateTime = dbo.DateAt730(@DateTime + 2)
 else
   begin
    if DATEPART(dw, @DateTime) = 1
      set @DateTime = dbo.DateAt730(@DateTime + 1)
   end
if @DateTime <= dbo.DateAt730(@DateTime)
   set @DateTime = dbo.DateAt730(@DateTime)
else
 begin 
  if @DateTime >= dbo.DateAt1130(@DateTime) and @DateTime <= dbo.DateAt12(@DateTime)
     set @DateTime = dbo.DateAt12(@DateTime)
  else 
   if @DateTime >= dbo.DateAt16(@DateTime) 
      set @DateTime = dbo.DateAt16(@DateTime)
 end
return @DateTime
end
-- =================================================================================================================
-- Function 7 [CalcGetDate]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Mazharuddin Ehsan>
-- =============================================

ALTER function [CalcGetDate]()
-- Returns the ending DateTime on a particular office day for calculations
returns datetime
as
begin
 declare @CalcGetDate datetime
 set @CalcGetDate = getdate()
    if DATEPART(dw, @CalcGetDate) = 7 
     set @CalcGetDate = dbo.DateAt730(@CalcGetDate + 2)
     else
 begin
         if DATEPART(dw, @CalcGetDate) = 1
             set @CalcGetDate = dbo.DateAt730(@CalcGetDate + 1)
 end
    if @CalcGetDate <= dbo.DateAt730(@CalcGetDate)
     set @CalcGetDate = dbo.DateAt730(@CalcGetDate)
    else
 begin
     if @CalcGetDate >= dbo.DateAt1130(@CalcGetDate) and @CalcGetDate <= dbo.DateAt12(@CalcGetDate)
         set @CalcGetDate = dbo.DateAt12(@CalcGetDate)
     else 
         if @CalcGetDate >= dbo.DateAt16(@CalcGetDate) 
             set @CalcGetDate = dbo.DateAt16(@CalcGetDate)
 end
 return @CalcGetDate
 end
-- =================================================================================================================
-- Function 8 [CalcTimeSinceCreated]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:  <Mazharuddin Ehsan>
-- =============================================

CREATE function [CalcTimeSinceCreated](@DateTime DateTime)
-- This is the function that will be called to do the job. All the previous are internally used
-- Returns Total hours passed since the input date. Calculates excluding the off hours (Saturday, Sunday & lunch period (11.30 AM to 12 Noon))
-- Loops through all the days from the input date to the current DateTime to calculate the hours.
returns real
as
begin
 declare @CalcHours real
 declare @VarCalcCreateDate datetime
 if dbo.DateAtMidnight(dbo.CalcCreateDate(@DateTime)) = dbo.DateAtMidnight(dbo.CalcGetDate())
    set @CalcHours = 
    case 
    when dbo.CalcCreateDate(@DateTime) <= dbo.DateAt1130(dbo.CalcGetDate()) and dbo.CalcGetDate() >= dbo.DateAt1130(dbo.CalcGetDate()) then
    round(((cast(datediff(ss, dbo.CalcCreateDate(@DateTime), dbo.CalcGetDate()) as real)-1800)/3600),2)
    else
    round((cast(datediff(ss, dbo.CalcCreateDate(@DateTime), dbo.CalcGetDate()) as real)/3600),2) 
    end
 else
  begin
    set @CalcHours = 
    case
    when
    dbo.CalcCreateDate(@DateTime) <= dbo.DateAt1130(dbo.CalcCreateDate(@DateTime)) then
    round(((cast(datediff(ss, dbo.CalcCreateDate(@DateTime), dbo.DateAt16(dbo.CalcCreateDate(@DateTime))) as real)-1800)/3600),2)
    else
    round((cast(datediff(ss, dbo.CalcCreateDate(@DateTime), dbo.DateAt16(dbo.CalcCreateDate(@DateTime))) as real)/3600),2)
    end 
    set @VarCalcCreateDate = dbo.CalcCreateDate(@DateTime)
    while (1 = 1)
        begin
             if DATEPART(dw, @VarCalcCreateDate) = 6
                 set @VarCalcCreateDate = dbo.DateAt730(@VarCalcCreateDate + 3)
             else
                 begin
                   if DATEPART(dw, @VarCalcCreateDate) = 1
                     set @VarCalcCreateDate = dbo.DateAt730(@VarCalcCreateDate + 2)
                   else
                     set @VarCalcCreateDate = dbo.DateAt730(@VarCalcCreateDate + 1)
                 end 
             if dbo.DateAtMidnight(@VarCalcCreateDate) = dbo.DateAtMidnight(dbo.CalcGetDate())
                 set @CalcHours = @CalcHours + (case
                 when 
                 @VarCalcCreateDate <= dbo.DateAt1130(dbo.CalcGetDate()) and dbo.CalcGetDate() >= dbo.DateAt1130(dbo.CalcGetDate()) then
                 round(((cast(datediff(ss, @VarCalcCreateDate, dbo.CalcGetDate()) as real)-1800)/3600),2)
                 else
                 round((cast(datediff(ss, @VarCalcCreateDate, dbo.CalcGetDate()) as real)/3600),2) 
                 end)
             if dbo.DateAtMidnight(@VarCalcCreateDate) = dbo.DateAtMidnight(dbo.CalcGetDate()) 
                 BREAK
             else
             set @CalcHours = @CalcHours + 8
        end
  end
 return @CalcHours
 end

Rate

3.5 (6)

Share

Share

Rate

3.5 (6)