Link to home
Start Free TrialLog in
Avatar of I_play_with_DNA
I_play_with_DNA

asked on

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
Avatar of OriNetworks
OriNetworks

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
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.
ASKER CERTIFIED SOLUTION
Avatar of rmm2001
rmm2001
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial