Need query to calculate my SLA Begin Time

[TraciAnnNeedsHelp]

I need to start monitoring my company's response time when a customer calls
for help. I already have a database that stores the data for the calls and
the responses but I don't have a way of calculating the results. Mainly
because the complexity of the difference between a stored value [CreateTime]
and the variable I need to calculate [StartSLA] which takes into account
non-business hours.

I have built a query using 2 tables which have the following fields used in
the query:
- [CreateTime] – Date/Time (UTC) when ticket is created
- [SLAType] – 1 = 24hr; 2 = 4hr
- [EventID] – 7 = Onsite Service
- [EventTime] – Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00 (current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help is
greatly appreciated.

Gratefully,
TraciAnn

 

[Clifford Bass]

Hi TraciAnn,

By 12:00 do you mean midnight? Or noon? Assuming midnight. Try:

StartSLA = IIf([SLAType] = 1, IIf([CreateTime] Between #0:00# and #16:00#,
#16:00#, #0:00#), IIf([CreateTime] Between #8:00# and #18:00#, [CreateTime],
#8:00#))

Clifford Bass

 

[Clifford Bass]

Hi TraciAnn,

Oops. I forgot about the business day and the holiday part, partly due
to your naming of the CreateTime field which implies only a time. How do you
know what days are holidays?

Clifford Bass

Hi TraciAnn,

By 12:00 do you mean midnight? Or noon? Assuming midnight. Try:

StartSLA = IIf([SLAType] = 1, IIf([CreateTime] Between #0:00# and #16:00#,
#16:00#, #0:00#), IIf([CreateTime] Between #8:00# and #18:00#, [CreateTime],
#8:00#))

Clifford Bass

I need to start monitoring my company's response time when a customer calls
for help. I already have a database that stores the data for the calls and
the responses but I don't have a way of calculating the results. Mainly
because the complexity of the difference between a stored value [CreateTime]
and the variable I need to calculate [StartSLA] which takes into account
non-business hours.

I have built a query using 2 tables which have the following fields used in
the query:
- [CreateTime] – Date/Time (UTC) when ticket is created
- [SLAType] – 1 = 24hr; 2 = 4hr
- [EventID] – 7 = Onsite Service
- [EventTime] – Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00 (current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help is
greatly appreciated.

Gratefully,
TraciAnn

 

[TraciAnnNeedsHelp]

Hi Clifford! Thanks for your effort on this.

First, I used military time to be more clear; therefore, 12:00 is noon
(twelve hundred hours).

As for the holidays, I don't know. Would I need to compare to a table of
holidays? It wouldn't be difficult to create because Outlook tracks holidays
and I could dump those into a text file and pull them into a table. I only
need "bankers' holidays" (the days banks and government organizations
traditionally are closed for business).

Hi TraciAnn,

Oops. I forgot about the business day and the holiday part, partly due
to your naming of the CreateTime field which implies only a time. How do you
know what days are holidays?

Clifford Bass

Hi TraciAnn,

By 12:00 do you mean midnight? Or noon? Assuming midnight. Try:

StartSLA = IIf([SLAType] = 1, IIf([CreateTime] Between #0:00# and #16:00#,
#16:00#, #0:00#), IIf([CreateTime] Between #8:00# and #18:00#, [CreateTime],
#8:00#))

Clifford Bass

I need to start monitoring my company's response time when a customer calls
for help. I already have a database that stores the data for the calls and
the responses but I don't have a way of calculating the results. Mainly
because the complexity of the difference between a stored value [CreateTime]
and the variable I need to calculate [StartSLA] which takes into account
non-business hours.

I have built a query using 2 tables which have the following fields used in
the query:
- [CreateTime] – Date/Time (UTC) when ticket is created
- [SLAType] – 1 = 24hr; 2 = 4hr
- [EventID] – 7 = Onsite Service
- [EventTime] – Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00 (current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help is
greatly appreciated.

Gratefully,
TraciAnn

 

[Clifford Bass]

Hi TraciAnn,

A table with the holidays specified is probably the best way to deal
with it. I am not aware of any specific functions in Access that know about
holidays. Complicated by the fact that one organization's holidays can be
different from another's. I am leaning towards using a custom VBA function
to do the computation. I will give it some thought and get back to you.

Clifford Bass

 

[TraciAnnNeedsHelp]

Oh...Thank You!!!

I am really green with VBA but I follow instructions very well. Thank you so
much!!!

TraciAnn

 

[Clifford Bass]

Hi TraciAnn,

Some questions: To what is the current business day in relation?
Today? CreateTime? Something else? How is it related?

Clifford Bass

Hi Clifford! Thanks for your effort on this.

First, I used military time to be more clear; therefore, 12:00 is noon
(twelve hundred hours).

As for the holidays, I don't know. Would I need to compare to a table of
holidays? It wouldn't be difficult to create because Outlook tracks holidays
and I could dump those into a text file and pull them into a table. I only
need "bankers' holidays" (the days banks and government organizations
traditionally are closed for business).

Hi TraciAnn,

Oops. I forgot about the business day and the holiday part, partly due
to your naming of the CreateTime field which implies only a time. How do you
know what days are holidays?

Clifford Bass

Hi TraciAnn,

By 12:00 do you mean midnight? Or noon? Assuming midnight. Try:

StartSLA = IIf([SLAType] = 1, IIf([CreateTime] Between #0:00# and #16:00#,
#16:00#, #0:00#), IIf([CreateTime] Between #8:00# and #18:00#, [CreateTime],
#8:00#))

Clifford Bass

:

I need to start monitoring my company's response time when a customer calls
for help. I already have a database that stores the data for the calls and
the responses but I don't have a way of calculating the results. Mainly
because the complexity of the difference between a stored value [CreateTime]
and the variable I need to calculate [StartSLA] which takes into account
non-business hours.

I have built a query using 2 tables which have the following fields used in
the query:
- [CreateTime] – Date/Time (UTC) when ticket is created
- [SLAType] – 1 = 24hr; 2 = 4hr
- [EventID] – 7 = Onsite Service
- [EventTime] – Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00 (current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help is
greatly appreciated.

Gratefully,
TraciAnn

 

[TraciAnnNeedsHelp]

When I reference "current business day" I am infering the CreateDate/Time. It
is conceivable that CreateDate can occur during non-business hours (evenings,
weekends, holidays); therefore, the need for StartSLA.

Thanks Clifford!!
TraciAnn

Hi TraciAnn,

Some questions: To what is the current business day in relation?
Today? CreateTime? Something else? How is it related?

Clifford Bass

Hi Clifford! Thanks for your effort on this.

First, I used military time to be more clear; therefore, 12:00 is noon
(twelve hundred hours).

As for the holidays, I don't know. Would I need to compare to a table of
holidays? It wouldn't be difficult to create because Outlook tracks holidays
and I could dump those into a text file and pull them into a table. I only
need "bankers' holidays" (the days banks and government organizations
traditionally are closed for business).

Hi TraciAnn,

Oops. I forgot about the business day and the holiday part, partly due
to your naming of the CreateTime field which implies only a time. How do you
know what days are holidays?

Clifford Bass

:

Hi TraciAnn,

By 12:00 do you mean midnight? Or noon? Assuming midnight. Try:

StartSLA = IIf([SLAType] = 1, IIf([CreateTime] Between #0:00# and #16:00#,
#16:00#, #0:00#), IIf([CreateTime] Between #8:00# and #18:00#, [CreateTime],
#8:00#))

Clifford Bass

:

I need to start monitoring my company's response time when a customer calls
for help. I already have a database that stores the data for the calls and
the responses but I don't have a way of calculating the results. Mainly
because the complexity of the difference between a stored value [CreateTime]
and the variable I need to calculate [StartSLA] which takes into account
non-business hours.

I have built a query using 2 tables which have the following fields used in
the query:
- [CreateTime] – Date/Time (UTC) when ticket is created
- [SLAType] – 1 = 24hr; 2 = 4hr
- [EventID] – 7 = Onsite Service
- [EventTime] – Date/Time (UTC) of Event

StartSLA is based on UTC field [CreateTime] but needs adjusted based on
business hours

Business hours are different based on [SLAType]
- If SLA is 4hr then Business Hours are 8:00:00 to 17:59:59, M-F not
including holidays
- If SLA is 24hr then Business Hours are 8:00:00 to 15:59:59, M-F not
including holidays

[CreateTime] Adjustments
- If [SLAType] In (1) And [CreateTime] Between 16:00 (previous business day)
And 12:00 (current business day) [StartSLA] = 12:00 (current business day)

- If [SLAType] In (1) And [CreateTime] Between 12:00 (current business day)
And 16:00:00 (current business day), [StartSLA] = 16:00:00 (current business
day)

- If [SLAType] In (2) And [CreateTime] Between 18:00 (previous business day)
And 08:00 (current business day), [StartSLA] = 08:00 (current business day)

- If [SLAType] In (2) And [CreateTime] Between 08:00 And 16:00 (current
business day), [StartSLA] = [CreateTime]

I know this looks a bit daunting, but I really need this and any help is
greatly appreciated.

Gratefully,
TraciAnn

 

[Clifford Bass]

Hi TraciAnn,

Final question I hope. If the CreateTime is sometime this Sunday (8
March 2009), what is the current business day? Friday, 6 March or Monday, 9
March?

Clifford Bass

 

[TraciAnnNeedsHelp]

I understand how that can be confusing.

In your sample below: "current business day" is referring to CreateTime BUT
the SLAStart time would be First Business Day after CreateTime.

I hope that doesn't muddy the water any..

TraciAnn

 

[TraciAnnNeedsHelp]

Hi Clifford!

Was my answer too confusing?

Hi TraciAnn,

Final question I hope. If the CreateTime is sometime this Sunday (8
March 2009), what is the current business day? Friday, 6 March or Monday, 9
March?

Clifford Bass

 

[Clifford Bass]

Hi TraciAnn,

Begging pardon for the delay :-( For some reason Microsoft's
notification (along with a lot of other functionality) apparently was not
working and I never knew you replied until today when I did a search for my
posts to see if I had missed anything. This is further annoying in that I
had searched multiple times in the last two weeks and you post never showed
up. Grr!!

Anyway, did you ever solve the problem? If not, let me know and I will
see what I can come up with.

Clifford Bass

 

[TraciAnn]

I believe you experienced the same frustration as many others...including me.

I have rarely been able to view posts or make additional posts.

To answer your question, no, I still do not have a solution to this one.

thank you for your reply!

 

[John W. Vinson]

I believe you experienced the same frustration as many others...including me.

I have rarely been able to view posts or make additional posts.

To answer your question, no, I still do not have a solution to this one.

The newsgroups are NOT on Microsoft's webpage!

In fact, Microsoft's webpage is a notoriously faulty newsreader.

The newsgroups are actually on (working!) NNTP news servers hosted by
Microsoft. They can be read from the webpage but also from any "Newsreader".
Windows Mail and Microsoft Outlook Express (not Outlook, but OE which came
with Internet Explorer) are adequate newsreaders; there are many others, my
favorite being Agent (from www.forteinc.com). Just point the newsreader to
news.microsoft.com or msnews.microsoft.com as the news server.

You can search the newsgroup archives on http://groups.google.com. Use
"Advanced Search" and limit it to the microsoft.public.access.* newsgroups.

 

[Clifford Bass]

Hi TraciAnn,

Okay, here is what I hope is a solution. If I understood things
correctly.

Create a table named "tblHolidays" that contains one column, named
"HolidayDate", obviously of type Date/Time. Make it the primary key.

Then copy the following code into a regular module (not class, form or
report).

----------------------------------------------------------

Public Function CalculateStartSLA( _
ByVal intSLAType As Integer, _
ByVal dtCreateDateTime As Date) As Variant

Dim dtCreateDay As Date
Dim dtCreateTime As Date
Dim dtCurrentBusinessDay As Date
Dim dowTemp As VbDayOfWeek
Dim varHoliday As Variant

On Error GoTo Handle_Error:

If intSLAType < 1 Or intSLAType > 2 Then
Err.Raise 513, "CalculateStartSLA", _
"Invalid SLA type."
End If

dtCreateDay = DateSerial(Year(dtCreateDateTime), _
Month(dtCreateDateTime), Day(dtCreateDateTime))
dtCreateTime = TimeSerial(Hour(dtCreateDateTime), _
Minute(dtCreateDateTime), Second(dtCreateDateTime))

' Determine current business day
dtCurrentBusinessDay = dtCreateDay
If (intSLAType = 1 And dtCreateTime >= #4:00:00 PM#) _
Or (intSLAType = 2 And dtCreateTime >= #6:00:00 PM#) _
Then
dtCurrentBusinessDay = dtCurrentBusinessDay + 1
End If
Do While True
dowTemp = Weekday(dtCurrentBusinessDay, vbSunday)
If dowTemp >= vbMonday And dowTemp <= vbFriday Then
varHoliday = DLookup("[HolidayDate]", _
"tblHolidays", "[HolidayDate] = #" & _
dtCurrentBusinessDay & "#")
If IsNull(varHoliday) Then
Exit Do
End If
End If
dtCurrentBusinessDay = dtCurrentBusinessDay + 1
Loop

Select Case intSLAType
Case 1
If dtCreateDay = dtCurrentBusinessDay And _
dtCreateTime >= #12:00:00 PM# And _
dtCreateTime <= #4:00:00 PM# Then
CalculateStartSLA = dtCurrentBusinessDay _
+ #4:00:00 PM#
Else
CalculateStartSLA = dtCurrentBusinessDay _
+ #12:00:00 PM#
End If

Case 2
If dtCreateDay = dtCurrentBusinessDay And _
dtCreateTime >= #8:00:00 AM# And _
dtCreateTime <= #4:00:00 PM# Then
CalculateStartSLA = dtCreateDateTime
Else
CalculateStartSLA = dtCurrentBusinessDay _
+ dtCreateTime = #8:00:00 AM#
End If

End Select

Exit_Function:
Exit Function

Handle_Error:
CalculateStartSLA = "Error #" & Err.Number
Resume Exit_Function

End Function

----------------------------------------------------------

Now, whenever you want to calculate the SLA, use the function, passing
it the SLA type and the create date/time. So in the SQL of a query it might
look like this:

SELECT [SLAType], [CreateTime], [EventID], [EventTime],
CalculateStartSLA([SLAType], [CreateTime]) AS [StartSLA]
FROM [tblYourTable];

If entering it into a query while in design mode, you would enter into
a cell in the top row:

StartSLA: CalculateStartSLA([SLAType], [CreateTime])

Give it a try and see if it does what you want. If not post back,
detailing what values you are passing in and what values are expected.

Clifford Bass

 

[Clifford Bass]

Hi TraciAnn,

Stating the unmentioned obvious: Add the appropriate holiday dates
into the tblHolidays table to cover your CreateTime range.

Clifford Bass

 

[Clifford Bass]

Hi TraciAnn,

One other thing. Ideally, the SLA types and their associated times
should really be stored in a table, which would then be used in the code
rather than hard coding those types and times. I chose not to do that so as
to keep it simpler while focusing on getting the correct results.

Clifford Bass

 

[TraciAnn]

Thanks Clifford!

It looks logical. I will be a week or two before I can test it though. Due
to my slow progress of this particular project they have me working on
another application which has my full attention right now (at least they
haven't released me yet ).

Your solution is VERY important to me. So I don't want you to think your
work has been unappreciated due to my lack of response over the next couple
of weeks.

Gratefully,
--
TraciAnn

Hi TraciAnn,

Okay, here is what I hope is a solution. If I understood things
correctly.

Create a table named "tblHolidays" that contains one column, named
"HolidayDate", obviously of type Date/Time. Make it the primary key.

Then copy the following code into a regular module (not class, form or
report).

----------------------------------------------------------

Public Function CalculateStartSLA( _
ByVal intSLAType As Integer, _
ByVal dtCreateDateTime As Date) As Variant

Dim dtCreateDay As Date
Dim dtCreateTime As Date
Dim dtCurrentBusinessDay As Date
Dim dowTemp As VbDayOfWeek
Dim varHoliday As Variant

On Error GoTo Handle_Error:

If intSLAType < 1 Or intSLAType > 2 Then
Err.Raise 513, "CalculateStartSLA", _
"Invalid SLA type."
End If

dtCreateDay = DateSerial(Year(dtCreateDateTime), _
Month(dtCreateDateTime), Day(dtCreateDateTime))
dtCreateTime = TimeSerial(Hour(dtCreateDateTime), _
Minute(dtCreateDateTime), Second(dtCreateDateTime))

' Determine current business day
dtCurrentBusinessDay = dtCreateDay
If (intSLAType = 1 And dtCreateTime >= #4:00:00 PM#) _
Or (intSLAType = 2 And dtCreateTime >= #6:00:00 PM#) _
Then
dtCurrentBusinessDay = dtCurrentBusinessDay + 1
End If
Do While True
dowTemp = Weekday(dtCurrentBusinessDay, vbSunday)
If dowTemp >= vbMonday And dowTemp <= vbFriday Then
varHoliday = DLookup("[HolidayDate]", _
"tblHolidays", "[HolidayDate] = #" & _
dtCurrentBusinessDay & "#")
If IsNull(varHoliday) Then
Exit Do
End If
End If
dtCurrentBusinessDay = dtCurrentBusinessDay + 1
Loop

Select Case intSLAType
Case 1
If dtCreateDay = dtCurrentBusinessDay And _
dtCreateTime >= #12:00:00 PM# And _
dtCreateTime <= #4:00:00 PM# Then
CalculateStartSLA = dtCurrentBusinessDay _
+ #4:00:00 PM#
Else
CalculateStartSLA = dtCurrentBusinessDay _
+ #12:00:00 PM#
End If

Case 2
If dtCreateDay = dtCurrentBusinessDay And _
dtCreateTime >= #8:00:00 AM# And _
dtCreateTime <= #4:00:00 PM# Then
CalculateStartSLA = dtCreateDateTime
Else
CalculateStartSLA = dtCurrentBusinessDay _
+ dtCreateTime = #8:00:00 AM#
End If

End Select

Exit_Function:
Exit Function

Handle_Error:
CalculateStartSLA = "Error #" & Err.Number
Resume Exit_Function

End Function

----------------------------------------------------------

Now, whenever you want to calculate the SLA, use the function, passing
it the SLA type and the create date/time. So in the SQL of a query it might
look like this:

SELECT [SLAType], [CreateTime], [EventID], [EventTime],
CalculateStartSLA([SLAType], [CreateTime]) AS [StartSLA]
FROM [tblYourTable];

If entering it into a query while in design mode, you would enter into
a cell in the top row:

StartSLA: CalculateStartSLA([SLAType], [CreateTime])

Give it a try and see if it does what you want. If not post back,
detailing what values you are passing in and what values are expected.

Clifford Bass

I believe you experienced the same frustration as many others...including me.

I have rarely been able to view posts or make additional posts.

To answer your question, no, I still do not have a solution to this one.

thank you for your reply!

 

[Clifford Bass]

Hi TraciAnn,

You are quite welcome! And I won't worry about a slow response.

Clifford Bass