Obtaining business hours based on start and end date

[mitchchristensen]

I have a transaction log that tracks issues from a call center. Each
time an issue is assigned to someone else, closed, etc. I get a time
stamp. I have these time stamps for the beginning of an issue to the
end of an issue and I'd like to determine how many business hours these
issues were open.

Issue BeginDt Enddt Total hours
1 3/29/05 5:00 PM 4/1/05 2:00 PM 69

Basically, this is the type of data I'm looking at and my hours of work
are from 7:30 - 5:00 weekdays. I need to come up with a way to remove
all nonbusiness hours, weekends, & holidays from the difference of the
two dates. Issues can span for 2-3 days or 20-30 days.

Please let me know if anyone has any ideas or has done something like
this before.

Thanks!

[Hugo Kornelis]

On 1 Apr 2005 14:26:14 -0800, mi**************@gmail.com wrote:

I have a transaction log that tracks issues from a call center. Each
time an issue is assigned to someone else, closed, etc. I get a time
stamp. I have these time stamps for the beginning of an issue to the
end of an issue and I'd like to determine how many business hours these
issues were open.

Issue BeginDt Enddt Total hours
1 3/29/05 5:00 PM 4/1/05 2:00 PM 69

Basically, this is the type of data I'm looking at and my hours of work
are from 7:30 - 5:00 weekdays. I need to come up with a way to remove
all nonbusiness hours, weekends, & holidays from the difference of the
two dates. Issues can span for 2-3 days or 20-30 days.

Please let me know if anyone has any ideas or has done something like
this before.

Thanks!

Hi mitchchristensen,

The easiest way to do it is to use a calendar table. What that is, how
you can make it and various good ways to use it are described at Aaron's
site: http://www.aspfaq.com/show.asp?id=2519.

For this specific situation, I'd suggest the following approach:

DECLARE @Start smalldatetime,
@End smalldatetime
SET @Start = '2005-03-22T17:00:00'
SET @End = '2005-04-01T14:00:00'

SELECT DATEDIFF (minute, @Start, @End) / 60.0
- DATEDIFF (day, @Start, @End) * 14.5
- (SELECT COUNT(*)
FROM Calendar
WHERE dt > @Start
AND dt < @End
AND (isWeekday = 0 OR isHoliday = 1)) * 9.5

This might not be the quickes, but it has the advantage that it'spretty
straightforward: first, calculate the number of clock hours from start
to end; then subtract 14.5 hours (the time from 5:00 PM - 7:30 AM) for
each full day in the range; finally subtract another 9.5 hours (the time
from 7:30 AM to 5:00 PM) for each weekend or holiday in the range.

The assumption I made is that start and end dates will always be during
opening hours (i.e. not on weekends or on holidays and never outside the
7:30 AM - 5:00 PM range).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:

Hi mitchchristensen,

The easiest way to do it is to use a calendar table. What that is, how
you can make it and various good ways to use it are described at Aaron's
site: http://www.aspfaq.com/show.asp?id=2519.

For this specific situation, I'd suggest the following approach:

DECLARE @Start smalldatetime,
@End smalldatetime
SET @Start = '2005-03-22T17:00:00'
SET @End = '2005-04-01T14:00:00'

SELECT DATEDIFF (minute, @Start, @End) / 60.0
- DATEDIFF (day, @Start, @End) * 14.5
- (SELECT COUNT(*)
FROM Calendar
WHERE dt > @Start
AND dt < @End
AND (isWeekday = 0 OR isHoliday = 1)) * 9.5

Since it seems unlikely that Mitch would like to disregard Christmas,
Thanksgiving and other holidays, Hugo solutions is very good. However,
I believe this is a solution that would work if we are for some reason
talking all Monday to Friday:

SELECT DATEDIFF (minute, @start, @stop) / 60.0 -
DATEDIFF (day, @start, @stop) * 14.5 -
DATEDIFF (week, @start, @stop) * 2 * 9.5

I owe Hugo's original query lot for this extension.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[mitchchristensen]

Thanks for the suggestions!

Unfortunately I do need to disregard certain holidays based on the
region(I will have a holiday calendar for each region)

Also, end dates can occur on weekends or even holidays as some issues
are closed by the system based on a time parameter and the system has
no regards for whether or not it's an actual working day.

Mitch

[pb648174]

I have tried to do this in SQL and can tell you that you will be better
off doing it in application logic. The solution posted above performs
terribly.

[Erland Sommarskog]

(mi**************@gmail.com) writes:

Unfortunately I do need to disregard certain holidays based on the
region(I will have a holiday calendar for each region)
Well, Hugo's solution should be your choice.
Also, end dates can occur on weekends or even holidays as some issues
are closed by the system based on a time parameter and the system has
no regards for whether or not it's an actual working day.

Since I don't have any test data, I can't test Hugo's solution
for the case where the end date is a non a working day, but at a
glance it appers that his solution should handle this situation.

Since it was time, I repost Hugo's solution here:

DECLARE @Start smalldatetime,
@End smalldatetime
SET @Start = '2005-03-22T17:00:00'
SET @End = '2005-04-01T14:00:00'

SELECT DATEDIFF (minute, @Start, @End) / 60.0
- DATEDIFF (day, @Start, @End) * 14.5
- (SELECT COUNT(*)
FROM Calendar
WHERE dt > @Start
AND dt < @End
AND (isWeekday = 0 OR isHoliday = 1)) * 9.5

The complete thead can be reviewed at
http://groups.google.com/groups?dq=&...127.0.0.1%253E
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp