business hours

[sedgely]

i need to be able to calculate the time diiference in hours(datediff?) between 2 datetime fields which is no problem...but here's the difficulty. i need to only count working hours i.e mon-fri 09:00 - 17:00
is this possible and if so how?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[misscrf]

I'm not sure if this will help, but you could probably do this 2 ways. One would be to add AM and PM and then involve a case select, which would evaluate if it is within that time frame. The other would be to make the time military, so that it goes to 24:00. Then you could make it validate from 09:00 - 19:00. Does that help?
Hope so!


misscrf

Management is doing things right, leadership is doing the right things

 

[rac2]

Do you need to calculate for example the number of working hours from 4:47pm Wednesday Oct 6, 2004 to 9:00am Monday Oct 11, 2005?

Boy that sounds like a good function to create once and add to everbody's system!

One approach is to break the problem into pieces.
A. Start and end times are within the working hours on the same date, DATEDIFF(minutes, StartTime, EndTime).

B. Start time is during working hours, end time is before the next work day starts, DATEDIFF(minutes, StartTime, QuittingTime)

C. Start time is before ...

D. Times cross whole days, NumberDays x 8 plus residuals from above conditions on the start and end times.

You will need a way to identify work days such as DATEPART(dayofweek, StartTime).

Tackle each condition one by one and work out an expression for the calculation and the condtions. Write the expression and use it to define a calulated column.

Define calculated columns for all of the conditions, there might be half-dozen or so. Make the value zero if the condition does not apply. Then a final column to add them up.

It seems like a lot of calculation, I know. It is a lot for us, it is nothing for a computer.

Once you get the solution, patent it, or at least post it here for the rest of us.

 

[r937]

or use a table, it makes for really simple sql

see Difference between two dates using office hours
(site registration may be required, but it's free)

rudy

http://r937.com/

SQL Consulting

 

[sedgely]

Thanks all for your answers, i will work on it today nad if i come up with anything will post back.

Cheers, Craig
Si fractum non sit, noli id reficere

 

[misscrf]

Hey Craig, what does "Si fractum non sit, noli id reficere" mean?

misscrf

Management is doing things right, leadership is doing the right things

 

[MichaelRed]

see faq181-261 for the working days difference, SOMEWHERE within these threads is at least one which, using the faq procedure, completes the exercise.





MichaelRed

 

[sedgely]

Michael
Thanks will keep searching the forums.

BTW Si fractum non sit, noli id reficere = if it ain't brok, don't fix it

Cheers, Craig
Si fractum non sit, noli id reficere