Technical Article

Difference between two dates in Week Day Hours

,

This function will calcuate the number hours between two dates excluding weekends. This allows for cycle time metrics and anything else requiring only the hours of weekdays.

CREATE FUNCTION dbo.fnHourDiffExcludingWeekEnds
(@StartDate datetime,
@EndDate datetime)
RETURNS int
AS  
BEGIN 
declare @ActualDateDiff int
declare @NewStartDate datetime
declare @Difference int
declare @TempDifference int
declare @ActualHourDiff int

set @ActualDateDiff = datediff(dd, @StartDate, @EndDate)
set @ActualHourDiff = datediff(hh, @StartDate, @EndDate)

select @Difference =  
case @ActualDateDiff
when 0 then @ActualHourDiff --Same Day (just calculate days)
when 1 then 
case when datepart(dw,@Startdate) in (1,7) or datepart(dw,@EndDate) in (1,7) then @ActualHourDiff - 24 else @ActualHourDiff end
when 2 then--Could be 1 weekend day
case when datepart(dw,@Startdate) in (1,7) or datepart(dw,@EndDate) in (1,7) then @ActualHourDiff - 24 else @ActualHourDiff end
when 7 then @ActualHourDiff - 48 --Always Two weekend days 
else
case when @ActualDateDiff < 7 then --Less than a week
case @ActualDateDiff + datepart(dw,@Startdate) - 1
when 8 then @ActualHourDiff - 48
when 7 then @ActualHourDiff - 24
else 
@ActualHourDiff
end
else -- More than a week (always 5 work days)
5*24
end
end

if @ActualDateDiff > 7
begin
set @NewStartDate = dateadd(dd,7,@StartDate)
set @Difference = isnull(@Difference,0) + dbo.fnHourDiffExcludingWeekEnds(@NewStartDate,@EndDate)
end


return @Difference
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating