DateDiff -- Business Hours

[Tom B]

I'm writing a little Help Desk, and would like to show the amount of time
that a problem takes to be solved.

If I use DateDiff(hh,.......) then I would get a value of 16 for a problem
that began at 5pm and was resolved at 9 am the following morning. That
would look unacceptable to the manager, but is in fact quite acceptable.

I'm using....

SELECT DATEDIFF(hh, Problems.DateTimeStamp, StepsTaken.DateTimeStamp) FROM
etc.etc.

I'm sure this has come up before, and am curious what people do.

Thanks

TOm B

[William Tasso]

Tom B wrote:

> I'm writing a little Help Desk, and would like to show the amount of
> time that a problem takes to be solved.
>
> If I use DateDiff(hh,.......) then I would get a value of 16 for a
> problem that began at 5pm and was resolved at 9 am the following
> morning. That would look unacceptable to the manager, but is in fact
> quite acceptable.
>
> I'm using....
>
> SELECT DATEDIFF(hh, Problems.DateTimeStamp,
> StepsTaken.DateTimeStamp) FROM etc.etc.
>
> I'm sure this has come up before, and am curious what people do.

I fear the answer lies in the domain of whoever is using the reported
figures and would depend on purpose.

I suspect you should refrain from making any comment along the lines of
"give a better service if you want better figures".

Whatever you do, don't change the data - you'll feel more dirty than a whore
on her first day - but by all means accept briefs to provide reports that
show the figures in a more favourable light, perhaps you could build in
time-of-day sensitive adjustments.

--
William Tasso - [url]http://WilliamTasso.com[/url]

[Mark Schupp]

Do you want elapsed time or working time?

For elapsed time you would use datediff as you show.

For working time you would have the person doing the work enter the time
they spent on the problem. It is quite possible that a problem will consume
an hour here and there over a period of several days (weeks, months, ...)
and the total would be the sum of all the individual time periods.

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Tom B" <shuckle@hotmail.com> wrote in message
news:ee4sZizkDHA.2488@TK2MSFTNGP12.phx.gbl...

> I'm writing a little Help Desk, and would like to show the amount of time
> that a problem takes to be solved.
>
> If I use DateDiff(hh,.......) then I would get a value of 16 for a problem
> that began at 5pm and was resolved at 9 am the following morning. That
> would look unacceptable to the manager, but is in fact quite acceptable.
>
> I'm using....
>
> SELECT DATEDIFF(hh, Problems.DateTimeStamp, StepsTaken.DateTimeStamp)

FROM

> etc.etc.
>
> I'm sure this has come up before, and am curious what people do.
>
> Thanks
>
> TOm B
>
>

[Tom B]

Yes, I appreciate what you are saying.

I want a manager to be able to answer the question, "How long (on average)
did the end user have to wait (and theoretically, was unable to work) "

So, I'm really after--the number of business hours lost.

Thanks


"Mark Schupp" <mschupp@ielearning.com> wrote in message
news:%23lqOTz1kDHA.744@tk2msftngp13.phx.gbl...

> Do you want elapsed time or working time?
>
> For elapsed time you would use datediff as you show.
>
> For working time you would have the person doing the work enter the time
> they spent on the problem. It is quite possible that a problem will

consume

> an hour here and there over a period of several days (weeks, months, ...)
> and the total would be the sum of all the individual time periods.
>
> --
> Mark Schupp
> Head of Development
> Integrity eLearning
> [url]www.ielearning.com[/url]
>
>
> "Tom B" <shuckle@hotmail.com> wrote in message
> news:ee4sZizkDHA.2488@TK2MSFTNGP12.phx.gbl...

> > I'm writing a little Help Desk, and would like to show the amount of

time

> > that a problem takes to be solved.
> >
> > If I use DateDiff(hh,.......) then I would get a value of 16 for a

problem

> > that began at 5pm and was resolved at 9 am the following morning. That
> > would look unacceptable to the manager, but is in fact quite acceptable.
> >
> > I'm using....
> >
> > SELECT DATEDIFF(hh, Problems.DateTimeStamp, StepsTaken.DateTimeStamp)

> FROM

> > etc.etc.
> >
> > I'm sure this has come up before, and am curious what people do.
> >
> > Thanks
> >
> > TOm B
> >
> >

>
>

[John Blessing]

"Tom B" <shuckle@NOSPAMhotmail.com> wrote in message
news:ep1JGO2kDHA.392@TK2MSFTNGP11.phx.gbl...

> Yes, I appreciate what you are saying.
>
> I want a manager to be able to answer the question, "How long (on average)
> did the end user have to wait (and theoretically, was unable to work) "
>
> So, I'm really after--the number of business hours lost.
>

This is actually quite a complex problem. Even if you make a radical
assumption such as working hours are Mon-Friday 9-5, then you have to step
through each day from start to finish, identifying each day as a workday.
Remember, each month will have a differing number of days and weekdays. Not
something you could do in database query.

Then, you perhaps would need to be more realistic - what about
public/business holidays?

--
John Blessing

[url]http://www.LbeHelpdesk.com[/url] - Help Desk software priced to suit all
businesses
[url]http://www.free-helpdesk.com[/url] - Completely free help desk software !
[url]http://www.lbetoolbox.com[/url] - Remove Duplicates from MS Outlook

[Tom B]

Thanks for that.

I do realize there are complexities involved, I was just wondering if anyone
had a suggestion. You're right, I completely forgot about weekends and
holidays.

Thanks

"John Blessing" <jb@**REMOVE**THIS**LbeHelpdesk.com> wrote in message
news:y9jjb.1432$KA5.15458@newsfep4-glfd.server.ntli.net...

> "Tom B" <shuckle@NOSPAMhotmail.com> wrote in message
> news:ep1JGO2kDHA.392@TK2MSFTNGP11.phx.gbl...

> > Yes, I appreciate what you are saying.
> >
> > I want a manager to be able to answer the question, "How long (on

average)

> > did the end user have to wait (and theoretically, was unable to work) "
> >
> > So, I'm really after--the number of business hours lost.
> >

>
>
> This is actually quite a complex problem. Even if you make a radical
> assumption such as working hours are Mon-Friday 9-5, then you have to step
> through each day from start to finish, identifying each day as a workday.
> Remember, each month will have a differing number of days and weekdays.

Not

> something you could do in database query.
>
> Then, you perhaps would need to be more realistic - what about
> public/business holidays?
>
> --
> John Blessing
>
> [url]http://www.LbeHelpdesk.com[/url] - Help Desk software priced to suit all
> businesses
> [url]http://www.free-helpdesk.com[/url] - Completely free help desk software !
> [url]http://www.lbetoolbox.com[/url] - Remove Duplicates from MS Outlook
>
>

[Aaron Bertrand - MVP]

> This is actually quite a complex problem. Even if you make a radical

> assumption such as working hours are Mon-Friday 9-5, then you have to step
> through each day from start to finish, identifying each day as a workday.
> Remember, each month will have a differing number of days and weekdays.

Not

> something you could do in database query.

You could, just not trivial. There is an example here that uses days; the
table would be a bit larger if you had hour divisions, but the logic would
be similar:
[url]http://www.aspfaq.com/2453[/url]

[Tom B]

Thanks Aaron, this is way more complicated then expected.

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:ej98t2%23kDHA.2444@TK2MSFTNGP09.phx.gbl...

> > This is actually quite a complex problem. Even if you make a radical
> > assumption such as working hours are Mon-Friday 9-5, then you have to

step

> > through each day from start to finish, identifying each day as a

workday.

> > Remember, each month will have a differing number of days and weekdays.

> Not

> > something you could do in database query.

>
> You could, just not trivial. There is an example here that uses days; the
> table would be a bit larger if you had hour divisions, but the logic would
> be similar:
> [url]http://www.aspfaq.com/2453[/url]
>
>

[Unregistered]

you could try this. work out the total hours and then remove the non worked hours

Private Function calcBusinessHours(ByVal startTime As Date, ByVal endTime As Date) As Integer

Dim intHours As Integer
Dim intDays As Integer

intHours = DateDiff(DateInterval.Hour, startTime, endTime)
intdays = DateDiff(DateInterval.Day, startTime, endTime)

' If the start time and the end time is on the same day then return the total number of hours
If startTime.Date = endTime.Date Then
Return intHours
End If

For x = 0 To intDays - 1
If startTime.DayOfWeek Then
' -weekdays
intHours -= 15
Else
' -weekends
intHours -= 24
End If
startTime = startTime.AddDays(1)
Next

Return intHours

End Function