Removing out of hours time

[John McGinty]

Hello Chaps,

Just a little sounding on removing out of hours from some datetime
date that I have.
Basically we have a helpdesk that logs calls when they are entered and
when they are resolved. Now, if this happens during the day we just
subtract one date from the other to give the time it took to resolve
the issue. If it is not solved during the day we have an additional 11
hours to add due to people going home and not worrying about peoples
printers which is good but it buggers up stats that I'm trying to
produce.

I haven't got anywhere with this at the mo and am looking at a routine
that compares the resolved time and see's is there is more that 24
hours difference and if so remove 11 hours but wondered if you bright
sparks had any ideas
(also once I write something I tend to find the answer straight after
to confirm me stoppidity).
Any ideas let us know.

Ginters

[nib]

Re: Removing out of hours time

John McGinty wrote:[color=blue]
> Hello Chaps,
>
> Just a little sounding on removing out of hours from some datetime
> date that I have.
> Basically we have a helpdesk that logs calls when they are entered and
> when they are resolved. Now, if this happens during the day we just
> subtract one date from the other to give the time it took to resolve
> the issue. If it is not solved during the day we have an additional 11
> hours to add due to people going home and not worrying about peoples
> printers which is good but it buggers up stats that I'm trying to
> produce.
>
> I haven't got anywhere with this at the mo and am looking at a routine
> that compares the resolved time and see's is there is more that 24
> hours difference and if so remove 11 hours but wondered if you bright
> sparks had any ideas
> (also once I write something I tend to find the answer straight after
> to confirm me stoppidity).
> Any ideas let us know.
>
> Ginters[/color]

Have you looked at the DATEDIFF() and DATEADD() functions, they seem to
be just what you need. :D

Zach

[John McGinty]

Re: Removing out of hours time

Cheers Zach your right with the datediff and here's how I think yon
remove out of hours time (there is few issues if you can spot them!)

1. Helpdesk opens at 9am and closes at 6pm

select issue_name, date_submitted, date_resolved,
-- so we display the issue name, the time it was added and the time it
was resolved. Not interested in ones not resolved.

datediff(minute , date_submitted, date_resolved) as 'Diff_Min',
--difference in minutes. Shows me the time to resolve in minutes

datediff(day, date_submitted, date_resolved) as 'Diff_Day',
--number of days before resolution.

datediff(day, date_submitted, date_resolved) * (15*60) as 'Difference',
--This takes the total number of days and subtracts the dead time which
in this case is 15 hours and then multiplies this by 60 to get the
minutes. This is the total dead time in minutes.

datediff(minute , date_submitted, date_resolved)-datediff(day,
date_submitted, date_resolved) * (13*60) as 'DiffTo_min',
--subtracting the total minutes by the dead time minutes gives me the
total 'live minutes'

(datediff(minut e, date_submitted, date_resolved)-datediff(day,
date_submitted, date_resolved) * (13*60))/60 as 'DiffTo_hrs'
-- this is the same as above but in hours.
from test_table2

What dya think?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[nib]

Re: Removing out of hours time

John McGinty wrote:[color=blue]
> Cheers Zach your right with the datediff and here's how I think yon
> remove out of hours time (there is few issues if you can spot them!)
>
> <snipped>
>
> What dya think?
>[/color]

Heh, I think that without DDL and sample data reading that makes my head
hurt. :D

Zach