[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 965
  • Last Modified:

Calculate elapsed business hours - SQL 2008

Our business is open 9-5, Monday to Friday.  I am using the SQL function in the attached text file to calculate the elapsed business hours between two datetimes.  The variables are as follows:
@startdate - the start datetime of the period (datetime)
@enddate - the end date of the period (datetime)
@starttime - the start of the business day (vchar(9))
@endtime - the end of the business day (vchar(9))

The function is called with a SQL statement that passes the values of these variables to the function.

Does anyone have any idea how I would go about modifying this function to exclude Saturdays and Sundays from the calculation?

Thanks!
business-hours.txt
0
I_play_with_DNA
Asked:
I_play_with_DNA
  • 3
3 Solutions
 
OriNetworksCommented:
FIrst I would ask why the times are separated from the dates.

Create a new combined date/time
e.g.
@startdate = '1/1/2010'
@enddate = ' 1/2/2010'
@starttime = '9:00 am'
@endtime = '12:30 pm'

@newstartdate = '1/1/2010 9:00:00 am'
@newenddate = '1/2/2010 12:30:00 pm'

select datediff(minute,@newstartdate,@newenddate)/60 = the amount of hours spent
0
 
OriNetworksCommented:
sorry i shouldnt have thought it was that easy.

Add to that

select @minutesUsed=datediff(minute,@newstartdate,@newenddate)

Select @minutesUsed=(@minutesUsed-(case when @StartDate<>@EndDate then datediff(day,@startdate,@enddate)*900))

this will remove the after hours minutes between days. You can also do a similar function to check for weekends that have passed.
0
 
rmm2001Commented:
Put in a where DATEPART(weekday, @startdate) NOT IN (1, 7) (1 is sunday...7 is saturday)
0
 
CboudrozCommented:
add this in the where clause:

and DATEPART(dw,DateValue) not in (1,7)
0
 
OriNetworksCommented:
Ithe datelart cannot be and otherwise it will evaluate as false for start or end date on weekends and not return a result. You would have to add a case statement with the datepart to subtract those days but this is not good either because it would not calculate weekends in between so oh have to use it in conjunction with a datediff to find out how many weekends are between those dates.
,
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now