Function for Determining Completion Time

The company I work for uses tracking software for entering help desk
(IT-related) issues. The issues is timestamped and assigned a Priority
level (A-D). Each level relates to an estimated completion time. What
I'm trying to accomplish is to add the estimated completion time to the
time and date. For example. If a customer sends in an issue at 12:21PM
with a priority level of 'A' then it should be known that the issue
should be done at 2:22PM.
Key:
A - 2 hours
B - 8 hours
C - 48 hours
D - 72 hours
This is where it gets tricky. Our business hours are from 7:30am -
4:00pm. I would like to account for this in the function. So if a
customer sends in a request on 01/21/2006 @. 3:30PM with a 'B' priority
level it should be fulfilled by 01/22/2006 @. 3:00PM.
I don't expect anyone to write up a function for this write away. Any
pointers, suggestions, or direction would be greatly appreciated.
*** Sent via Developersdex http://www.examnotes.net ***Hello, Will
Hint: If your workday is 8 and a half hours long (and wends don't
count) you can say:
A = 2 hours = 1 day minus 6.5 hours
B = 8 hours = 1 day minus 0.5 hours
C = 48 hours = 6 days minus 3 hours
D = 72 hours = 9 days minus 4.5 hours
and use a CASE expression to see which of the above is within working
hours.
But I have a feeling that you don't work on sundays, so this method
will not be good enough...
Razvan|||Because of wends and holidays, you cannot use a formula for this.
Try a look-up table instead:
CREATE TABLE CompletionTimes
(submission_time DATETIME NOT NULL PRIMARY KEY,
A_complete_time DATETIME NOT NULL,
B_complete_time DATETIME NOT NULL,
C_complete_time DATETIME NOT NULL,
D_complete_time DATETIME NOT NULL);
15 minute intervals is probably good enough, so each day is 07:30 to
16:00 hrs to give us about 30 time slots. Again, just estimating, you
have 250 work days to a year, so the table is about 75,000 rows for ten
years.
A calendar table for US Secular holidays can be built from the data at
this website, so you will get the three-day wends:
http://www.smart.net/~mmontes/ushols.html