Business Hour Calculations
-
All,
Has anyone ever tried to compute business hours between two given date/times? I've found a lot of great information for business DAYS by using the dw function of datepart but need to get to the business hour level.
Any ideas?
thanks, jb
-
I have never seen a way of setting the time in sql server. If anyone has a way I would love to see it. I would approach the problem by writting some logic to do the work for you.
Here is a script I have used before that works in vb. You should be able to hack it to get it work in sql server.
Apologies if it is code someone has written that attends this site. I admit it is not mine.
---------------------------------------------------------------------------Function SLA(StartStamp As Date, EndStamp As Date, Optional BizStart As Date, Optional BizEnd As Date)
'
' I had a need to track the hours between when problem ticket was open and when
' it was resolved to meet Service Level Agreement (SLA) commitments. However,
' I only needed to know how many Business hours the ticket was open. Our Biz
' hours are Monday - Friday, 8:00am to 5:00pm. This will take two time/date
' entries and calculate the number of hours between 8:00 and 5:00 (unless
' otherwise specified). It will also exclude Sat or Sun hours. (I'm also
' working on excluding holidays, but that's a future version.
'
' This was created in Excel 97, but should be portable to later versions.
'
' Usage:
' SLA(Start Date,End Date,<optional> Business hour open, <optional> business hour close)
' where all variables are Excel Serial Number dates/times.
' The optional BizStart and BizEnd should be supplied if they are not
' 8:00 am and 5:00 pm.
' If optional hours are supplied, BOTH should be defined or an error will be returned
' If the End Date is BEFORE the Start Date, an error message will be returned
' If the BizEnd is BEFORE the BizStart, an error message will be returned
'
'
' Function Created 2/6/03 by W. Patrick Day, MCSE
' Comments, questions, critiques, and suggestions should be sent to
' Apexian@nc.rr.com or <A href="mailto
atrick.Day.B@bayer.com">Patrick.Day.B@bayer.com'
' If you make any improvements, please let me know, I've not a programmer by trade
' so I would definitely like to know how to make it better.
'
'*************************************************
Dim DaysBetween, DayCount, WeekEndCount As Integer
Dim FinalHours, StartHour, EndHour As Single
Error_Checking:
If StartStamp > EndStamp Then 'looks reversed timestamps
SLA = "End before Start"
Exit Function
End If
If StartStamp * EndStamp = 0 Then 'looks missing timestamps
SLA = "Missing Parameter"
Exit Function
End If
If BizStart > BizEnd Then 'looks for reversed biz hours
SLA = "End before Start"
Exit Function
End If
If BizStart = 0 And BizEnd = 0 Then 'assigns default hours if not passed
BizStart = 0.333333333333333 '8:00 AM
BizEnd = 0.708333333333333 '5:00 PM
GoTo Date_Fixing
End If
If BizStart * BizEnd = 0 Then 'looks for partial Biz hrs
SLA = "Missing Parameter"
Exit Function
End If
If BizStart > 1 Or BizEnd > 1 Then 'looks for erronious times
SLA = "Bad Hours"
Exit Function
End If
'************************
Date_Fixing:
'Start Day on Weekend fix (Move to Friday)
Select Case WeekDay(Int(StartStamp), vbMonday)
Case 6
StartStamp = (Int(StartStamp) - 1) + BizEnd
Case 7
StartStamp = (Int(StartStamp) - 2) + BizEnd
Case Else
StartStamp = StartStamp + 0
End Select
'End Day on Weekend fix (Move to Monday)
Select Case WeekDay(Int(EndStamp), vbMonday)
Case 6
EndStamp = (Int(EndStamp) + 2) + BizStart
Case 7
EndStamp = (Int(EndStamp) + 1) + BizStart
Case Else
EndStamp = EndStamp + 0
End Select
'*************************
Hour_Fixing:
'starting hour Adjustment
StartHour = StartStamp - Int(StartStamp) 'grabs the hour portion of the date
If StartHour < BizStart Or StartHour > BizEnd Then
If StartHour < BizStart Then 'if before start time, convert to start hour
StartStamp = Int(StartStamp) + BizStart
Else 'otherwise, convert to end hour
StartStamp = Int(StartStamp) + BizEnd
End If
End If
'ending hour Adjustment
EndHour = EndStamp - Int(EndStamp) 'grabs the hour portion of the date
If EndHour < BizStart Or EndHour > BizEnd Then
If EndHour < BizStart Then 'if before start time, convert to start hour
EndStamp = Int(EndStamp) + BizStart
Else
EndStamp = Int(EndStamp) + BizEnd
End If
End If
'***************
Day_Counting:
'counts the number of days between, not including start and finish days
DayCount = 0
WeekEndCount = 0
DaysBetween = Int(EndStamp) - Int(StartStamp)
Select Case DaysBetween
Case 0
FinalHours = ((EndStamp - Int(EndStamp)) - (StartStamp - Int(StartStamp)))
Case 1
FinalHours = (BizEnd - (StartStamp - Int(StartStamp))) + ((EndStamp - Int(EndStamp)) - BizStart)
Case Else
'this counts the whole days between and subtracts the weekends
For x = (Int(StartStamp) + 1) To (Int(EndStamp) - 1) Step 1
If WeekDay(x, vbMonday) > 5 Then
WeekEndCount = WeekEndCount + 1
End If
Next x
DaysBetween = DaysBetween - WeekEndCount
FinalHours = (BizEnd - (StartStamp - Int(StartStamp))) + ((EndStamp - Int(EndStamp)) - BizStart) + ((DaysBetween - 1) * (BizEnd - BizStart))
End Select
'******************
Done:
'puts final result into decimal format
SLA = Format(FinalHours, "#,###.0000000") * 24
End Function
-----------------------------------------------------------------
------------------------------
The Users are always right - when I'm not wrong! -
Just remember to remove holidays and weekends wouldbe the only thing extra.
-
I have Excel code for determining elapsed business hours. It recognizes weekends (Sat/Sun) but does not have a provision for holidays.
It's part of a larger application, so send email if you want it and I'll send the .xls file.
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply