Question Calculating Time Values

[brittaink]

Hi,
I am trying to write a part of my database and am a bit stuck. I have used the NetWorkhours() Function available on the web for calculating working hours for Service Level Agreements in my database. Basically faults have an open date\time and a shut date\time.

Each fault has an SLA (Service Level Agrrement) for example a password Lockout has a SLA of 1 hour. A Server Virus has an SLA of 90 "Working" Hours, (10 Days in a 9 hour day).

The NetWorkhours() Function calculates how many "Working" Hours there are between the two Date\Times.

This works absoloutley fine BUT (I say with a look of desperation), I need to be able to place the faults on hold when they are being dealt with by external agencies......

For example:
A User rings and raises a fault with an SLA of 5 hours...
The fault is raised on Friday at 0900. This gives me until 1300 to complete the job....
At 1000 I pass the fault to an external agency for rectification...
The fault is still open BUT, the time does not come out of my SLA. If they pass the job back to me at 1300 on the Monday. I need to take the job off "Hold" and the SLA will continue to count down.

This is not too much of a problem, BUT the job may be placed "On Hold" and Taken "Off Hold" more then once, in certain circumstances up to 4 times.

I have a Sub Table Which tracks comments on the fault, by linking through the 'id' field of the Faults to 'FaulId' on the sub table.

For example:
Fault 1 "Account Lockout"
-----Comment 1 "Stupid User"
-----Comment 2 "Stupid Admin"
etc.

This Table can be used to store vales such as
----- "Date\Time","On Hold"
----- "Date\Time","Off Hold"
----- "Date\Time","Rang User Left Message"
----- "Date\Time","On Hold"
----- "Date\Time","Off Hold"
----- "Date\Time","Fault Fixed and Closed"

From Here how would I be able to calculate time differences between the first time the fault goes "On Hold" and "Off Hold", then the second, third and fourth etc.

With these values I could then subtract them from the total working hours.

As you can see, I know what I would like to do but really am struggling with how to implement it.

If anyone has any idea's or links It would be much appreciated.

Thank You

Keith

 

[Fifty2One]

Where did you get the NetWorkhours() Function on the web ?

 

[brittaink]

Where did you get the NetWorkhours() Function on the web ?

http://www.access-programmers.co.uk/forums/archive/index.php/t-120289.html

 

[DCrake]

This function looks very similar to my function that I provided to a member a while ago
Option Compare Database

Public Function NetWorkhours(dteStart As Date, dteEnd As Date, Spellout As Boolean) As Variant

Dim intGrossDays As Integer
Dim intGrossMins As Single
Dim dteCurrDate As Date
Dim i As Integer
Dim WorkDayStart As Date
Dim WorkDayend As Date
Dim nonWorkDays As Integer
Dim StartDayMins As Single
Dim EndDayMins As Single
Dim NetworkMins As Integer
NetworkMins = 0
nonWorkDays = 0
'Calculate work day hours on 1st and last day

WorkDayStart = DateValue(dteEnd) + TimeValue("09:00:00")
WorkDayend = DateValue(dteStart) + TimeValue("17:00:00")
StartDayMins = DateDiff("n", dteStart, WorkDayend)
EndDayMins = DateDiff("n", WorkDayStart, dteEnd)
'adjust for time entries outside of business hours


'Calculate total hours and days between start and end times

intGrossDays = DateDiff("d", (dteStart), (dteEnd))
intGrossMins = DateDiff("n", (dteStart), (dteEnd))

'count number of weekend days and holidays (from a table called "Holidays" that lists them)

For i = 0 To intGrossDays
    dteCurrDate = dteStart + i
    If Weekday(dteCurrDate, vbSaturday) < 3 Then
        nonWorkDays = nonWorkDays + 1
    Else
        'If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
        'nonWorkDays = nonWorkDays + 1
        'End If
    End If
Next i
'Calculate number of work hours

Select Case intGrossDays
    Case 0
        'start and end time on same day
        NetworkMins = (intGrossMins - ((nonWorkDays) * 1440))
    Case 1
        'start and end time on consecutive days
        NetworkMins = StartDayMins + EndDayMins
    Case Is > 1
        'start and end time on non consecutive days
        NetworkMins = (((intGrossDays - 1) - nonWorkDays) * 480) + (StartDayMins + EndDayMins)
    
    End Select
    If Spellout = True Then
        NetWorkhours = MinsToTime(NetworkMins) ' hours and mins
    Else
        NetWorkhours = NetworkMins ' minutes only
    End If
    
    
End Function

Function MinsToTime(Mins As Integer) As String
    MinsToTime = Mins \ 60 & " hour" & IIf(Mins \ 60 <> 1, "s ", " ") & Mins Mod 60 & " minute" & IIf(Mins Mod 60 <> 1, "s", "")
End Function

 

[brittaink]

DCrake,

Sorry if this sounds like a bit of a stupid question but, The NetWorkHours() function already works in the database. I can see that there is the extra bit of code in your version. Is this to enable me to put the call on hold? I can't seem to see what it would achieve. If it is then how would I implement that.

That is the bit that I am having trouble implementing, I need to find out how to "pause" or "hold" the call at certain points with it being open. Either by increasing the SLA of that fault by the "On Hold" Time, or by deducting the "On Hold" Time from the NewWorkHours() total when the call is closed.

Thank You

Keith

 

[DCrake]

What I think you would need is a new incident table that was a 1:m to the actual incident and in that you would record the pauses expressed in minutes. One record for each pause, then after you have calculated the overall network hours you would then deduct the summed pause minutes frm the that.

The question is though would you want this to be ongoing or restrospective?

David

 

[brittaink]

DCrake,

Thank You, you have just kicked my brain back in to gear. That is such a simple thing to do. I really can't believe I never thought of that. I think I may have been looking so in depth I was not concentrating on the easier thing to do. I will like you say create another table that can filter by Fault ref and hold "On Hold" entries only. The again as you say just Sum() the minutes that the fault was put on hold to subtract from the NetWorkHours() at the end.

Thank You

Keith