[Webinar] Streamline your web hosting managementRegister Today

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

Calculating working hours time span

Does anyone have a function to calculate working hours time span between 9am and 5.30pm using the 12 hour clock

I want to calculate averagewaitingtimes using the format below.

 I want  the calculation to exclude bank holidays and weekends and should not be the 24 hour format
The working hours are between 9am  to 5.30pm
I want the calculation to stop at 5.30pm

e.g  
datepricequoterequested -24/10/2011 at 15.00
datepricequotetodistributor  – 24/10/2011 at 16.00
datepricequotefromdistributor – 24/10/ 2011 at 17:00
datepricequoteprocessed  -25/10/2011 at 10:00am
Stop counting from  17:30 and 09:00

e.g
15:00-16:00 1 hour at company
16:00-17:00 1 hour at distributor
17:00 – 17:30 ½ hour at company
09:00 -10:00 1 hour at company
Gives the company 2 ½ hrs


Format is:
datepricequoteprocessed -  (datepricequotetodistributor  - datepricequotefromdistributor)- datepricequoterequested

e.g
25/10/2011 at 10:00am-(24/10/2011 at 16.00-– 24/10/ 2011 at 17:00)- 25/10/2011 at 10:00am
0
turaks
Asked:
turaks
1 Solution
 
momi_sabagCommented:
can you post your table structure? (column names and types)
0
 
DcpKingCommented:
There's a blog post on a related subject at
http://pratchev.blogspot.com/2008/01/calculating-work-hours.html
and you may find something close in the business maths part at
http://www.totallysql.com/

If you do end up doing it yourself, I think you can best start by creating a dates table, that provides you with all the days that can be utilised (i.e. get weekends and holidays specified so you can skip them).

Then what you imply is that you provide the code with the quoteRequested and quoteProcessed datetimes and distributorOut and distributorIn datetimes, This effectively gives you two periods - from quoteRequested to distributorOut and from distributorIn to quoteProcessed. I think you can get the technique for these two from the blog and then add them ...

hth

Mike
0
 
Scott PletcherSenior DBACommented:
Weekends are easy to determine, but you would need a table with all the bank holidays in it.

The rest of the computation can be done in an expression to determine the total company time.

Presumably average would just be dividing that total company time by the total row count.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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