Time Calculations

[nikolaus21]

Hi,

I need some help with cut off time (off time) calculations in MS-Access. I have a database with dispatch dates for tickets. Based on the Severity there is a response time associated with it. Now, for Severity 3 tickets we have a response Time of 2 hrs. (Severity 4 = 24 hrs.) from Sunday 07:00 PM EST to Friday 07:00 PM EST. If a Severity 3 ticket gets dispatched on Friday 05:30 PM, I need a function/SQL which takes this date and updates "SLA Response time" field to Sunday 07:30 PM, same goes for Severity 4 issues. Anyone has a clue/function on how to resolve this ???

Thanks,
Michael

 

[ErikSnoek]

I'm not sure if this is what you wanted to know, but you can add hours to a time by using

DateAdd("h", number of hours, your time)

 

[nikolaus21]

I know of this function, but it does not do the trick

 

[willpower]

It looks like you need to test for the day of the week either the ticket time or the response time falls into. You can test for the weekday in VBA using the Weekday(Date, [FirstDayofWeek As vbDayofWeek = vbSunday]) function.

I normally omit the [FirstDayOfWeek] optional parameter and test against the vb enums, so, if I want to know if a date falls on a Monday, I would use:

If Weekday(mydate) = vbMonday Then
...
End If

By using this in combination with your time test, you can then work out if you need to DateAdd("d", 2, yourdate) i.e. add two days to the SLA response time...

willpower

 

[nikolaus21]

Hi Willpower,

Maybe you can point out here the exact steps I have to follow (function) on how to get this done. The ideal would be a function to be used in an update statement to populate "SLA Response time" based on the dispatch date/time and Severity.....

Thanks in advance,
Michael

 

[willpower]

Sorry for not responding earlier...been on leave.

I think you want tomething like this... I haven't exactly tested this thoroughly, but off the top of my head, you want to call this function, passing in the date (and time) of the ticket dispatch, and the severity level as a number, e.g. yourSLATime = fnSLA(yourDispatchTime, yourseverityLevel)

Public Function fnSLA(dtTicket As Date, intSeverity As Integer) As Date

Dim dblResponse As Double

'intSeverity is the Severity Rating of the Ticket (i.e. 3 or 4)
Select Case intSeverity
    'Define the reponse times (in hours) here, so if severity is 3, then the response time is 4 hours
    Case Is = 3
        dblResponse = 4
    Case Is = 4
        'Or if severity is 4, response time is 24 hours etc.
        dblResponse = 24
End Select

'First, check to see if the dispatch time is within the "open" times
'and if not, move it to Sunday at 7:00pm
If Weekday(dtTicket) = vbFriday And TimeValue(dtTicket) >= #7:00:00 PM# Then
    dtTicket = DateAdd("d", 2, DateValue(dtTicket)) + #7:00:00 PM#
ElseIf Weekday(dtTicket) = vbSaturday Then
    dtTicket = DateAdd("d", 1, DateValue(dtTicket)) + #7:00:00 PM#
ElseIf Weekday(dtTicket) = vbSunday And TimeValue(dtTicket) < #7:00:00 PM# Then
    dtTicket = DateValue(dtTicket) + #7:00:00 PM#
End If

'Next, calculate what the response time would normally be.
fnSLA = DateAdd("h", dblResponse, dtTicket)

'Then check to see if that lands during your closed hours
If (Weekday(fnSLA) = vbFriday And TimeValue(fnSLA) >= #7:30:00 PM#) Or (Weekday(fnSLA) = vbSaturday) Then
    'And if it does, add two days to the response time
    fnSLA = DateAdd("d", 2, fnSLA)
End If

End Function

Hope this works out for your...
willpower

 

[nikolaus21]

Thanks. I will try and see if it does the job.

 

[nikolaus21]

Hi Willpower

I'll got an error "Undefined function 'fnSLA' in expression". Any Idea ???

Thanks so much for you help in advance.
Michael

 

[willpower]

It sounds like you're trying to call the function as part of an SQL expression attached to a form. Instead, you'll need to call the function from code in an event procedure (which event depends on when you need to update the value) For instance, you may need to use the AfterUpdate event for the dispatch date/time control (assuming there is one), or you may need to use the BeforeUpdate event of the form. If you're struggling, I think you'll have to attach a copy of your database so I can take a look. I'm afraid it may take me some time at the moment, but will do what I can...

willpower

 

[boblarson]

Hi Willpower

I'll got an error "Undefined function 'fnSLA' in expression". Any Idea ???

Thanks so much for you help in advance.
Michael

Did you paste the code into a STANDARD module and NOT a form's module? That would cause that problem.

 

[nikolaus21]

Hi Willpower,

Here we go. Attached is the DB with some test data and the function.

Thanks in advance,
Michael

 

[boblarson]

Your problem is you named your module the same as the function. You can't do that. Rename your module to something like modFnSLA.

 

[boblarson]

Now, that being said, the function itself is flawed and doesn't return the correct values. I'll try to analyze why.

 

[nikolaus21]

YOU GUYS ARE GREAT !!! Renaming the module worked. I am checking the results right now but so far and I cannot find a wrong time calculation. I will check a couple more records.

Thanks so much for your help !!!

 

[willpower]

Erk...just logged back in to find this topic's gone all hot on me...and that the function's flawed...perhaps I should slip away quietly now. Thanks for stepping in bob...

Just getting around to downloading the db now, so apologies I'm not around at the right times... would really appreciate you letting me know if there's problem with the function, as I'll owe some apologies...

Some immediate points to note:
1. There are no allowances for severity levels of 1 or 2. These would need to be added to the Select Case section... At present, the function will simply return the ticket time (or Sunday, 7pm for out-of-hours tickets) for any records with these severity values.
2. There is no check for response times landing in "closed" hours on Sunday before being adjusted to allow for closed hours (if that makes sense!). I skipped this because any calls whose un-adjusted response time would have landed on a Sunday must have an out-of-hours ticket time and would therefore have already been shifted to Sunday 7pm.

Hope things are going OK...

willpower

 

[willpower]

'Then check to see if that lands during your closed hours
If (Weekday(fnSLA) = vbFriday And TimeValue(fnSLA) >= #7:30:00 PM#) Or (Weekday(fnSLA) = vbSaturday) Then
    'And if it does, add two days to the response time
    fnSLA = DateAdd("d", 2, fnSLA)
End If

End Function

Noticed a mistake in the above section... the check should be:

If (Weekday(fnSLA) = vbFriday And TimeValue(fnSLA) >= #7:00:00 PM#) Or (Weekday(fnSLA) = vbSaturday) Then
...

...checking for times landing on or after 7pm on Friday, not 7:30...

willpower