Help with weekend calculations needed....

[michael.staschull]

Hi,

Maybe someone can help me with this one:

1. I have one table called "On Hold Data". This table has the
following fields/data:

Request No Problem Severity Seq# Time On-Hold Time Released Total On-
Hold Time
36883 Severity 3 1 12/20/06 3:17 PM 12/20/06 3:18 PM
36883 Severity 3 2 12/21/06 7:08 AM 1/12/07 2:39 PM
36909 Severity 3 1 12/20/06 4:02 PM 1/3/07 6:16 PM
37879 Severity 3 1 1/12/07 5:47 PM 1/16/07 9:41 AM
37931 Severity 3 1 1/14/07 6:51 AM 1/15/07 2:25 AM
37930 Severity 3 1 1/14/07 5:00 AM 1/14/07 10:17 AM
37928 Severity 3 1 1/14/07 4:19 AM 1/14/07 9:20 AM
37633 Severity 3 1 1/8/07 4:02 PM 1/11/07 3:48 PM
37828 Severity 3 1 1/12/07 3:26 AM 1/16/07 5:05 AM
35765 Severity 4 1 11/28/06 9:43 AM 11/29/06 6:42 PM
35765 Severity 4 2 11/29/06 6:43 PM 12/13/06 1:33 PM
35765 Severity 4 3 12/20/06 5:49 PM 1/31/07 7:50 AM
35193 Severity 4 1 11/30/06 12:30 PM 1/3/07 10:32 AM
35193 Severity 4 2 1/3/07 3:47 PM 1/16/07 12:44 PM
35272 Severity 4 1 11/20/06 1:41 AM 12/4/06 7:45 AM

For Severity 3 & 4, I am working on a 5 day workweek starting from
Sunday 07:00 PM to Friday 07:00 PM EST. I need to get the "Total On-
Hold Time" calculated on this 5 day calendar (The only "cut off time"
is from Friday 07:00 PM to Sunday 07:00 PM).

Can someone help me building this fuction which can be used in an
update query ??

2. The second table is named Tickets and contains the following fields/
data:

Request No Problem Severity Dispatched Date/Time SLA Response Time
SLA Resolution Time Total On-Hold Time SLA Resolution Time2
36883 Severity 3 12/20/06 10:23 AM
36909 Severity 3 12/20/06 10:52 AM
37879 Severity 3 1/12/07 6:14 PM
37931 Severity 3 1/14/07 3:29 AM
37930 Severity 3 1/14/07 4:54 AM
37928 Severity 3 1/14/07 3:58 AM
37633 Severity 3 1/8/07 2:50 PM
37828 Severity 3 1/11/07 6:57 PM
35765 Severity 4 11/28/06 6:38 AM
35193 Severity 4 11/16/06 8:04 AM
35272 Severity 4 11/17/06 10:24 AM

I need the following solution:

The SLA response time for Severity 3 is 2 hrs but it should take the
above mentioned cut off time (Friday 07:00 PM to Sunday 07:00 PM) into
consideration. Meaning for ticket 37879 I have 46 minutes till 07:00
PM, then the cut off time kicks in till Sunday 07:00 PM. For this
ticket the SLA Response time should be Sunday 08:14 PM. For Severity 4
the response time is 24 hrs with the same cut off times.

The SLA resolution time for Severity 3 is 48 hrs, for Severity 4 it is
96 hrs. with the same cut off times.

The Total On-Hold time will come from the first table and I do know
how to get the update query done, but I would like to get a logic in
place which does an update based on a condition. The condition should
check if the Time On-Hold from the On-Hold Data table is < SLA
Resolution Time from the Tickets table. If this is the case then
update "SLA Resolution Time2" = "SLA Resolution Time" + "Total On-Hold
Time", else "SLA Resolution Time2" = "SLA Resolution Time".

Any help is highly appreciated.

Michael